Outline

• Finish Secondary Indexes
• B-tree Domain of Application
• B-tree Operations
• Quiz
• Hash Tables on Disk
• Hash Table Operations

Introduction

• At the end of last day, we were talking about secondary indexes and their applications.
• We said that these were indexes on some field that the data file is not sorted on.
• They might be used if the data file was not sequential or on additional attributes we'd like indexed.
• We said they can also be used in conjunction witth horizontally partitioned tables and cluster files.
• We then looked at indirection in secondary indexes.
• We start today by describing a particular kind of secondary index which is useful to allow for searches within a field containing ttextt values.

Document Retrieval and Inverted Indexes

• An inverted index provides a mapping between terms and their locations in a text collection C.
• For web search, a text collection is typically split into documents and the mapping might only say the document rather than the byte offset in the document at which the term occurs.
• Such inverted indexes are usually secondary indexes and employ the kind of indirection mentioned on the last slide.
• The data structure used to store the possible key values is called a dictionary.
• The data structure used to contain the list of locations where a particular term occurs is called a posting list.
• A single entry in a posting list is called a posting.
• Often an inverted index stores only stem words for a given word/term in the document. Ex: storing dog for dogs, jump for jumping.
• Some stop words like "a" and "the" might not be included in the inverted index.
• To create a fulltext index for a DBMS like MYSQL which supports them, we can use a command like:

  ALTER TABLE  my_table 
  ADD FULLTEXT(column1, column2,..)
  

• If our SQL query has a LIKE clause on the columns in question the index will be used.
• Information on using stopwords in MySQL can be found on the MySQL reference article on full-text stopwords.

B-trees

• As we have already seen, multi-level indexes can speed up the retrieval of data.
• B-Trees (Bayer, McCreight 1970) are a family of data structures that allow us to maintain such kind of indexes by supporting 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.

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.

Quiz

Which of the following statements is true?

1. We might use a tombstone record when we insert a new record into a data file.
2. A clustering index is used to index a field in a sequential file with duplicates.
3. Oracle allows you to use the keyword FILLFACTOR to create tables that avoid overflow blocks.

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.

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 (W. W. Peterson, IBM Journal of Research and Development, 1957) 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 some polynomial (ideally, linear) of the length of `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.