Outline

• Hash Indexes
• Bitmap Indexes
• Bloom Filters
• In-Class Exercise
• B-tree Indexes
• Optimizations

Introduction

• On Monday, we were looking at secondary indexes -- indexes based on an attribute or attributes that are not used as an ordering criteria of the actual data file.
• If such an index has a pointer from an attribute value to an intermediate structure with pointers to all the records with that attribute value, we said we have an inverted file.
• We also described inverted indexes which are used to implement full text search.
• We said how inverted indexes and column oriented database storage can be compressed.
• We then looked at multi-column indexes and discussed kd-trees.
• One important factor for kd-trees is the value `k`. This should be determined by the maximum number of record pointers and key ranges thhat can be fit into a block. I.e., the blocking factor. We choose `k` to be as large as possible to minimize the depth of the tree and hence block accesses needed for look up.
• We begin today by looking at some other common index structures before talking about B-tree.

Hash Indexes

• Recall in random file organization, we hash a key field to a bucket, then look for the record in that bucket (which is a list of blocks).
• If a secondary index on a table uses random file organization, it is called a hash index.
• Such an index still consists index entries (key value, pointer).
• To look up an entry in the index though, we first hash the key value, see what bucket we get, then look up the index entry in that bucket.
• Once we have the index entry we can follow the pointer to the record.

Bitmap Indexes

• If we have a binary-valued attribute we'd like to make an index on, it often makes sense to use a bitmap index.
• Given two choices (For example, Thumbs Up/Thumbs Down), assign one choice the value 1, the other 0.
• A bitmap index is a sequence of bits, where the `i`th bit is 1, indicates the choice value of the `i`th row was 1; a 0 indicates the choice value of the `i`th row was 0.
• Example: consider a 1,000,000 row table where 10 records fit in a block. So the table takes 100,000 blocks to store. Assume that a block is 4096 bytes long, of which 96 are used for header/overhead. Then we can store `8 times 4000 = 32000` bits in a block. So the bitmap index would take 33 blocks.
• We are likely able to compress this further if we use techniques like run length encoding (RLE). As an example of RLE: use 11 to indicate 1 and 10 to indicate 0 and use 01 to indicate we are switching from 0 to 1 or vice-versa. A string 11111111111111100000 would be encoded as: number of 1's till first 0 followed by 01 followed by number of 0's till next one or end of string. I.e., 11 11 11 11 01 11 10 11.
• Bitmap indexes are good for computing boolean queries. For example, if we ave two bitmap indexes and want to know rows where they are both 1, we can compute the bit-wise and of the two indexes.
• If a column only has `k` distinct values. We can use `log k` many bitmap indexes to index it.

Bloom Filters

• Given an index or a file and a key value, it often takes the same amount of time to determine if the key is in the index, as to determine if it is not in the index. I.e., several block accesses.
• It is useful to have a quick access structure (i.e., small enough to fit in RAM) to let us determine if a key is in the index or not, and then only look up the key if it is.
• A Bloom filter (Bloom 1970) is a commonly used data structure to solve this problem.
• It consists of `m` hash functions for the key `h_1(k), ..., h_m(k)` together with an `n`-bit vector `D`.
• To insert a key `k` into the Bloom filter, for each `i=1,2,..., m`, we turn the `h_i(k)`th bit of `D` to `1` if it isn't already 1, and leave it unchanged otherwise.
• To check if a key `k` is in the filter, we check that for all `i=1,2,..., m` the `h_i(k)`th bit of `D` is `1`.
• Notice if `k` has been stored in the filter, the above will say that it is in the filter, but if `k` hasn't been stored, the above procedure might give a false positive.
• It can be shown that the optimal ratio of `n`/(# of keys to store) is `-1.44 log_2 epsilon` where `epsilon` is the error rate and for this ratio we should use `-log_2 epsilon` many hash functions.
• For a 1% error rate, we need about 9.6 bits/key, regardless of the size of the key.

In-Class Exercise

• Suppose we have 10 integer keys we want to store in a Bloom filter with a 1% error rate.
• How big should `n` and `m` be?
• Let `h_(a,b)(k) := a cdot k + b mod n`. Randomly, pick `m` hash functions of this type.
• Insert 1000, 55, 99 into your Bloom filter.
• Look up 55 and 75? Did you get a false positive?
• Please post your solutions to the Oct 21 In-Class Exercise Thread.

B-trees

• As we have already seen, multi-level indexes (having an index of an index of ...) can speed up the retrieval of data.
• B-Trees (Bayer, McCreight 1970) are a family of data structures that allow us to maintain indexes of similar performance, but which naturally support inserts, deletes, and updates.
  • They automatically maintain the correct number of levels for the file size in use.
  • B-tree index blocks are used so that they are always between half and completely filled.
• We say family above because there are many variant on B-trees.
• We are going to store data only in the leaves not internally as the original B-trees do, so the structure I will present is actually what's called a `B^+`-tree.

The Structure of B-trees

• A B-tree is a balanced tree whose nodes are blocks. Balanced means 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 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`.

Example

• To look up 29 in the above, we'd first compare 29 with 13 at the top, following its right pointer.
• Then we'd find that 29 is between 23 and 31, so we'd follow the pointer between these values.
• This gets us to a block with 29 in it and we can follow its pointer to the data value.

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 on the earlier slide.

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.

Some Optimizations

• Rebalancing - some B-tree implementations try to delay or amortize split and merge node operations, by moving elements from more occupied nodes into less occupied nodes in a process called rebalancing.
• For example, Sqlite uses a `B^{\star}`-trees variant of `B^+`-tree. Given a node that we keep adding data to, we keep rebalancing data between it and its two sibling until all three nodes are full. Then rather than split the node we are adding data into two half-full nodes, we split it and a sibling into three nodes which are 2/3rds full. Keeping the load factor higher, will tend to make for a less deep tree and faster lookups.
• Right-Only Appends - if the search key of a `B`-tree is an auto-incremented field of the underlying tables, then when we do an insert where we don't give the key value, we know it is going to be appended to the end of the file. So we can save the access time needed to search down the right-hand side of the tree.