Database Systems II Index Structures CMPT 454, Simon Fraser - - PDF document

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 111

Database Systems II Index Structures

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 112

Introduction

We have discussed the organization of records in secondary storage blocks. Records have an address, either logical or physical. But SQL queries reference attribute values, not record addresses. SELECT * FROM R WHERE a=10; How to find the records that have certain specified attribute values?

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 113

Introduction

recordID1 recordID2 . . . value

value value matching records index blocks holding records

value

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 114

Index-Structure Basics

Storage structures consist of files. Data files store, e.g., the records of a relation. Search key: one or more attributes for which we want to be able to search efficiently. Index file over a data file for some search key associates search key values with pointers to (recordID = rid) data file records that have this value. Sequential file: records sorted according to their primary key.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 115

Index-Structure Basics

Sequential File 20 10 40 30 60 50 80 70 100 90

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 116

Index-Structure Basics

Three alternatives for data entries k*:

• record with key value k
• <k, rid of record with search key value k>
• <k, list of rids of records with search key k>

Choice is orthogonal to the indexing technique used to locate entries k* Two major indexing techniques:

• tree-structures
• hash tables.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 117

Index-Structure Basics

Dense index: one index entry for every record in the data file. Sparse index: index entries only for some of the record in the data file. Typically, one entry per block of the data file. Primary index: determines the location of data file records, i.e. order of index entries same as order

• f data records.

Secondary index does not determine data location. Can only have one primary index, but multiple secondary indexes.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 118

Index-Structure Basics

Sequential File 20 10 40 30 60 50 80 70 100 90 Dense Index

10 20 30 40 50 60 70 80 90 100 110 120

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 119

Index-Structure Basics

Sequential File 20 10 40 30 60 50 80 70 100 90 Sparse Index

10 30 50 70 90 110 130 150 170 190 210 230

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 120

Index-Structure Basics

10 10 20 10 30 20 30 30 45 40

10 20 30 40

Duplicate key values

– sparse index – data entry for first new key from block

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 121

Index-Structure Basics

Sparse index:

• requires less index space per record,
• can keep more of index in memory,
• needed for secondary indexes.

Dense index:

• can tell if any record exists without accessing

data file,

• better for insertions.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 122

Index-Structure Basics

Index file can become very large, e.g. at least

• ne tenth of data file size for records with ten

attributes of same length. To speed-up index access, add a second index level on top of the first index level, a third level

• n top of the second one, . . .

First level can be dense, other levels are sparse.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 123

Index-Structure Basics

Sequential File 20 10 40 30 60 50 80 70 100 90 Sparse 2nd level

10 30 50 70 90 110 130 150 170 190 210 230 10 90 170 250 330 410 490 570

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 124

Index-Structure Basics

Index structure needs to support Equality Queries and Range Queries. Equality query: one attribute value specified, e.g. docID = 100, or age = 18. Range query: attribute range specified, e.g. 30 <= age <= 40. Index structures must also support DB modifications, i.e. insertions, deletions and updates.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 125

ISAM

ISAM = Index Sequential Access Method Hierarchy of index files (tree structure)

Non-leaf blocks blocks Overflow block Primary blocks Leaf CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 126

ISAM

Leaf blocks contain data entries. Non-leaf blocks contain pairs (ki,pi), where ki is a search key value and pi a pointer to the (first

• f the) records with that search key value.

P0 K 1 P 1 K 2 P 2 K m P m

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 127

ISAM

File Creation Leaf (data) blocks allocated sequentially, sorted by search key. Then non-leaf blocks allocated, then space for

• verflow blocks.

Index entries: <search key value, block id>; they „direct‟ search for data entries, which are in leaf blocks.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 128

ISAM

10* 15* 20* 27* 33* 37* 40* 46* 51* 55* 63* 97* 20 33 51 63 40 Root

Example

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 129

ISAM

Index Operations Search Start at root; use key comparisons to go to leaf. Insert Find leaf data entry belongs to, and put it there. Delete Find and remove from leaf; if empty overflow block, de-allocate.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 130

ISAM

Example After inserting 23*, 48*, 41*, 42*

10* 15* 20* 27* 33* 37* 40* 46* 51* 55* 63* 97* 20 33 51 63 40 Root 23* 48* 41* 42* Overflow blocks Leaf Index blocks blocks Primary

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 131

