反向索引是B*Tree索引的一个分支，它的设计是为了运用在某些特定的环境下的。Oracle推出它的主要目的就是为了降低在并行服务器（Oracle Parallel Server）环境下索引叶块的争用。当B*Tree索引中有一列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。也是由于索引被散布在不同的索引块中，会引起不必要的排序。

SQL> create table t1 as select * from dba_objects;
Table created.
Elapsed: 00:00:00.66
SQL> create index i_id on t1(object_id);
Index created.
Elapsed: 00:00:00.28
SQL> create table t2 as select * from dba_objects;
Table created.
Elapsed: 00:00:01.89
SQL> create index i_id_t2 on t2(object_id) reverse;
Index created.
Elapsed: 00:00:00.36

--为收集统计信息之前。
SQL> set autot traceonly
SQL> select object_id from t2 where object_id is not null order by object_id;
53521 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 783427685
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT ORDER BY     |      |
|*  2 |   TABLE ACCESS FULL| T2   |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
     975129  bytes sent via SQL*Net to client
      39740  bytes received via SQL*Net from client
       3570  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      53521  rows processed

SQL>    select object_id from t1 where object_id is not null order by object_id;
53519 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2500393926
-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|   1 |  SORT ORDER BY     |      |
|*  2 |   TABLE ACCESS FULL| T1   |
-----------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
   - rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
     974981  bytes sent via SQL*Net to client
      39729  bytes received via SQL*Net from client
       3569  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      53519  rows processed

--收集统计信息之后。
SQL> exec dbms_stats.gather_table_stats(user,'t1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.73
SQL> exec dbms_stats.gather_table_stats(user,'t2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.73
SQL> select object_id from t1 where object_id is not null order by object_id;
53519 rows selected.
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 2369746005
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 53519 |   261K|   120   (1)| 00:00:02 |
|*  1 |  INDEX FULL SCAN | I_ID | 53519 |   261K|   120   (1)| 00:00:02 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
        138  recursive calls
          0  db block gets
       3697  consistent gets
          0  physical reads
          0  redo size
     974981  bytes sent via SQL*Net to client
      39729  bytes received via SQL*Net from client
       3569  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      53519  rows processed

SQL> select object_id from t2 where object_id is not null order by object_id;
53521 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 4001069977
-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         | 53521 |   261K|       |   197   (4)| 00:00:03 |
|   1 |  SORT ORDER BY        |         | 53521 |   261K|  1272K|   197   (4)| 00:00:03 |
|*  2 |   INDEX FAST FULL SCAN| I_ID_T2 | 53521 |   261K|       |    29   (4)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        738  consistent gets
          0  physical reads
          0  redo size
     975129  bytes sent via SQL*Net to client
      39740  bytes received via SQL*Net from client
       3570  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      53521  rows processed
---反向索引可以解决块争用的问题，但是如果要对结果集进行排序的话，尽量不要在该表上使用反向索引。不能在所有使用常规索引的地方使用。在范围搜索中其不能被使用，例如，where column>value,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。