CSL 451 Introduction to Database Systems
Indexing and Hashing
Department of Computer Science and Engineering
Indian Institute of Technology Ropar
Narayanan (CK) Chatapuram Krishnan!
Summary
• 
• 
• 
• 
• 
• 
• 
•  Multilevel indices
•  B+-Tree Index Files
Access types
Access time
Insertion time
Deletion time
Space overhead
Search key
Clustering index
–  Balanced tree
–  ceil(n/2) – n number of
children for a nonleaf node.
• 
• 
• 
• 
–  primary indices
•  Nonclustering indices
–  secondary indices
•  index-sequential files
•  Dense and Sparse indices
30/03/15!
structure of a B+ tree
queries
insertion
deletion
•  B+-Tree Extensions
–  file organization
–  indexing strings
•  prefix compression
–  B tree
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
2!
11.1 Indices speed query processing, but it is usually a bad idea
to create indices on every attribute, and every combination of
attributes, that is a potential search key. Explain why?
30/03/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
3!
11.2 Is it possible in general to have two clustering indices on
the same relation for different search keys? Explain?
30/03/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
4!
11.15 When is it preferable to use a dense index rather than a
sparse index? Explain your answer.
30/03/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
5!
11.16 What is the difference between a clustering index and a
secondary index?
30/03/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
6!
11.3.a Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of pointers that will fit in one
node to be 4.
2
3
5
5
2
2
3
3
5
11
5
7
5
5
5
11
7
2
17
2
5
2
3
5
11
7
3
17
19
7
11
5
11
5
7
11
17
19
17
19
23
29
23
5
30/03/15!
5
19
11
2
3
3
5
7
11
19
11
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
7!
11.3.a Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of pointers that will fit in one
node to be 4.
5
2
3
5
7
11
19
11
17
19
23
29
19
23
29
30/03/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
31
8!
11.3.a Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of pointers that will fit in one
node to be 4.
5
2
3
5
11
7
19
11
17
19
23
29
19
5
2
30/03/15!
3
5
11
7
29
11
17
19
23
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
29
31
9!
11.3.b Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of pointers that will fit in one
node to be 6.
2
30/03/15!
3
5
7
19
7
11
17
19
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
23
29
31
10!
11.17 For the B+-tree show the steps involved in the following
queries
a.  find records with a search-key value of 11
b.  find records with a search-key value between 7 and 17,
inclusive.
19
5
2
30/03/15!
3
5
11
7
29
11
17
19
23
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
29
31
11!
11.4 Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of points that will fit in one
node to be 4. Show the form of the tree after
a.  insert 9
19
5
2
3
5
11
29
7
11
17
19
23
29
31
19
5
2
30/03/15!
3
5
11
7
29
9
11
17
19
23
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
29
31
12!
11.4 Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of points that will fit in one
node to be 4. Show the form of the tree after
b.  insert 10
19
5
2
3
11
5
7
29
9
11
17
19
23
29
31
19
5
2
3
5
9
11
29
7
11
9
30/03/15!
17
19
23
29
31
10
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
13!
11.4 Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of points that will fit in one
node to be 4. Show the form of the tree after
c.  insert 8
19
5
2
3
5
9
11
29
7
11
9
17
19
23
29
31
19
23
29
31
10
19
5
2
3
5
9
7
11
29
8
11
9
30/03/15!
17
10
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
14!
11.4 Construct a B+ -tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in
ascending order. Let the number of points that will fit in one
node to be 4. Show the form of the tree after
d.  delete 23
19
5
2
3
5
9
7
11
29
8
11
9
17
19
29
23
31
10
11
5
2
30/03/15!
3
9
5
19
7
8
9
10
11
17
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
19
29
31
15!
11.12 What would the occupancy of each leaf node of a B+-tree
be, if index entries are inserted in sorted order? Explain why?
30/03/15!
Department of Computer Science and Engineering !
Indian Institute of Technology Ropar!
16!
Static Hashing
■  A bucket is a unit of storage containing one or more records (a
bucket is typically a disk block).
■  In a hash file organization we obtain the bucket of a record directly
from its search-key value using a hash function.
■  Hash function h is a function from the set of all search-key values K
to the set of all bucket addresses B.
■  Hash function is used to locate records for access, insertion as well
as deletion.
■  Records with different search-key values may be mapped to the
same bucket; thus entire bucket has to be searched sequentially to
locate a record.
Database System Concepts - 6th Edition
11.17
©Silberschatz, Korth and Sudarshan
Example of Hash File Organization
Hash file organization of instructor file, using dept_name as key (See figure in next slide.)
■  There are 10 buckets,
■  The binary representation of the ith character is assumed to be the
integer i.
■  The hash function returns the sum of the binary representations of
the characters modulo 10
● 
E.g. h(Music) = 1
h(History) = 2 h(Physics) = 3 h(Elec. Eng.) = 3
Database System Concepts - 6th Edition
11.18
©Silberschatz, Korth and Sudarshan
Example of Hash File Organization
bucket 0
bucket 4
12121 Wu
76543 Singh
bucket 1
15151 Mozart
Music
40000
History
80000
58583 Califieri
History
60000
98345 Kim
Finance
90000
80000
Biology
72000
bucket 5
bucket 2
32343 El Said
bucket 3
22222 Einstein
33456 Gold
Finance
76766 Crick
bucket 6
10101 Srinivasan Comp. Sci. 65000
45565 Katz
Comp. Sci. 75000
83821 Brandt
Comp. Sci. 92000
bucket 7
Physics 95000
Physics 87000
Elec. Eng. 80000
Hash file organization of instructor file, using dept_name as key
(see previous slide for details).
Database System Concepts - 6th Edition
11.19
©Silberschatz, Korth and Sudarshan
Hash Functions
■  Worst hash function maps all search-key values to the same bucket;
this makes access time proportional to the number of search-key
values in the file.
■  An ideal hash function is uniform, i.e., each bucket is assigned the
same number of search-key values from the set of all possible values.
■  Ideal hash function is random, so each bucket will have the same
number of records assigned to it irrespective of the actual distribution of
search-key values in the file.
■  Typical hash functions perform computation on the internal binary
representation of the search-key.
● 
For example, for a string search-key, the binary representations of
all the characters in the string could be added and the sum modulo
the number of buckets could be returned. .
Database System Concepts - 6th Edition
11.20
©Silberschatz, Korth and Sudarshan
Handling of Bucket Overflows
■  Bucket overflow can occur because of
● 
Insufficient buckets
● 
Skew in distribution of records. This can occur due to two
reasons:
!  multiple
records have same search-key value
!  chosen
hash function produces non-uniform distribution of key
values
■  Although the probability of bucket overflow can be reduced, it cannot
be eliminated; it is handled by using overflow buckets.
Database System Concepts - 6th Edition
11.21
©Silberschatz, Korth and Sudarshan
Handling of Bucket Overflows (Cont.)
■  Overflow chaining – the overflow buckets of a given bucket are
chained together in a linked list.
■  Above scheme is called closed hashing.
● 
An alternative, called open hashing, which does not use overflow
buckets, is not suitable for database applications.
bucket 0
bucket 1
overflow buckets for bucket 1
bucket 2
bucket 3
Database System Concepts - 6th Edition
11.22
©Silberschatz, Korth and Sudarshan
Hash Indices
■  Hashing can be used not only for file organization, but also for index-
structure creation.
■  A hash index organizes the search keys, with their associated record
pointers, into a hash file structure.
■  Strictly speaking, hash indices are always secondary indices
● 
if the file itself is organized using hashing, a separate primary
hash index on it using the same search-key is unnecessary.
● 
However, we use the term hash index to refer to both secondary
index structures and hash organized files.
Database System Concepts - 6th Edition
11.23
©Silberschatz, Korth and Sudarshan
Example of Hash Index
bucket 0
76766
bucket 1
45565
76543
bucket 2
22222
bucket 3
10101
bucket 4
bucket 5
15151
33456
58583
98345
76766
10101
45565
83821
98345
12121
76543
32343
58583
15151
22222
33465
Crick
Srinivasan
Katz
Brandt
Kim
Wu
Singh
El Said
Califieri
Mozart
Einstein
Gold
Biology
Comp. Sci.
Comp. Sci.
Comp. Sci.
Elec. Eng.
Finance
Finance
History
History
Music
Physics
Physics
72000
65000
75000
92000
80000
90000
80000
60000
62000
40000
95000
87000
bucket 6
83821
hash index on instructor, on attribute ID
bucket 7
12121
32343
Database System Concepts - 6th Edition
11.24
©Silberschatz, Korth and Sudarshan
Deficiencies of Static Hashing
■  In static hashing, function h maps search-key values to a fixed set of B
of bucket addresses. Databases grow or shrink with time.
● 
If initial number of buckets is too small, and file grows, performance
will degrade due to too much overflows.
● 
If space is allocated for anticipated growth, a significant amount of
space will be wasted initially (and buckets will be underfull).
● 
If database shrinks, again space will be wasted.
■  One solution: periodic re-organization of the file with a new hash
function
● 
Expensive, disrupts normal operations
■  Better solution: allow the number of buckets to be modified dynamically.
Database System Concepts - 6th Edition
11.25
©Silberschatz, Korth and Sudarshan
Dynamic Hashing
■  Good for database that grows and shrinks in size
■  Allows the hash function to be modified dynamically
■  Extendable hashing – one form of dynamic hashing
● 
● 
● 
Hash function generates values over a large range — typically b-bit
integers, with b = 32.
At any time use only a prefix of the hash function to index into a
table of bucket addresses.
Let the length of the prefix be i bits, 0 ≤ i ≤ 32.
!  Bucket
address table size = 2i. Initially i = 0
!  Value
● 
● 
of i grows and shrinks as the size of the database grows
and shrinks.
Multiple entries in the bucket address table may point to a bucket
Thus, actual number of buckets is < 2i
!  The number of buckets also changes dynamically due to
coalescing and splitting of buckets.
Database System Concepts - 6th Edition
11.26
©Silberschatz, Korth and Sudarshan
General Extendable Hash Structure
hash prefix
i1
i
00..
01..
bucket 1
10..
i2
11..
…
bucket 2
i3
bucket 3
bucket address table
…
In this structure, i2 = i3 = i, whereas i1 = i – 1 (see next
slide for details)
Database System Concepts - 6th Edition
11.27
©Silberschatz, Korth and Sudarshan
Use of Extendable Hash Structure
■  Each bucket j stores a value ij
● 
All the entries that point to the same bucket have the same values on
the first ij bits.
■  To locate the bucket containing search-key Kj:
1. Compute h(Kj) = X
2. Use the first i high order bits of X as a displacement into bucket
address table, and follow the pointer to appropriate bucket
■  To insert a record with search-key value Kj
● 
follow same procedure as look-up and locate the bucket, say j.
● 
If there is room in the bucket j insert record in the bucket.
● 
Else the bucket must be split and insertion re-attempted (next slide.)
!  Overflow
Database System Concepts - 6th Edition
buckets used instead in some cases (will see shortly)
11.28
©Silberschatz, Korth and Sudarshan
Insertion in Extendable Hash Structure (Cont)
To split a bucket j when inserting record with search-key value Kj:
■  If i > ij (more than one pointer to bucket j)
● 
● 
● 
● 
allocate a new bucket z, and set ij = iz = (ij + 1)
Update the second half of the bucket address table entries originally
pointing to j, to point to z
remove each record in bucket j and reinsert (in j or z)
recompute new bucket for Kj and insert record in the bucket (further
splitting is required if the bucket is still full)
■  If i = ij (only one pointer to bucket j)
● 
● 
If i reaches some limit b, or too many splits have happened in this
insertion, create an overflow bucket
Else
!  increment i and double the size of the bucket address table.
!  replace each entry in the table by two entries that point to the
same bucket.
!  recompute
new bucket address table entry for Kj Now i > ij so use the first case above.
Database System Concepts - 6th Edition
11.29
©Silberschatz, Korth and Sudarshan
Deletion in Extendable Hash Structure
■  To delete a key value,
● 
locate it in its bucket and remove it.
● 
The bucket itself can be removed if it becomes empty (with
appropriate updates to the bucket address table).
● 
Coalescing of buckets can be done (can coalesce only with a
“buddy” bucket having same value of ij and same ij –1 prefix, if it is
present)
● 
Decreasing bucket address table size is also possible
!  Note:
decreasing bucket address table size is an expensive
operation and should be done only if number of buckets becomes
much smaller than the size of the table
Database System Concepts - 6th Edition
11.30
©Silberschatz, Korth and Sudarshan
Use of Extendable Hash Structure: Example
!"#$%&'("
!"!"#$%&'("#
$%&'&()
**+*ȱ++*+ȱ++++ȱ+*++ȱ**+*ȱ++**ȱ**++ȱ****
,&-./ȱ01%/
++++ȱ***+ȱ**+*ȱ*+**ȱ+**+ȱ**++ȱ*++*ȱ++*+
2'31/ȱ24(/
*+**ȱ**++ȱ+*+*ȱ++**ȱ++**ȱ*++*ȱ++*+ȱ++++
5%46413
+*+*ȱ**++ȱ+*+*ȱ****ȱ++**ȱ*++*ȱ+**+ȱ++++
7%89&:)
++**ȱ*+++ȱ+++*ȱ++*+ȱ+*++ȱ++++ȱ**++ȱ+*+*
;<8%1
**++ȱ*+*+ȱ+*+*ȱ*++*ȱ++**ȱ+**+ȱ+++*ȱ+*++
=!)8%18ȱȱȱȱȱȱȱȱȱȱȱȱ+**+ȱ+***ȱ**++ȱ++++ȱ+**+ȱ++**ȱ****ȱ***+
Database System Concepts - 6th Edition
11.31
©Silberschatz, Korth and Sudarshan
Example (Cont.)
■  Initial Hash structure; bucket size = 2
hash prefix
0
0
bucket address table
Database System Concepts - 6th Edition
bucket 1
11.32
©Silberschatz, Korth and Sudarshan
Example (Cont.)
■ 
Hash structure after insertion of “Mozart”, “Srinivasan”, and “Wu” records
hash prefix
1
1
15151 Mozart
bucket address table
!"#$%&'("
40000
1
!"!"#$%&'("#
$%&'&()
**+*ȱ++*+ȱ++++ȱ+*++ȱ**+*ȱ++**ȱ**++ȱ****
,&-./ȱ01%/ ++++ȱ***+ȱ**+*ȱ*+**ȱ+**+ȱ**++ȱ*++*ȱ++*+
2'31/ȱ24(/
*+**ȱ**++ȱ+*+*ȱ++**ȱ++**ȱ*++*ȱ++*+ȱ++++
5%46413
+*+*ȱ**++ȱ+*+*ȱ****ȱ++**ȱ*++*ȱ+**+ȱ++++
7%89&:)
++**ȱ*+++ȱ+++*ȱ++*+ȱ+*++ȱ++++ȱ**++ȱ+*+*
;<8%1
**++ȱ*+*+ȱ+*+*ȱ*++*ȱ++**ȱ+**+ȱ+++*ȱ+*++
=!)8%18ȱȱȱȱȱȱȱȱȱȱȱȱ+**+ȱ+***ȱ**++ȱ++++ȱ+**+ȱ++**ȱ****ȱ***+
Database System Concepts - 6th Edition
Music
11.33
10101 Srinivasan Comp. Sci. 90000
12121 Wu
Finance
90000
Add “Einstein” record
©Silberschatz, Korth and Sudarshan
Example (Cont.)
■  Hash structure after insertion of Einstein record
hash prefix
1
2
15151 Mozart
Music
40000
12121 Wu
Finance
90000
22222 Einstein
Physics
95000
2
bucket address table
!"#$%&'("
2
!"!"#$%&'("#
$%&'&()
**+*ȱ++*+ȱ++++ȱ+*++ȱ**+*ȱ++**ȱ**++ȱ****
,&-./ȱ01%/ ++++ȱ***+ȱ**+*ȱ*+**ȱ+**+ȱ**++ȱ*++*ȱ++*+
2'31/ȱ24(/
*+**ȱ**++ȱ+*+*ȱ++**ȱ++**ȱ*++*ȱ++*+ȱ++++
5%46413
+*+*ȱ**++ȱ+*+*ȱ****ȱ++**ȱ*++*ȱ+**+ȱ++++
7%89&:)
++**ȱ*+++ȱ+++*ȱ++*+ȱ+*++ȱ++++ȱ**++ȱ+*+*
;<8%1
**++ȱ*+*+ȱ+*+*ȱ*++*ȱ++**ȱ+**+ȱ+++*ȱ+*++
=!)8%18ȱȱȱȱȱȱȱȱȱȱȱȱ+**+ȱ+***ȱ**++ȱ++++ȱ+**+ȱ++**ȱ****ȱ***+
Database System Concepts - 6th Edition
11.34
10101 Srinivasan Comp. Sci. 65000
Add “Gold” and “El Said”
records – Physics and
History
©Silberschatz, Korth and Sudarshan
Example (Cont.)
■  Hash structure after insertion of Gold and El Said records
1
hash prefix
3
Mozart
Music
40000
22222 Einstein
Physics
33456
Physics
95000
87000
Finance
90000
15151
3
Gold
3
12121 Wu
!"#$%&'("
!"!"#$%&'("#
bucket address table
$%&'&()
**+*ȱ++*+ȱ++++ȱ+*++ȱ**+*ȱ++**ȱ**++ȱ****
,&-./ȱ01%/ ++++ȱ***+ȱ**+*ȱ*+**ȱ+**+ȱ**++ȱ*++*ȱ++*+
2'31/ȱ24(/
*+**ȱ**++ȱ+*+*ȱ++**ȱ++**ȱ*++*ȱ++*+ȱ++++
5%46413
+*+*ȱ**++ȱ+*+*ȱ****ȱ++**ȱ*++*ȱ+**+ȱ++++
7%89&:)
++**ȱ*+++ȱ+++*ȱ++*+ȱ+*++ȱ++++ȱ**++ȱ+*+*
;<8%1
**++ȱ*+*+ȱ+*+*ȱ*++*ȱ++**ȱ+**+ȱ+++*ȱ+*++
=!)8%18ȱȱȱȱȱȱȱȱȱȱȱȱ+**+ȱ+***ȱ**++ȱ++++ȱ+**+ȱ++**ȱ****ȱ***+
Database System Concepts - 6th Edition
11.35
2
10101
32343
Srinivasan Comp. Sci. 65000
60000
El Said
History
©Silberschatz, Korth and Sudarshan
Example (Cont.)
2
15151 Mozart
76766 Crick
hash prefix
3
Music
Biology
40000
72000
22222 Einstein
Physics
33456 Gold
Physics
95000
87000
Finance
90000
Finance
80000
History
60000
History
62000
3
And after insertion of
eleven records
3
12121 Wu
76543 Singh
3
bucket address table
32343 El Said
58583 Califieri
3
10101 Srinivasan Comp. Sci.
45565 Katz
Database System Concepts - 6th Edition
65000
Comp. Sci. 75000
11.36
83821 Brandt
Comp. Sci. 92000
©Silberschatz, Korth and Sudarshan