Binary radix indexes

 

A radix index is a multilevel, hybrid tree structure that allows a large number of 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 address of the rows in the base table that are associated with the key value. The key value is used to quickly navigator to the leaf node with a few simple binary search tests.

The binary radix tree structure is very good for finding a small number of rows because it is able to find a given row with a minimal amount of processing. For example, using a binary radix index over a customer number column for a typical OLTP request like "find the outstanding orders for a single customer: will result in fast performance. An index created over the customer number column is considered to be the perfect index for this type of query because it allows the database to zero in on 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. Increasingly, users want ad hoc access to the detail data. They might for example, run a report every week to look at sales data, then "drill down" for more information related to a particular problem areas that they found in the report. In this scenario, you cannot write all of the queries in advance on behalf of the end users. Without knowing what queries will be run, it is impossible to build the perfect index.

• Specifying PAGESIZE on CRTPF or CRTLF commands
  When creating keyed files or indexes using the Create Physical File (CRTPF) or Create Logical File (CRTLF) commands, or the SQL CREATE INDEX statement, you can use the PAGESIZE parameter to specify the access path logical page size that is used by the system when the access path is created.

• General index maintenance
  Whenever indexes are created and used, there is a potential for a decrease in I/O velocity due to maintenance, therefore, you should consider the maintenance cost of creating and using additional indexes. For radix indexes with MAINT(*IMMED) maintenance occurs when inserting, updating or deleting rows.

 

Parent topic:

Creating an index strategy

 

Related information

SQL Create Index statement