Lecture 10 Index and Hashing (including B+ Tree)

Basic Concepts:

Search Key - attribute to set of attributes used to look up records in a file.

An index file consists of records (called index entries) of the form

search-key

pointer

Index files are typically much smaller than the original file

Two basic kinds of indices:

Ordered indices: search keys are stored in sorted order 

Hash indices: search keys are distributed uniformly across “ buckets” using a “ hash function” .

 

Ordered Indices:

In an ordered index, index entries are stored sorted on the search key value. E.g., author catalog in library.

Primary index: in a sequentially ordered file, the index whose search key specifies the sequential order of the file.

　　　Also called clustering index
　　　The search key of a primary index is usually but not necessarily the

Secondary index: an index whose search key specifies an order different from the sequential order of the file. Also called non-clustering index.

Index-sequential file: ordered sequential file with a primary index.

 

Dense Index Files:

Sparse Index Files:

Multilevel Index：

If primary index does not fit in memory, access becomes expensive.

Solution: treat primary index kept on disk as a sequential file and construct a sparse index on it.

　　outer index – a sparse index of primary index

　　inner index – the primary index file

If even outer index is too large to fit in main memory, yet another level of index can be created, and so on.

Indices at all levels must be updated on insertion or deletion from the file.

 

Index Update: Deletion：

Single-level index entry deletion:

Dense indices – deletion of search-key is similar to file record deletion.

Sparse indices –

 if an entry for the search key exists in the index, it is deleted by replacing the entry in the index with the next search-key value in the file (in search-key order).

 If the next search-key value already has an index entry, the entry is deleted instead of being replaced.

 

Index Update: Insertion：

Single-level index insertion:

　　Perform a lookup using the search-key value appearing in the record to be inserted.

　　Dense indices – if the search-key value does not appear in the index, insert it.

　　Sparse indices – if index stores an entry for each block of the file, no change needs to be made to the index unless a new block is created.

 　　If a new block is created, the first search-key value appearing in the new block is inserted into the index.

Multilevel insertion and deletion: algorithms are simple extensions of the single-level algorithms

 

Secondary Indices：

Frequently, one wants to find all the records whose values in a certain field (which is not the search-key of the primary index) satisfy some condition.

　　Example 1: In the instructor relation stored sequentially by ID, we may want to find all instructors in a particular department

　　Example 2: as above, but where we want to find all instructors with a specified salary or with salary in a specified range of values

We can have a secondary index with an index record for each search-key value

B -Tree Index Files:

B+-tree indices are an alternative to indexed-sequential files.

Disadvantage of indexed-sequential files

•   performance degrades as file grows, since many overflow blocks get created.

•   Periodic reorganization of entire file is required.

Advantage of B+-tree index files:

•   automatically reorganizes itself with small, local, changes, in the face of insertions and deletions.

•   Reorganization of entire file is not required to maintain performance.

(Minor) disadvantage of B+-trees:
extra insertion and deletion overhead, space overhead

 

A B+-tree is a rooted M-way search tree satisfying the following properties:



All paths from root to leaf are of the same length

　　keeps data sorted and allows searches, sequential access, insertions, and deletions in O(log n)

Each node that is not a root or a leaf has between ceiling(m/2)and m children.

Every node other than the root, is at least half full

ceiling(m/2)-1 <= #keys <= m-1

A leaf node has between [m/2] and m values

Special cases:

If the root is not a leaf, it has at least 2 children.
If the root is a leaf (that is, there are no other nodes in the tree), it can have between 0 and (m–1) values.

Insertion：

If the space that an entry would appear exists and is empty in the B+ tree, then just insert the value into it directly.

If the space does not exist, split the node and rearange the B+ tree so that it satisfies the rule.

Splitting a leaf node:

• take the n (search-key value, pointer) pairs (including the one being inserted) in sorted order. Place the first [n/2] in the original node, and the rest in a new node.  

• let the new node be p, and let k be the least key value in p, insert (k,p) in the parent of the node being split.

• If the parent is full, split it and propagate the split further up.

   

  

   

   

  

  

   

   

  

  

   

  Updates on B+-Trees: Deletion：

  Find the record to be deleted, and remove it from the main file and from the bucket (if present)

  Remove (search-key value, pointer) from the leaf node if there is no bucket or if the bucket has become empty

  If the node has too few entries due to the removal, and the entries in the node and a sibling fit into a single node, thenmerge siblings:

  – Insert all the search-key values in the two nodes into a single node, and delete the other node.

  – Delete the pair (Ki–1, Pi), where Pi is the pointer to the deleted node, from its parent, recursively using the above procedure.