More on Indexes
Secondary Indexes
B-Trees
1
Docsity.com
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Secondary Indexes - Database Systems - Lecture Slides, Slides of Database Management Systems (DBMS)
Some concept of Database System are Secondary Indexes, Security and Integrity, Sequential Files, Serializability, SQL Authorization, The Relational Data Model, Transaction Management. Main points of this lecture are: Secondary Indexes , Candies, Multiple Indexes, Manufacturer, Primary Index, Secondary Index, Facilitates Finding, Secondary Index, Predict, Duplicates
Typology: Slides
2012/2013
1 / 40
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Secondary Indexes - Database Systems - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
More on Indexes
Secondary Indexes
B-Trees
Secondary Indexes
- Sometimes we want multiple indexes on a relation. - Ex: search Candies(name,manf) both by name and by manufacturer
- Typically the file would be sorted using the key (ex: name) and the primary index would be on that field.
- The secondary index is on any other attribute (ex: manf).
- Secondary index also facilitates finding records, but cannot rely on them being sorted
4
Sequence field
50
30
70
20
40
80
10
100
60
90
- Sparse index
30 20 80 100
90 ...
does not make sense!
Design of Secondary Indexes
- Always dense, usually with duplicates
- Consists of key-pointer pairs ("key" means
search key, not relation key)
- Entries in index file are sorted by key
- Therefore second-level index is sparse
Secondary Index and Duplicate Keys
- Scheme in previous diagram wastes space
in the present of duplicate keys
- If a search key value appears n times in the
data file, then there are n entries for it in
the index.
Duplicate values & secondary indexes
8
10
20
40
20
40
10
40
10
40
30
10 10 10 20 20 30 40 40 40 40 ...
one option...
Problem:
excess overhead!
- disk space
- search time
Duplicate values & secondary indexes
10
10
20
40
20
40
10
40
10
40
30
10 20 30 40
50 60 ...
buckets
saves space as long as search-keys are larger than pointers and average key appears at least twice
Why “bucket” idea is useful
Indexes Records
name: primary Emp (name,dept,floor,...)
dept: secondary
floor: secondary
Summary of Indexes So Far
- Advantages:
- simple
- index is sequential file, good for scans
- Disadvantages
- either inserts are expensive
- or lose sequentiality (cf. next slide)
- Instead use B-tree data structure to
implement index
Example Index (sequential)
continuous
free space
14
10 20 30
40 50 60
70 80 90
39 31 35 36
32 38 34
33
overflow area (not sequential)
B-Tree Structure
- an example of a balanced search tree: every root-to-
leaf path has same length
- each node (vertex) in the tree is a block, which
contains search keys and pointers
- parameter n , which is largest value so that n+
pointers and n keys fit in one block
- Ex: If block size is 4096 bytes, keys are 4 bytes, and pointers are 8 bytes, then n = 340.
Constraints on B-Tree Nodes
- Keys in leaf nodes are copies of keys from data file, in
sorted order
- Root contains between 2 and n+1 index node
pointers
- Each internal node contains between
( n +1)/2 and n +1 index node pointers
- Each non-leaf node consists of
ptr 1 ,key 1 ,ptr 2 ,key 2 ,…,keym-1 ,ptr (^) m where ptri points to index node with keys between keyi-1 and keyi
Example B-tree nodes with n = 3
19
3035
30
(^30 )
30
more concise notation textbook notation
Leaf:
Non-leaf:
to record with key 30
to record with key 35
to part of tree with keys < 30
to part of tree with keys ≥ 30
Sample non-leaf
to keys to keys to keys to keys
< 57 57 ≤ k<81 81 ≤k<95 ≥ 95
20
57 81 95