理解Oracle反向索引

 ㈠ 定义
          
          建 立一个反向索引将把每个列的键值(each column key value)按字节反向过来，对于组合键，列的顺序被保留，但每个列的字节都作了反向
          例如：
          表的某一列内容
          ……
          1234
          1235
          1236
          1237
          ……
          
          建立正向索引
          ……
          1234
          1235
          1236
          1237
          ……
          这四行放在同一个leaf block中。
          如果事务A查询1234这行，同时事务B查询1235这行。那么就会在这个leaf block上发生I/O争用
          
          
          建立反向索引
          ……
          4321
          5321
          6321
          7321
          ……
          这四行放在四个不同leaf block中
          如果事务A查询1234这行，同时事务B查询1235这行。是分别在两个leaf block上进行，不会发生I/O争用
          
          很多事务访问同一个块，对同一个块并发操作产生的I/0竞争
          反向索引能作为避免热点块的一个方法
          
       ㈡ 查找
          

          user_indexes.index_type

[sql] view plain copy print ?

1. scott@ORCL> create index idx_rev on emp(sal) reverse;  
2.   
3. Index created.  
4.   
5. scott@ORCL> select index_name,index_type from user_indexes where index_name='IDX_REV';  
6.   
7. INDEX_NAME                     INDEX_TYPE  
8. ------------------------------ ---------------------------   
9. IDX_REV                        NORMAL/REV  

       ㈢ 它有什么缺点？
          
          ① if you use reverse key index，index range scan will not work
          ② 当应用需要获取一段范围的数据时，reverse key index将不会被使用，因为键值不是连续的排列的。在这种情况下，CBO将会选择全表扫描
          
          测试：

[sql] view plain copy print ?

1. hr@ORCL> drop table t purge;  
2.   
3. Table dropped.  
4.   
5. hr@ORCL> create table t (a number,b varchar2(20));  
6.   
7. Table created.  
8.   
9. hr@ORCL> ed     
10. Wrote file afiedt.buf  
11.   
12.   1  begin  
13.   2    for i in 1..20000  
14.   3    loop  
15.   4      insert into t values(i,to_char(sysdate,'yyyymmddhhmmss'));  
16.   5      commit;  
17.   6    end loop;  
18.   7* end;  
19. hr@ORCL> /  
20.   
21. PL/SQL procedure successfully completed.  
22.   
23. hr@ORCL> create index idx_t on t (a) reverse;  
24.   
25. Index created.  
26.   
27. hr@ORCL> set autot on exp  
28.   
29. hr@ORCL> select * from t where a >=19989 and a <=19990;  
30.   
31.          A B  
32. ---------- --------------------   
33.      19989 20130224060219  
34.      19990 20130224060219  
35.   
36.   
37. Execution Plan  
38. ----------------------------------------------------------   
39. Plan hash value: 1601196873  
40.   
41. --------------------------------------------------------------------------   
42. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
43. --------------------------------------------------------------------------   
44. |   0 | SELECT STATEMENT  |      |     2 |    50 |    19   (6)| 00:00:01 |  
45. |*  1 |  TABLE ACCESS FULL| T    |     2 |    50 |    19   (6)| 00:00:01 |  
46. --------------------------------------------------------------------------   
47.   
48. Predicate Information (identified by operation id):  
49. ---------------------------------------------------   
50.   
51.    1 - filter("A">=19989 AND "A"<=19990)  
52.   
53. Note  
54. -----   
55.    - dynamic sampling used for this statement  
56.   
57. hr@ORCL> drop index idx_t;  
58.   
59. Index dropped.  
60.   
61. hr@ORCL> create index idx_t on t (a);  
62.   
63. Index created.  
64.   
65. hr@ORCL> analyze index idx_t compute statistics;  
66.   
67. Index analyzed.  
68.   
69. hr@ORCL> select * from t where a >=19989 and a <=19990;  
70.   
71.          A B  
72. ---------- --------------------   
73.      19989 20130224060219  
74.      19990 20130224060219  
75.   
76.   
77. Execution Plan  
78. ----------------------------------------------------------   
79. Plan hash value: 1594971208  
80.   
81. -------------------------------------------------------------------------------------   
82. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
83. -------------------------------------------------------------------------------------   
84. |   0 | SELECT STATEMENT            |       |     2 |    50 |     3   (0)| 00:00:01 |  
85. |   1 |  TABLE ACCESS BY INDEX ROWID| T     |     2 |    50 |     3   (0)| 00:00:01 |  
86. |*  2 |   INDEX RANGE SCAN          | IDX_T |     2 |       |     2   (0)| 00:00:01 |  
87. -------------------------------------------------------------------------------------   
88.   
89. Predicate Information (identified by operation id):  
90. ---------------------------------------------------   
91.   
92.    2 - access("A">=19989 AND "A"<=19990)  
93.   
94. Note  
95. -----   
96.    - dynamic sampling used for this statement  

       ㈣ 什么时候使用它？
          
          反向索引主要是建立在那些以序列号生成的列上，可以将本来是连在一起的index entry分散到不同的leaf block中去
          当索引是从序列中取的时候，如果是一般的b-tree 索引，在大量的插入后会导致块的分裂以及树的倾斜，使用reverse key index可以使索引段条目被更均匀的分布
          
          
       所以，reverse index主要是缓解右向增长的索引右侧叶子节点的争用,对于查询意义不大,注意reverse索引可能导致无法走range scan
       但用于解决被索引引起的热块问题倒是很不错的！