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.
 Trying to gather statistics on an invisible index using either gather_index_stats or gather_table_stats with the cascade option gets an error.

YAS@11G>create table t as select * from all_objects;

Table created.

YAS@11G>create index t_ind on t(object_name) invisible;

Index created.

USER_INDEXES has a new column named VISIBILITY to indicate whether the index is visible or not.

YAS@11G>select index_name,VISIBILITY from user_indexes where index_name='T_IND';

INDEX_NAME                     VISIBILITY
------------------------------ ---------
T_IND                          INVISIBLE

YAS@11G>exec dbms_stats.gather_index_stats(user,'T_IND');
BEGIN dbms_stats.gather_index_stats(user,'T_IND'); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1

YAS@11G>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
BEGIN dbms_stats.gather_table_stats(user,'T',cascade=>true); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

YAS@11G>alter index t_ind visible;

Index altered.

YAS@11G>exec dbms_stats.gather_index_stats(user,'T_IND');

PL/SQL procedure successfully completed.

YAS@11G>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

is this is a bug or intended behavior but we need a way to collect statistics for an invisible index, otherwise how can we test if it will be used or not? There is a way to collect statistics for an invisible index, which is to set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES.

YAS@11G>alter session set optimizer_use_invisible_indexes=true;

Session altered.

YAS@11G>exec dbms_stats.gather_index_stats(user,'T_IND');

PL/SQL procedure successfully completed.

YAS@11G>alter session set optimizer_use_invisible_indexes=false;

Session altered.

We can change the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to see if the index will be used.

YAS@11G>alter session set optimizer_use_invisible_indexes=true;

Session altered.

YAS@11G>explain plan for select * from t where object_name='STANDARD';

Explained.

YAS@11G>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1376202287

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   202 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   202 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OBJECT_NAME"='STANDARD')

14 rows selected.

YAS@11G>alter session set optimizer_use_invisible_indexes=false;

Session altered.

If we make the index visible we see that the index is considered and used.

YAS@11G>alter index t_ind visible;

Index altered.

YAS@11G>explain plan for select * from t where object_name='STANDARD';

Explained.

YAS@11G>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1376202287

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     2 |   316 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |   316 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     2 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OBJECT_NAME"='STANDARD')

Note
-----
  - dynamic sampling used for this statement

18 rows selected.