反向索引是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,因为在索引的叶块中索引码没有分类，所以不能通过搜索相邻叶块完成区域扫描。