ISAM

Example

After deleting 42*, 51*, 97*. 51* appears in index level, but not in leaf level.

10* 15* 20* 27* 33* 37* 40* 46* 55* 63* 20 33 51 63 40 Root 23* 48* 41*

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 132

ISAM

Discussion Inserts / deletes affect only leaf pages.  static tree structure Tree can degenerate into a linear list of

• verflow blocks.

In this case, ISAM looses all advantages compared to a simple, non-hierarchical index file. Can we maintain a balanced tree structure dynamically under insertions / deletions?

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 133

B-Trees

Introduction Tree node corresponds to block. B-trees are balanced, i.e. all leaves at same

• level. This guarantees efficient access.

B-trees guarantee minimum space utilization. n (order): maximum number of keys per node, minimum number of keys is roughly n/2. Exception: root may have one key only. m + 1 pointers in node, m actual number of keys.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 134

B-Trees

Introduction

Index Entries (inner nodes) Data Entries (leaf nodes)

 leaf nodes are linked in sequential order  this B-tree variant is normally referred to as B+-tree

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 135

B-Trees

Introduction Node format: (p1,k1, . . ., pn,kn,pn+1) pi: pointer, ki: search key Node with m pointers has m children and corresponding sub-trees. n+1-th index entry has only pointer. At leaf level, this pointer references the next leaf node. Search key property: i-th subtree contains data entries with search key k <ki, i+1-th subtree contains data entries with search key k >= ki.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 136

B-Trees

Example Root n = 3

100 120 150 180 30 3 5 11 30 35 100 101 110 120 130 150 156 179 180 200

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 137

B-Trees

Example

to keys to keys to keys to keys < 57 57 k<81 81 k<95 95

57 81 95

Non-leaf (inner) node

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 138

B-Trees

Example

From non-leaf node to next leaf in sequence

57 81 95

To record with key 57 To record with key 81 To record with key 85

Leaf node

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 139

B-Trees

Space utilization full node

• min. node

Non-leaf Leaf

120 150 180 30 3 5 11 30 35

counts even if null

n = 3

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 140

B-Trees

Space utilization Number of pointers/keys for B-tree

Non-leaf (non-root) n+1 n (n+1)/2 (n+1)/2 - 1 Leaf (non-root) n+1 n Root n+1 n 1 1 Max Max Min Min ptrs keys ptrs data keys (n+1)/2 (n+1)/2

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 141

B-Trees

) (log

2 / N

O

n

Equality Queries To search for key k, start from root. At a given node, find “nearest key” ki and follow left (pi) or right (pi+1) pointer depending on comparison of k and ki. Continue, until leaf node reached. Explores one path from root to leaf node. Height of B-tree is where N: number of records indexed  runtime complexity

) (log N O

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 142

B-Trees

Insertions Always insert in corresponding leaf. Tree grows bottom-up. Four different cases: space available in leaf, leaf overflow, non-leaf overflow, new root.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 143

B-Trees

Insertions Space available in leaf

3 5 11 30 31 30 100 32

Insert key 32 n = 3

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 144

B-Trees

Insertions Leaf overflow

split overflowing node into two of (almost) same size and copy middle (separating) key to father node 3 5 11 30 31 30 100 3 5 7 7

n = 3 Insert key 7

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 145

B-Trees

Insertions Non-leaf overflow

split overflowing node and push middle key up to father node 100 120 150 180 150 156 179 180 200 160 180 160 179

Insert key 160

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 146

B-Trees

Insertions New root

split can propagate up to the root and result in new root 10 20 30 1 2 3 10 12 20 25 30 32 40 40 45 40 30 new root

Insert key 45

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 147

B-Trees

Deletions Locate corresponding leaf node. Delete specified entry. Four different cases: Leaf node has still enough entries, Coalesce with neighbor (sibling), Re-distribute keys, Coalesce or re-distribute at non-leaf.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 148

B-Trees

Deletions Coalesce with neighbor (sibling)

if node underflows and sibling has enough space, coalesce the two nodes 10 40 100 10 20 30 40 50 40

Delete key 50 n=4

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 149

B-Trees

Deletions Redistribute keys

if node underflows and sibling has extra entry, re-distribute entries of the two nodes

Delete key 50 n=4

10 40 100 10 20 30 35 40 50 35 35

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 150

B-Trees

Deletions Non-leaf coalesce Delete key 37 n=4

40 45 30 37 25 26 20 22 10 14 1 3 10 20 30 40 40 30 25 25

new root

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 151

B-Trees

B-Trees in Practice Often, coalescing is not implemented. It is too hard and typically does not gain a lot

• f performance.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 152

B-Trees

B-Trees in Practice Typical order: 200, typical space utilization: 67%. I.e., average fanout = 133. Typical capacities:

Height 4: 1334 = 312,900,700 records, Height 3: 1333 = 2,352,637 records.

Can often hold top levels in buffer pool:

Level 1 = 1 blocks = 8 Kbytes, Level 2 = 133 blocks = 1 Mbyte, Level 3 = 17,689 blocks = 133 Mbytes.

 O(1) processing for equality queries

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 153

B-Trees

B-Trees in Practice Order (n) concept replaced by physical space criterion in practice („at least half-full‟). Inner nodes can typically hold many more entries than leaf nodes. Variable sized records and search keys mean different nodes will contain different numbers

• f entries.

Even with fixed length fields, multiple records with the same search key value (duplicates) can lead to variable-sized data entries.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 154

Hash Tables

Introduction Tree-based index structures map search key values to record addresses via a tree structure. Hash tables perform the same mapping via a hash function, which computes the record address. Search key K Hash function h B: number of buckets.

} 1 ,..., { ) ( B K h

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 155

Hash Tables

Introduction Good hash function should have the following property: expected number of keys the same (similar) for all buckets. This is difficult to accomplish for search keys that have a highly skewed distribution, e.g. names. Common hash function K = „x1 x2 … xn‟ n byte character string B often chosen as prime number

B MOD x x K h

n)

