Indexes perform a critical role in any relational database and Db2 is no exception. Applications expect to receive near instant response when querying multi-billion row tables Indexing tables in a manner appropriate to the workload is a big part of a Database Administrator’s job.When no index exists to service a query then every row will be examined to determine which records qualify (i.e. tablespace scan). Let’s take a look at the way indexes work in a Db2 environment.
Readers can probably think of many practical (non database) examples of indexes. My favorite is a trip to your local library. We can think of the non-fiction section as an unordered collection of things (yes I know the Dewey Decimal Classification is not completely without order but work with me). When looking for a particular book (or group of books), one now uses the computer in the lobby to search for the desired book and then go to its relative location on the library shelf. In the old days the analogy is even better as the index existed in big cabinets with drawers and cards. There were three ways of looking things up (three indexes if you follow): subject, author and title. As long as you knew one of those things your book was easy to find even among tens of thousands of its peers. If you wanted to search by something else like publisher or author’s first name you would need to examine every book on every shelf.
Indexes in a RDBMS work in a very similar way. In a Db2 database, indexes are always stored separately from the data in the tables that they reference. Each row in the database has an address called a Record Identifier (RID) that may be used to directly locate the row with a single page request. An index is a sorted list of key values paired with the RIDs associated with those values.
Indexes are stored in a self-balancing tree (b-tree) structure with at least 2 levels. The number of levels needed will depend on the size of the index. The leaf page level consists of a sorted list of keys with the associated RIDs. The non-leaf levels provide a quick path to the leaf pages when columns are matched in the index by tracking the highest key value on each page below.
The example below demonstrates how an index may be used when columns are matched to locate only a few rows from a large table while only examining a few pages of the index. Once the appropriate entries are located at the leaf page level the tablespace pages containing the desired rows may be accessed directly. Notice that this index contains 3 columns but only the first 2 are used in the query. The index is still very useful in locating the desired result set.
The example below demonstrates how an index may be used when columns are matched to locate only a few rows from a large table while only examining a few pages of the index. Once the appropriate entries are located at the leaf page level the tablespace pages containing the desired rows may be accessed directly. Notice that this index contains 3 columns but only the first 2 are used in the query. The index is still very useful in locating the desired result set.