Reverse Key Index

There is an option to create index entries as reversed, which is called reverse key indexes. Oracle stores the index entries as their bytes reversed, except rowids of course.

There are a few cases where reverse key indexes can help to improve performance. One is in RAC environments. If you have a column populated by an increasing sequence the new entries come to the same blocks when you are inserting rows. If you have many concurrent sessions inserting rows from different RAC instances then you will have a contention for the same index blocks between nodes. If you use reverse key indexes in this case then the new index entries will go to different blocks and contention will be reduced.

In single instance databases there is also a case where reverse key indexes can be helpful. If you have a column populated by an increasing sequence, you delete some old rows from the table and you do not do range scans on that column and you have contention issues on index blocks, reverse key indexes can be considered. The reverse key index will scatter the entries accross different blocks during inserting and your many concurrent sessions will not have index block contention issues.

If you are deleting some old rows, the blocks from a normal index on that column will have some used and some empty space in them, but they will not be put on the freelist because they are not completely free. That empty space will not be used because the sequence values are always increasing and they will not go to those old blocks because of that. You will be able to use that space for different values with reverse key indexes.

One of the things to be careful about reverse key indexes is that you cannot perform range scans on them. Because the entries are stored as reversed you lose the capability to range scan on that index.

I had a situation just fitting the reasons to use a reverse key index. We have many concurrent programs that insert into the same table. The table has a primary key column populated by an increasing sequence. There are no range scans on that column. The data is deleted time to time according to some rules which leave some old data undeleted in the table. When these programs are running statspack reports show high buffer busy waits for the index segment (More than 900,000 waits for a 30 minute period causing %85 of all buffer busy waits). Also as this database will be converted to a RAC database soon, this case seems very appropriate to use a reverse key index on the related column.

To change an existing index as a reverse key index you can use the alter index statement.

alter index indexname rebuild reverse;

After this change the index size was reduced down to 16MB from 250MB. This change got rid of the buffer busy waits on the index blocks. The program run time was reduced from about 40 minutes to about 25 minutes.

A few discussions from asktom about reverse key indexes are here, here and here.