Oracle Tips by Burleson

Using Reverse-Key Indexes

A reverse-key index prevents unbalancing of the B-tree and the resulting hot blocking, which will happen if the B-tree becomes unbalanced. Generally, unbalanced B-trees are caused by high-volume insert activity in a parallel server where the key value is only slowly changing, such as with an integer generated from a sequence or a data value. A reverse key index works by reversing the order of the bytes in the key value; of course, the rowid value is not altered, just the key value. The only way to create a reverse-key index is to use the CREATE INDEX command. An index that is not reverse-key cannot be altered or rebuilt into a reverse-key index; however, a reverse-key index can be rebuilt as a normal index.      

One of the major limitations of reverse-key indexes is that they cannot be used in an index range scan, since reversing the index key value randomly distributes the blocks across the index leaf nodes. A reverse-key index can only use the fetch-by-key or full-index(table)scans methods of access. Let’s look at an

example:

See Code Depot   

The above command would reverse the values for the po_num column  as  it creates the index. This would assure random distribution of the values across the index leaf nodes. But what if we then determine that the benefits of the reverse key do not outweigh the drawbacks? We can use the ALTER command to rebuild the index as a NOREVERSE index:

www.oracle-script.com