2011-10-12The Index Leaf Nodes

The primary purpose of an index is to provide an ordered representation of the indexed data. It is, however, not possible to store the data sequentially because an insert statement would need to move the following entries to make room for the new one. But moving large amounts of data is very time-consuming, so that the insert statement would be very slow. The problem’s solution is to establish a logical order that is independent of physical order in memory.

The logical order is established by a doubly linked list. Every node has links to two neighboring entries, very much like a chain. New nodes are inserted between two existing ones by updating their links to refer to the new node. The physical location of the new node doesn’t matter because the doubly linked list maintains the logical order.

The data structure is called a doubly linked list because each node refers to the preceding and the following node. It enables the database to read the index forwards or backwards as needed. Yet it is possible to insert new entries in constant time—that is, independent of the list length.

Doubly linked lists are also used for collections (containers) in many programming languages. Table 1.1 lists some of them.

Databases use doubly linked lists to connect the so-called index leaf nodes. Each leaf node is stored in database block or page; that is, the database’s smallest storage unit. All index blocks have the same size—typically a few kilobytes. The database uses the space in each block to the best possible extent and stores as many index entries as possible in each block. That means that the index order is maintained on two different levels; the index entries within each leaf node, and the leaf nodes among each other using a doubly linked list.

Figure 1.1. Index Leaf Nodes and Corresponding Table Data

Figure 1.1 illustrates the index leaf nodes and their connection to the table data. Each index entry consists of the indexed columns (the key, column 2) and refers to the corresponding table row (via ROWID or RID). Unlike the index, the table data is stored in a heap structure and is not sorted at all. There is neither a relationship between the rows stored in the same table block, nor is there any connection between the blocks.