Index Enhancements   «Prev 

Determining Which Type of Index to Use

Oracle provides a wide range of index types and features. The correct use of indexes results in well performing and scalable database application. Conversely, if you incorrectly or unwisely implement a feature, there may be detrimental performance implications. Table 4-5 summarizes the various Oracle index types available. At first glance, this is a long list and may be somewhat overwhelming to somebody new to Oracle. Deciding which index type to use is not as daunting as it might initially seem. For most applications, you should simply use the default B-tree index type

Note: Several of the index types listed in Table 4-5 are actually just variations on the basic, B-tree index. A reverse-key index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index valueis sequentially generated and inserted with similar values.

Table 4-5: Oracle Index Types and Feature Descriptions

Table 4-5:Oracle Index Types and Feature Descriptions

Oracle Reverse Key Indexes

In a normal B*-tree index, the values are spread throughout the leaf nodes from lowest to highest, left to right.

Smaller earlier values (101, 103, 105, 107, 108, 111) are deleted from the table, the left side of the B*-tree becomes more sparsely populated than the right side.

In a reverse index, the values are spread throughout the B*-tree structure

When these same earlier values are deleted, the B*-tree index does not become nearly as imbalanced