oracle 反向键索引的原理和用途（减少索引热点块）！

oracle 反向键索引的原理和用途（减少索引热点块）！

原创  2012年03月05日 16:52:35

• 标签：
• oracle /
• sql /
• access /
• table /
• insert

• 8909

我们知道Oracle会自动为表的主键列建立索引，这个默认的索引是普通的B-Tree索引。对于主键值是按顺序(递增或递减)加入的情况，默认的B-Tree索引并不理想。这是因为如果索引列的值具有严格顺序时，随着数据行的插入，索引树的层级增长很快。搜索索引发生的I/O读写次数和索引树的层级数成正比，也就是说，一棵具有5个层级的B -Tree索引，在最终读取到索引数据时最多可能发生多达5次I/O操作。因而，减少索引的层级数是索引性能调整的一个重要方法。

如果索引列的数据以严格的有序的方式插入，那么B-Tree索引树将变成一棵不对称的"歪树"，如图 5所示：

而如果索引列的数据以随机值的方式插入，我们将得到一棵趋向对称的索引树，如图 6所示：

比较图 5和图 6，在图 5中搜索到A块需要进行5次I/O操作，而图 6仅需要3次I/O操作。
既然索引列数据从序列中获取，其有序性无法规避，但在建立索引时，Oracle允许对索引列的值进行反向，即预先对列值进行比特位的反向，如 1000,10001,10011,10111,1100经过反向后的值将是0001,1001,1101,0011。显然经过位反向处理的有序数据变得比较随机了，这样所得到的索引树就比较对称，从而提高表的查询性能。
但反向键索引也有它局限性：如果在WHERE语句中，需要对索引列的值进行范围性的搜索，如BETWEEN、<、>等，其反向键索引无法使用，此时，Oracle将执行全表扫描；只有对反向键索引列进行 <>和 = 的比较操作时，其反向键索引才会得到使用。

1.反向索引应用场合
1）发现索引叶块成为热点块时使用
通常，使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象，严重时将会导致系统性能下降。
2）在RAC环境中使用
当RAC环境中几个节点访问数据的特点是集中和密集，索引热点块发生的几率就会很高。如果系统对范围检索要求不是很高的情况下可以考虑使用反向索引技术来提高系统的性能。因此该技术多见于RAC环境，它可以显著的降低索引块的争用。

2.使用反向索引的优点
最大的优点莫过于降低索引叶子块的争用，减少热点块，提高系统性能。

3.使用反向索引的缺点
由于反向索引结构自身的特点，如果系统中经常使用范围扫描进行读取数据的话（例如在where子句中使用“between and”语句或比较运算符“>”“<”等），那么反向索引将不适用，因为此时会出现大量的全表扫描的现象，反而会降低系统的性能。

有时候可以通过改写sql语句来避免使用范围扫描，例如where id between 12345 and 12347，可以改写为where id in(12345,12346,12347)，CBO会把这样的sql查询转换为where id=12345 or id=12346 or id=12347,这对反向索引也是有效的。
4.通过一个小实验简单演示一下反向索引的创建及修改

[sql]  view plain  copy

1. SQL> select count(*) from t1;  
2.   
3.   COUNT(*)  
4. ----------  
5.          0  
6.   
7. SQL> select count(*) from t2;  
8.   
9.   COUNT(*)  
10. ----------  
11.          0  
12.   
13. SQL> select count(*) from t3;  
14.   
15.   COUNT(*)  
16. ----------  
17.    2000000  
18.   
19. SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes;  
20.   
21. INDEX_NAME                     INDEX_TYPE                  TABLE_NAME  
22. ------------------------------ --------------------------- ------------------------------  
23. PK_T2                          NORMAL/REV                  T2  
24. PK_T1                          NORMAL                      T1  

表t1是主键是正常的主键，表t2的主键是反向主键。现在我把表t3的数据分别插入到表t1和表t2

[sql]  view plain  copy

