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

Indexing using Oracle Database

Oracle Reverse Key Indexes

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

2) 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.

3) In a reverse index, the values are spread throughout the B*-tree structure. — 3) In a reverse index, the values are spread throughout the B*-tree structure

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