Oracle Parallel Processing

Using Reverse Key Indexes

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