Reverse key index:

 

*it is a type of b-tree index that can physically store the index in reverses the bytes of each index key while keepin the column order .

* reversing the key solves the problems of contention for leaf blocks in the right side of a b-tree index.

*In RAC databases in which multiple instances repeatedly modify the same block ex:if the one instance in the cluster adds 20 while another adds 21 in the order table which is the primary is sequential with each instance writing its key to the same leaf block on the right-hand side of the index.

*the reversal bytes of index will allow inserts across all keys

* *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.

*if we use the reverse key index the empty space(when we deleted the old rows in the table)block will be refilled ,because it ll store the column value in reverse order

*it will reduce the index block contention issues(the index block splits while inserting a new row into the index ,that transaction will have to for lock mode until the current session completes the operation).

 

Create reverse index:

SQL> create index idx_rever on test

(employee_id);

Index created.

rebuild existing index into reverse:



  
SQL> alter index EMP_EMP_ID_PK rebuild  reverse;

Index altered.

* I am going to take above indexes to monitor "index monitoring"

reverse index into normal index:


SQL> alter index EMP_EMP_ID_PK rebuild noreverse;

Index altered.

 

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

Source: Oracle  Website ,Google