Outline

• B-tree Domain of Application
• B-tree Operations
• In-Class Exercise
• Hash Tables on Disk
• Hash Table Operations
• Extensible Hash Tables
• Multidimensional Indexes

Introduction

• Recall on Monday, we began talking about B-tree indexes (Bayer, McCreight 1970), a particular kind of search tree which can be used to implement secondary indexes in DBMSs.
• We said,a B-tree is a balanced tree whose nodes are blocks: Every path from the root to a leaf is of the same length.
• A B-tree is characterized by a parameter `n` which depends on the block size.
• Within a node a B-tree stores `n` search key values and `n+1` pointers. (pointers for internal nodes always point to one level lower in the tree)
• Key values are nondecreasing in going from left to right with tree at the same level
• We assume that the root will always use at least two of these pointers (unless we have an index with only 1 record).
• At a leaf, the last pointer points to the block to right. At least half of the pointers in a leaf are used and the pointers point to actual records.
• At least half of the pointers in are used in an internal node.

Domain of Application of B-trees

• B-trees can be used for search keys which are primary keys for the table whether or not the table is sorted on this key.
• B-trees can be used on non-primary key fields.
• B-trees can allow multiple occurrences of search key at the leaves.

Lookup in B-Trees

For simplicity we will assume no duplicate keys. To search for a record with key K:

• If at a leaf, look among the keys, follow pointer `K` if exists to record.
• For an interior node, if `K` is less than the first key or greater than the last, follow the pointer to the left/right. If `K_i le K lt K_{i+1}` then follow the pointer to the right of `K_i`.

In-Class Exercise

Use the algorithm of the previous slide to step-by-step explain how the record with key 19 would be looked up in the B-tree above.

Post your solution to the Feb 14 In-Class Exercise Thread.

Range Queries

• B-Trees not only support single-valued queries, but can also support range queries like:

  SELECT *
  FROM R
  WHERE R.a > 10;
  

• To look up keys in a range `[a,b]`, we look up `a`, then cycle through entries until we get a value larger than `b`.

Insertion into B-Trees

• First try to put key into the appropriate leaf, if there is room.
• If there is no room:
  1. We split the leaf node among two blocks, so that each is half full.
  2. We put a new (key, pointer) pair into the parent that selects between these two halves.
  3. If the parent has no room for the new key, we recurse on the parent node.
  4. If this propagates to the root and it is full,split the root into two and make a new root with a key to select between these two halves.
• The above two images shows the steps that might result when inserting a record with key 40 into the tree we had for the in-class exercise.

Deletion from B-Trees

• To delete from a B-Tree, we locate the record and its key pointer pair in a leaf in B-tree.
• We delete the record from the data file, and the key pointer from the leaf node of the B-tree.
• If the leaf node is at least half full, we are done with the deletion.
• Otherwise:
  • We need to modify the node, so we check if either sibling could remain more than half full after moving a key, pointer pair.
  • If so, move the appropriate end value pair from the sibling into the current node. Adjust parent node.
  • If neither sibling can do this, then combine this leaf with one of its two siblings and propagate the results up to parent.
• Starting from the initial tree on the right, the above figures show the tree that results from delete 7, followed by the two steps needed to restore a B-tree after further deleting 11.
• Notice we can't steal from the right in the third image because by "right" we mean the node that corresponds to the next pointer in the parent. In this case, that pointer is null.
• It should be noted some DBs just use tombstones rather than bother with deletion.

Efficiency of B-Trees

• Our goal in using B-trees is to minimize I/Os (block read/write from secondary storage).
• So we typically, choose the parameter `n` of a B-tree accordingly.
• Suppose a block can store `340` key-pointer pairs and on average is around `3/4` full.
• So around 255 pointers in any block index in use.
• In a three level tree the root has 255 children, `255^2` grandchildren, and `255^3` records pointer at the leaves. That is, around 16.6 million records.
• Since one typically caches at least the root, and often the next level as well, this means in one or two disk I/Os can find any record among 16.6 million records.

Secondary-Storage Hash Tables

• We next consider another way to implement secondary indexes which can often be as fast/faster than B-tree.
• I assume you have seen main memory hashing before...
  • Set-up is have a hash function `h(K)` mapping keys into buckets `0, ..., B-1` and have some scheme to resolve collisions.
  • `h(K)` should be computable in time proportional to `K` and `log B`. (Has to be proportional to `log B` as `h(K)` should be able to take any value in the range `0, ..., B-1`.)
  • Collisions should be rare enough that they can be resolved in `O(1)` expected time given some fill parameter of our hash table.
• If the hash table is in secondary storage, then we modify the above so that the buckets are each some number of blocks, initially, 1.
• If a block has too many records in it, then we use overflow blocks to handle any additional records hashing to this value.

Insertion, Deletion and Efficiency

• To insert we compute `h(K)`. If the labelled bucket `h(K)` still has room then we add a key pointer pair there. If not, we create an overflow bucket.
• To delete we compute `h(K)`, then search that bucket and any overflow bucket for the record pointer to the record to be deleted. Might consolidate blocks if some blocks becomes empty.
• Ideally, only one disk I/O is needed to find the record pointer we want. However, as the file grows we will tend to use overflow blocks more and more.

Extensible Hash Tables

• We want to dynamically increase the number of buckets to avoid having to do more than one I/O per hash lookup.
• To do this we add an indirection level (which might be cached in memory), which is an array of pointers 0 to `2^i -1` for some `i` that the data structure keeps track of.
• Some of these pointers might point to same block.
• Each block records says in its header how many bits of hash used to get to it.
• `h(K)` computes a `k` bit number for some `k >= i`. Alternatively, we could have a have function `h(K,i)` where `i` says number of bits we want.
• To look up `K`, first, compute `h(K)` using its first `i` bits. Then look up the corresponding indirection pointer and follow it to a bucket.

Insertion into Extensible Hash Tables

1. Compute `h(K)` look at first `i` bits.
2. Suppose this equals `x`.
3. Look up the `x`th entry in the indirection table, follow its pointer to a bucket.
4. If there is room insert into this bucket.
5. If no room and the number of bits of hash value used to look up bucket way less than `i`, say `j`, divide entries in block `x` into two blocks according to the `j+1`st bit of the hash function.
6. If `j=i` then we double the number of indirection pointers. That is set `i:=i+1`. Each old pointer on `i` bits now becomes two pointers on `i+1` bits. Now we can go to 3.

Linear Hash Tables

• Extensible hashing can be inefficient if:
  1. The number of records/block is small, we might have to set `i` reasonably high bbefore can distinguish keys.
  2. We have to insert a lot and update pointers
  3. After a doubling of the indirection layer size, it can no longer fit in memory.
• Another technique, linear hashing, always keeps the number of buckets `n` so that at most 80% of each bucket is filled on average. It works as follows:
  • We use the low order `|~ log n ~|` bits from `h(K)` to determine the bucket to look at for a key.
  • Buckets still maintain in their header a prefix of the hash function as an identifier.
  • Overflow blocks are permitted but on average there will be much fewer than `1` such block per bucket.
  • When we insert we check if adding the record makes block more than 80% full.
  • If yes, we increase the number of blocks by one, splitting the block that the record would be added to.
  • We use the most significant bit of the used portion of the hash function to distinguish between these two blocks.
  • `n` increases in this case and hence so too might `|~ log n ~|` and the number of bits from the hash we use.