1. SQL> set timing on;  
2. SQL> set autotrace on;  
3. SQL> insert /* +append */ into t1 select * from t3;  
4.   
5. 已创建2000000行。  
6.   
7. 已用时间:  00: 01: 42.83  
8.   
9. 执行计划  
10. ----------------------------------------------------------  
11. Plan hash value: 4161002650  
12.   
13. ---------------------------------------------------------------------------------  
14. | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
15. ---------------------------------------------------------------------------------  
16. |   0 | INSERT STATEMENT         |      |  2316K|   485M| 19014   (1)| 00:03:49 |  
17. |   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |          |  
18. |   2 |   TABLE ACCESS FULL      | T3   |  2316K|   485M| 19014   (1)| 00:03:49 |  
19. ---------------------------------------------------------------------------------  
20.   
21. Note  
22. -----  
23.    - dynamic sampling used for this statement (level=2)  
24.   
25.   
26. 统计信息  
27. ----------------------------------------------------------  
28.       12305  recursive calls  
29.      538835  db block gets  
30.      203937  consistent gets  
31.       83057  physical reads  
32.   428323528  redo size  
33.         688  bytes sent via SQL*Net to client  
34.         614  bytes received via SQL*Net from client  
35.           3  SQL*Net roundtrips to/from client  
36.           2  sorts (memory)  
37.           0  sorts (disk)  
38.     2000000  rows processed  
39.   
40. SQL> commit;  
41.   
42. 提交完成。  
43.   
44. 已用时间:  00: 00: 00.04  
45.   
46. SQL> insert /* +append */ into t2 select * from t3;  
47.   
48. 已创建2000000行。  
49.   
50. 已用时间:  00: 02: 02.63  
51.   
52. 执行计划  
53. ----------------------------------------------------------  
54. Plan hash value: 4161002650  
55.   
56. ---------------------------------------------------------------------------------  
57. | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
58. ---------------------------------------------------------------------------------  
59. |   0 | INSERT STATEMENT         |      |  2316K|   485M| 19014   (1)| 00:03:49 |  
60. |   1 |  LOAD TABLE CONVENTIONAL | T2   |       |       |            |          |  
61. |   2 |   TABLE ACCESS FULL      | T3   |  2316K|   485M| 19014   (1)| 00:03:49 |  
62. ---------------------------------------------------------------------------------  
63.   
64. Note  
65. -----  
66.    - dynamic sampling used for this statement (level=2)  
67.   
68.   
69. 统计信息  
70. ----------------------------------------------------------  
71.        7936  recursive calls  
72.     6059147  db block gets  
73.      158053  consistent gets  
74.       56613  physical reads  
75.   790167468  redo size  
76.         689  bytes sent via SQL*Net to client  
77.         614  bytes received via SQL*Net from client  
78.           3  SQL*Net roundtrips to/from client  
79.           2  sorts (memory)  
80.           0  sorts (disk)  
81.     2000000  rows processed  
82.   
83. SQL> commit;  
84.   
85. 提交完成。  
86.   
87. 已用时间:  00: 00: 00.01  

可以看见：由于反向索引的数据块比较分散了后，db block gets要稍微高一些。热块的争用有所缓解，consistent gets有所下降，从203937下降到158053，减少了45884次。redo size 也变多了！再来做查询，来看看他们的区别。

[sql]  view plain  copy

