Reverse key indexes are a new feature of Oracle8. With reverse key indexes, the byte string for each column in an index is reversed. Consider a two-column index on last name and first name. The following two lines illustrate the difference between a normal index and a reverse key index:

Mahapatra,Tushar      (normal index)
artapahaM,rahsuT      (reverse key index)

This key reversal results in an improved distribution of data across the leaf nodes of the index tree as compared to what you get using the standard indexing method. That’s the upside. The downside is that you can’t perform range scans using reverse key indexes.

Reverse key indexes can improve OPS performance by reducing the occurrence of pinging on index blocks. This is especially true when records are inserted with sequentially increasing key values. Order records, with sequentially increasing order numbers, provide an example of when this might occur. When the key values of inserted records increase sequentially, all of the index inserts take place in the rightmost block of the index tree. If the inserts are performed from more than one OPS instance, that index block becomes a “hot” block and will experience a large amount of pinging. When the bytes of a sequentially increasing index column are reversed, the updates will be spread over a number of index blocks. That’s because the leading digit will no longer always be the same.

Specify the REVERSE keyword in the CREATE INDEX command to create a reverse key index. The following index generates a reverse key index on the ord_id column of the orders table:

CREATE INDEX ord_idx ON orders (ord_id) REVERSE;

Now, as new orders are added, the order ID numbers will be reversed before being indexed. For example, the sequential order numbers 831, 832, and 833 will be reversed to 138, 238, and 338, respectively. These reversed values will be spread out in the index tree and are likely to reside in different blocks. Because more than one block is involved, the odds that pinging will occur are reduced.

A standard index cannot be rebuilt as a reverse key index. To convert a standard index to a reverse key index, you will need to drop the index and re-create it using the REVERSE keyword.