... ( ) (

1

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 156

Hash Tables

Secondary-Storage Hash Tables Bucket: collection of blocks. Initially, bucket consists of one block. Records hashed to b are stored in bucket b. If bucket capacity exceeded, link chain of

• verflow buckets.

Assume that address of first block of bucket i can be computed given i. E.g., main memory array of pointers to blocks.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 157

Hash Tables

Secondary-Storage Hash Tables Hash tables can perform their mapping directly

• r indirectly.

. . .

records

. . .

(1) key h(key) (2) key h(key)

Index record key 1

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 158

Hash Tables

Insertions To insert record with search key K. Compute h(K) = i. Insert record into first block of bucket i that has enough space. If none of the current blocks has space, add a new block to the overflow chain, and store new record there.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 159

Hash Tables

Insertions INSERT: h(a) = 1 h(b) = 2 h(c) = 1 h(d) = 0

1 2 3

d a c b

h(e) = 1

e bucket capacity: 2 records

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 160

Hash Tables

Deletions To delete record with search key K. Compute h(K) = i. Locate record(s) with search key K in bucket i. If possible, move up remaining records within block. If possible, move remaining records from

• verflow chain to the previous block and de-

allocate block.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 161

Hash Tables

Deletions

1 2 3

a b c e d

Delete: e f

f g

maybe move “g” up

c

d

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 162

Hash Tables

Queries To find record(s) with search key K. Compute h(K) = i. Locate record(s) with search key K in bucket i, following the overflow chain. In the absence of overflow blocks, only one block I/O necessary, i.e. O(1) runtime. This is (much) better than B-trees. But hash tables do not support range queries!

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 163

Hash Tables

Queries In order to keep overflow chains short, keep space utilization between 50% and 80%. If space utilization < 50%: waste space. If space utilization > 80%: overflow chains become significant. Depends on hash function and on bucket capacity.

b b b n utilizatio space in fit that keys # bucket in keys # ) (

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 164

Hash Tables

So far, only static hash tables, i.e. the number B of buckets never changes. With growing number of records, space utilization cannot be kept in the desired range. Dynamic hash tables adapt B to the actual number

• f records stored.

Goal: approximately one block per bucket. Two dynamic methods:

Extensible Hashing, and Linear Hashing.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 165

Extensible Hash Tables

Introduction Add a level of indirection for the buckets, a directory containing pointers to blocks, one for each value of the hash function. Size of directory doubles in each growth step.

. . . . . .

h(K) to bucket

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 166

Extensible Hash Tables

Introduction Several buckets can share a data block, if they do not contain too many records. Hash function computes sequences of k bits, but bucket numbers use only the i first of these bits. i is the level of the hash table.

00110101

k i, grows over time

initially , 2 directory

• f

size i

i

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 167

Extensible Hash Tables

Insertions To insert record with search key K. Compute h(K) and take its first i bits. Global level i is part of the data structure. Retrieve the corresponding directory entry. Follow that pointer leading to block b. b has a local level j <= i. If b has enough space, insert record there. Otherwise, split b into two blocks.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 168

Extensible Hash Tables

Insertions If j < i, distribute records in b based on (j+1)st bit of h(K): if 0, old block b, if 1 new block b‟. Increment the local level of b and b‟ by one. Adjust the pointer in the directory that pointed to b but must now point to b‟. If j = i, first increment i by one. Double the directory size and duplicate all entries. Proceed as in case j < i.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 169

Extensible Hash Tables

Example

i = 1 1 1 0001 1001 1100

Insert 1010

1 1100 1010 New directory 2

00 01 10 11

i =

2 2

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 170

Extensible Hash Tables

1 0001 2 1001 1010 2 1100 Insert: 0111 0000

00 01 10 11

2 i = 0111 0000 0111 0001 2 2

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 171

Extensible Hash Tables

00 01 10 11

2 i = 2 1001 1010 2 1100 2 0111 2 0000 0001 Insert: 1001 1001 1001 1010

000 001 010 011 100 101 110 111

3 i = 3 3

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 172

Extensible Hash Tables

Overflow Chains May still need

• verflow chains

in the presence

• f too many

duplicates of hash values. Split does not help if all entries belong to same

• f two resulting blocks!

1 1101 1100 2 2 1100 insert 1100 1100 if we split:

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 173

Extensible Hash Tables

Overflow Chains

1 1101 1100 1 1100 insert 1100 add overflow block: 1101 1101

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 174

Extensible Hash Tables

Deletions To delete record with search key K. Using the directory, locate corresponding block b and delete record from there. If possible, merge block b with “buddy” block b‟ and adjust the directory pointers to b and b‟. If possible, halve the directory.  reverse insertion procedure

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 175

Extensible Hash Tables

Discussion Can manage growing number of buckets without wasting too much space. Assume that directory fits into main memory. Never need to access more than one data block (as long as there are no overflow chains) for a query. Doubling the directory is a very expensive

• peration. Interrupts other operations and

may require secondary storage.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 176

Linear Hash Tables

Introduction No directory. Hash function computes sequences of k bits. Take only the i last of these bits and interpret them as bucket number m. n: number of last bucket, first number is 0.

00110101

k i, grows over time

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 177

Linear Hash Tables

records) (number capacity bucket and records

• f

number total where , ) 1 ( c r c n r n utilizatio space

Insertions If m <= n, store record in bucket m. Otherwise, store it in bucket number If bucket overflows, add overflow block. If space utilization becomes too high, add one bucket at the end and increment n by 1.  file grows linearly

1

2i m

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 178

Linear Hash Tables

Insertions Bucket we add is usually not in the range of hash keys where an overflow occurred. When n > 2i, increment i by 1. i is the number of rounds of doubling the size

• f the Linear Hash table.

No need to move entries.

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 179

Linear Hash Tables

Example

00 01 10 11

0101 1111 1010 n = 01

Future growth buckets

0101

• can have overflow chains!
• insert 0101

If h(k)[i ] n, then look at bucket h(k)[i ] else, look at bucket h(k)[i ] – 2i -1 k = 4, i = 2

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 180

Linear Hash Tables

Example

00 01 10 11

0101 1111 1010 n = 01

Future growth buckets

10 1010 0101

• insert 0101

11 1111

0101

k = 4, i = 2

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 181

Linear Hash Tables

Example

k = 4

00 01 10 11

1111 1010 0101 0101 n = 11 i = 2 100 101 110 111 3 . . . 100 100 101 101 0101 0101

CMPT 454, Simon Fraser University, Fall 2009, Martin Ester 182

Linear Hash Tables

Discussion Can manage growing number of buckets without wasting too much space. No directory, i.e. no indirection in access and no expensive doubling operation. Significant need for overflow chains, even if no duplicates among last i bits of hash values.