Binary radix indexes

A radix index is a multilevel, hybrid tree structure that allows many key values to be stored efficiently while minimizing access times. A key compression algorithm assists in this process. The lowest level of the tree contains the leaf nodes, which contain the base table row addresses associated with the key value. The key value is used to quickly navigate to the leaf node with a few simple binary search tests.

The binary radix tree structure is good for finding a few rows because it finds a given row with a minimal amount of processing. For example, create a binary radix index over a customer number column. Then create a typical OLTP request like "find the outstanding orders for a single customer". The binary index results in fast performance. An index created over the customer number column is considered the perfect index for this type of query. The index allows the database to find the rows it needs and perform a minimal number of I/Os.

In some situations, however, you do not always have the same level of predictability. Many users want on demand access to the detail data. For example, they might run a report every week to look at sales data. Then they want to "drill down" for more information related to a particular problem area they found in the report. In this scenario, you cannot write all the queries in advance on behalf of the end users. Without knowing what queries might run, it is impossible to build the perfect index.