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.

Index Skip Scanning

Index skip scan is one of the new feature in oracle9i. Prior to oracle9i, if we have composit index and if we are not using leading column in the WHERE clause, then optimizer will not use the index. But in oracle9i and further versions, optimizer may use the skip scan index depends on the cardinality of the leading column and optimizer cost. The regular B-tree index will have only one range scan from top to bottom. But skip scan will do many range scan depends on the cardinatlity of the leading index column.

The advantage of index skip scan is, we can reduce the number of indexes on the table. It save index space and reduce the index maintenance. Skip scan index is not same as efficient as B-tree index. But it is better then the full table scan.

Here is an example.

SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
SEX VARCHAR2(10)
NAME VARCHAR2(20)

SQL> select count(*),sex from emp group by sex;

COUNT(*) SEX
---------- ----------
10000 F
10000 M

SQL> create index idx_emp on emp(sex,empno);

Index created.
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'EMP',ESTIMATE
_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.
SQL> set autotrace on explain;
SQL> select name from emp where empno=1934;

NAME
--------------------
Scott
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=2 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=4 Card=2 Bytes=20)
2 1 INDEX (SKIP SCAN) OF 'IDX_EMP' (NON-UNIQUE) (Cost=3 Card=2)

Optimizer used skip scan index for the above query. During the skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values. When optimizer use the skip scan index, the query(select name from emp where empno=1934) is broken down into two small range scan as below.


select name from emp where sex = 'M' and empno=1934
union
select name from emp where sex = 'F' and empno=1934

Let us consider the below SQL query and it is using regular B-tree index. Since we use the leading index column in the where clause. This query does only one range scan, not like skip scan index.

SQL> select name from emp where sex='M' and empno=1934;

NAME
--------------------
Scott

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=11)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=11)
2 1 INDEX (RANGE SCAN) OF 'IDX_EMP' (NON-UNIQUE) (Cost=1 Card=1)

Index Monitoring in oracle

Before oracle9i, monitoring the index is difficult one. For monitoring index, we need to run the explain plan and find the estimated execution plan to see if the index is used for the query. For instances, if we have multiple queries running in the application, then we have to run the explain plan for each query to find out the index usage.

Oracle9i has overcome this problem and easily identify the unused index. This feature helps us to remove unused index and reduce the database overhead.

Enable index

ALTER INDEX indexname MONITORING USAGE;

Disable index

ALTER INDEX indexname NOMINTORING USAGE;

Note: v$object_usage is gathering index monitoring information.

Here is the table structure for v$object_usage.

SQL> desc v$object_usage;
Name Type
----------------------- -------- ------------------------------
INDEX_NAME VARCHAR2(30) /* Name of the index*/
TABLE_NAME VARCHAR2(30) /* Name of the table*/
MONITORING VARCHAR2(3) /* Monitoring Turned on(YES/NO)*/
USED VARCHAR2(3) /* Index used (YES/NO) */
START_MONITORING VARCHAR2(19) /* Index monitoring start date*/
END_MONITORING VARCHAR2(19) /* Index monitoring end date. It will be null if the index is currently monitoring. It will be populated when we stop monitoring index*/
SQL>

Example for Index Monitoring

Step 1

SQL> show user
USER is "SCOTT"
SQL> select index_name from user_indexes where table_name='EMPLOYEE';

INDEX_NAME
------------------------------
IDX

SQL> alter index idx monitoring usage;

Index altered.


Step 2

Check the inforamation in v$object_usage.
SQL> ;
1 select monitoring,used,start_monitoring,end_monitoring from v$object_usage
2* where table_name='EMPLOYEE'
SQL> /

MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
YES NO 06/20/2008 10:59:33

SQL>

Step 3

Make use of index and check the v$object_usage.

SQL> select count(ename) from employee where empno=7902;

COUNT(ENAME)
------------
1

SQL> select monitoring,used,start_monitoring,end_monitoring from v$object_usage
2 where table_name='EMPLOYEE';

MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
YES YES 06/20/2008 10:59:33

SQL>

Step 4

The index is used and USED column in v$object_usage is updated YES. Now we disable the index.

SQL> alter index idx nomonitoring usage;

Index altered.
SQL> select monitoring,used,start_monitoring,end_monitoring from v$object_usage
2 where table_name='EMPLOYEE';

MON USE START_MONITORING END_MONITORING
--- --- ------------------- -------------------
NO YES 06/20/2008 10:59:33 06/20/2008 11:20:08

SQL>


Important Note:

Before removing unused index, Index monitoring should run for few months, batch process, year beginning reports, year end reports and make sure the index is not used anywhere. Then we can come to conclusion that the index is not used.

LOB & IOT cannot be monitored.