Reverse key Index

Oracle introduced reverse key index to reduce the block contention(buffer busy waits) in index segment. Normal index stores the index column value and rowid in index segment. But reverse key index stores the reverse index column value and rowid. Ofcourse, oracle will not reverse the rowid. So we call this as reverse key index.

Let us say, the index column value is 1234. It will be converted as 4321 and stores in index segment. 1235 will be converted as 5321 and stored in index segment. In reverse key index, oracle will not store the actual column values in sequentail order. Instead, it reverse the index column value and will be spreaded across many index blocks. This would avoid index block contention.

Reverse key index would be useful when index column is populating from sequence and concurrent session inserting the data on the table.

What is ideal place to use Reverse key index?

1. In a single instance system, multiple sessions are trying to insert/update the index column at the same time, the index column value is extracting from sequence. Also we are deleting the rows and we are not doing any range scan on reverse key index column. This would be ideal place for reverse key index.

2. In RAC environment, if we have column populated by an increasing numbers, concurrent session inserting the rows from different RAC instance, the index block will have contention between nodes. The data are deleted time to time according to some rules which leave some old data undeleted in the table. Also there is not much range scan on this table. This would be ideal place for reverse key index.

If we use regular index for above two scenario, during the concurrent insert, oracle stores the index values in the sequence order and it would end up storing multiple sequential index values in the same block. This would lead into block contention when multiple insert happens at the same time with in the same block. Another thing, when we delete the old rows, the block will not be moved to free list until all rows are deleted in that block.

Reverse key index will resolve the above said issues.

When we use reverse key index, the empty space(empty space would happen when we delete old rows in the table) in the block will be refilled. Because, reverse key index stores the column value in reverse order. You will be able to use the empty space for different values with reverse key indexes.

When we use reverse key index, index block contention will be reduced. 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.

How do we create reverse key index?

scott@orcl> create index idx_rev
2 on employee(empno) reverse;

Index created.

scott@orcl>

How do we covert regular index to reverse key index?

scott@orcl> create index idx_rev on employee(empno);

Index created.

scott@orcl> alter index idx_rev rebuild reverse;

Index altered.

scott@orcl>

How do we covert reverse key index to regular index?

scott@ordb> alter index idx_rev rebuild noreverse;

Index altered.

scott@ordb>

Restriction on Reverse key index?

Reverse key index does not support range scan. Since the index column values are not stored in the sequential order.