1. SQL> set autotrace traceonly;  
2. SQL> select OBJECT_NAME from t1 where id = 100;  
3.   
4. 已用时间:  00: 00: 00.06  
5.   
6. 执行计划  
7. ----------------------------------------------------------  
8. Plan hash value: 1141790563  
9.   
10. -------------------------------------------------------------------------------------  
11. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
12. -------------------------------------------------------------------------------------  
13. |   0 | SELECT STATEMENT            |       |     1 |    79 |     0   (0)| 00:00:01 |  
14. |   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    79 |     0   (0)| 00:00:01 |  
15. |*  2 |   INDEX UNIQUE SCAN         | PK_T1 |     1 |       |     0   (0)| 00:00:01 |  
16. -------------------------------------------------------------------------------------  
17.   
18. Predicate Information (identified by operation id):  
19. ---------------------------------------------------  
20.   
21.    2 - access("ID"=100)  
22.   
23.   
24. 统计信息  
25. ----------------------------------------------------------  
26.           0  recursive calls  
27.           0  db block gets  
28.           4  consistent gets  
29.           3  physical reads  
30.           0  redo size  
31.         434  bytes sent via SQL*Net to client  
32.         416  bytes received via SQL*Net from client  
33.           2  SQL*Net roundtrips to/from client  
34.           0  sorts (memory)  
35.           0  sorts (disk)  
36.           1  rows processed  
37.   
38. SQL> select OBJECT_NAME from t1 where id > 100 and id < 200;  
39.   
40. 已选择99行。  
41.   
42. 已用时间:  00: 00: 01.10  
43.   
44. 执行计划  
45. ----------------------------------------------------------  
46. Plan hash value: 1249713949  
47.   
48. -------------------------------------------------------------------------------------  
49. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
50. -------------------------------------------------------------------------------------  
51. |   0 | SELECT STATEMENT            |       |    99 |  7821 |     1   (0)| 00:00:01 |  
52. |   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    99 |  7821 |     1   (0)| 00:00:01 |  
53. |*  2 |   INDEX RANGE SCAN          | PK_T1 |    99 |       |     1   (0)| 00:00:01 |  
54. -------------------------------------------------------------------------------------  
55.   
56. Predicate Information (identified by operation id):  
57. ---------------------------------------------------  
58.   
59.    2 - access("ID">100 AND "ID"<200)  
60.   
61. Note  
62. -----  
63.    - dynamic sampling used for this statement (level=2)  
64.   
65.   
66. 统计信息  
67. ----------------------------------------------------------  
68.           9  recursive calls  
69.           0  db block gets  
70.         140  consistent gets  
71.         189  physical reads  
72.        2356  redo size  
73.        2656  bytes sent via SQL*Net to client  
74.         482  bytes received via SQL*Net from client  
75.           8  SQL*Net roundtrips to/from client  
76.           0  sorts (memory)  
77.           0  sorts (disk)  
78.          99  rows processed  
79.   
80. SQL> select OBJECT_NAME from t2 where id = 100;  
81.   
82. 已用时间:  00: 00: 00.05  
83.   
84. 执行计划  
85. ----------------------------------------------------------  
86. Plan hash value: 1480579010  
87.   
88. -------------------------------------------------------------------------------------  
89. | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
90. -------------------------------------------------------------------------------------  
91. |   0 | SELECT STATEMENT            |       |     1 |    79 |     0   (0)| 00:00:01 |  
92. |   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    79 |     0   (0)| 00:00:01 |  
93. |*  2 |   INDEX UNIQUE SCAN         | PK_T2 |     1 |       |     0   (0)| 00:00:01 |  
94. -------------------------------------------------------------------------------------  
95.   
96. Predicate Information (identified by operation id):  
97. ---------------------------------------------------  
98.   
99.    2 - access("ID"=100)  
100.   
101.   
102. 统计信息  
103. ----------------------------------------------------------  
104.           1  recursive calls  
105.           0  db block gets  
106.           4  consistent gets  
107.           1  physical reads  
108.           0  redo size  
109.         434  bytes sent via SQL*Net to client  
110.         416  bytes received via SQL*Net from client  
111.           2  SQL*Net roundtrips to/from client  
112.           0  sorts (memory)  
113.           0  sorts (disk)  
114.           1  rows processed  
115.   
116. SQL> select OBJECT_NAME from t2 where id > 100 and id < 200;  
117.   
118. 已选择99行。  
119.   
120. 已用时间:  00: 00: 04.39  
121.   
122. 执行计划  
123. ----------------------------------------------------------  
124. Plan hash value: 1513984157  
125.   
126. --------------------------------------------------------------------------  
127. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
128. --------------------------------------------------------------------------  
129. |   0 | SELECT STATEMENT  |      |   336 | 26544 |  8282   (1)| 00:01:40 |  
130. |*  1 |  TABLE ACCESS FULL| T2   |   336 | 26544 |  8282   (1)| 00:01:40 |  
131. --------------------------------------------------------------------------  
132.   
133. Predicate Information (identified by operation id):  
134. ---------------------------------------------------  
135.   
136.    1 - filter("ID">100 AND "ID"<200)  
137.   
138. Note  
139. -----  
140.    - dynamic sampling used for this statement (level=2)  
141.   
142.   
143. 统计信息  
144. ----------------------------------------------------------  
145.          29  recursive calls  
146.           1  db block gets  
147.       60187  consistent gets  
148.       30335  physical reads  
149.        5144  redo size  
150.        2656  bytes sent via SQL*Net to client  
151.         482  bytes received via SQL*Net from client  
152.           8  SQL*Net roundtrips to/from client  
153.           0  sorts (memory)  
154.           0  sorts (disk)  
155.          99  rows processed  

可以看见，单个值查询的时候，表t1和表t2并无差别，但是范围查询的时候，表t1是INDEX RANGE SCAN，表t2是TABLE ACCESS FULL了。在数据库的优化中你经常会发现没有绝对的好，也没有绝对的差。

在考虑使用反向索引之前，大多数情况可以考虑对索引进行散列分区（hash）来减少索引叶块的争用。

反向索引：
 alter index id_inx rebuild reverse online;
 alter index id_inx rebuild online reverse;
 alter index name_inx rebuild online noreverse;