Levels of index pages

Indexes can have more than one level of pages. An index that is built by the LOAD utility requires a certain amount of storage, which depends on the number of index pages at all levels. The number of index pages at all levels depends on whether the index is unique.

Index pages that point directly to the data in tables are called leaf pages and are said to be on level 0. In addition to data pointers, leaf pages contain the key and record-ID (RID).

If an index has more than one leaf page, it must have at least one nonleaf page that contains entries that point to the leaf pages. If the index has more than one nonleaf page, then the nonleaf pages whose entries point to leaf pages are said to be on level 1. If an index has a second level of nonleaf pages whose entries point to nonleaf pages on level 1, then those nonleaf pages are said to be on level 2, and so on. The highest level of an index contains a single page, which Db2 creates when it first builds the index. This page is called the root page. The root page is a 4-KB index page. The following figure shows, in schematic form, a typical index.

Figure 1. Sample index structure and pointers (three-level index)
Begin figure description. This figure is described in the surrounding text. End figure description.

If you insert data with a constantly increasing key, Db2 adds the new highest key to the top of a new page. Be aware, however, that Db2 treats nulls as the highest value. When the existing high key contains a null value in the first column that differentiates it from the new key that is inserted, the inserted non-null index entries cannot take advantage of the highest-value split.