Reverse Key Index

Reverse Key Index

Indexes are used to help oracle retrieve data faster. But there is a drawback of Index, if an index create on a large table which under go massive insert/ update, the index may raise contention issue. To get ride from this Oracle provides couple of solutions and Reverse key Index is one of them.

 Real world case scenario
Case 1: Suppose table 'track_user' contains login related information of a system. The primary key column 'ID' of the table populated by an increasing sequence. So every new entries (for the index) come to the same blocks when a row inserted into the table. This is the way contention may increase!

As all we know that the primary key constraint is impose on column by creating an unique index on that column. So if we use reverse key indexes in this case then the index entries will go to different blocks and contention will be reduced.

Case 2: If you have a table with column which is populated by an increasing sequence and some times it go under some delete operation for old records. A index was created on That column and this index is on face range scan when you issue a select on that table. But this index has contention issues on index blocks.

To avoid the contention issue you can use Reverse key index as a solution.

What is Reverse key index ?

  Reverse key index was first introduce in Oracle 8 .A Reverse Key Index simply takes the index column values and reverses them before inserting into the index.

Uses

1. 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. For example, if you insert rows with keys 101, 102 and 103 into a table with a regular index, the rows are likely to be inserted into the same leaf block. W  In a Reverse Key Index the keys in our example become 101, 201 and 301, and the rows are inserted into disparate parts of the index segment.
2. 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 across different blocks during inserting and your many concurrent sessions will not have index block contention issues.
3. 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.

 Disadvantages

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. You can only perform fetch-by-key value or full-index scans.

Create And Manage Reverse Key Index

You create a Reverse Key Index with the key word REVERSE:

Create Index index_name on table_name (a,b,c) Reverse;

You can rebuild a Reverse Key Index into a regular index with the keyword NOREVERSE

Alter Index index_name Rebuild Noreverse;

If you rebuild a Reverse Key Index without the keyword NOREVERSE, it will rebuilt the Reverse Key Index.

Alter Index index_name Rebuild;

 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.

change an existing index as a reverse key index

You cannot rebuild a normal index into a reverse key index. You must drop the normal index and create the reverse index.
 

===============================================================================