Reverse Key Index

I think we are familiar with Oracle Index, specially Bitmap 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 avoide the contention issue you can use Reverse key index as a soluation.

What is Reverse key index ?

Reverse key index was first introduce in Oracle 8. A reverse key index reverses the bytes of each column indexed (except for the ROWID) while keeping the column order same as normal index.

Uses

1. Reverse key index can help avoid performance degradation in indexes in an Oracle Parallel server environment where modifications to the index are concentrated on a small set of leaf blocks. 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. When users connected to different instances of the same database insert into the same block a pingoccurs.(When a block is written to disk by one instance so that another instance can read it, it is called a ping.) Excessive pinging will severely degrade performance, so you want to reduce it. 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. This type of index is therefore useful in a parallel server environment because it reduces pinging.
2. Reverse Key Indexes can also be useful in situations where users insert ascending values and delete lower values from a table. A regular index would become skewed, but a Reverse Key Index would not.
3. 
   

Disadvantages

With a Reverse Key Index you cannot run an index range scanning query. This is because lexically adjacent keys are not stored next to each other in a Reverse Key Index. You can only perform fetch-by-key value or full-index scans . Of course, you can avoid the index and perform full table scans or use the parallel query option.

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;

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