一 反向索引
1.1 反向索引的定义
- 反向索引作为B-tree索引的一个分支，主要是在创建索引时，针对索引列的索引键值进行字节反转，进而实现分散存放到不同叶子节点块的目的。
1.2 反向索引针对的问题
- 使用传统的B-tree索引，当索引的列是按顺序产生时，相应的索引键值会基本分布在同一个叶块中。当用户对该列进行操作时，难免会发生索引块的争用。
- 使用反向索引，将索引列的键值进行反转，实现顺序的键值分散到不同的叶块中，从而减少索引块的争用。
- 例如：键值1001、1002、1003，反转后1001、2001、3001，进而分散到不用的叶子节点块中。
1.3 反向索引应用场景
- 索引块成为热点块
- rac环境
- rac环境下中多节点访问访问数据呈现密集且集中的特点，索引热块的产生较高。
- 在范围检索不高的rac环境中使用反向索引可有效提高性能。
1.4 反向索引的优点与缺点
- 优点：降低索引叶子块的争用问题，提升系统性能。
- 缺点：对于范围检索，例如：between，>，<时，反向索引无法引用，进而导致全表扫面的产生，降低系统性能。
1.5 反向索引示例说明
-- 创建两张相同结构的表，内部结构及数据均引用scott用户下的emp表SQL> select count(*) from test01;
COUNT(*)
----------
SQL> select count(*) from test02;
COUNT(*)
----------
--针对表TEST01的empno列，添加B-tree索引
SQL> create index PK_TEST01 on TEST01(EMPNO);
Index created.
--针对表TEST02的empno列，添加反向索引
SQL> create index PK_REV_TEST02 on TEST02(EMPNO) REVERSE;
Index created.
--验证上面的索引，NORMAL/REV表明为反向索引
SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE from user_indexes where INDEX_NAME like '%TEST%';
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- -------------------- --------------------
TEST01 PK_TEST01 NORMAL
TEST02 PK_REV_TEST02 NORMAL/REV
--打开会话追踪
SQL> set autotrace traceonly
--相同条件查询，观察两表的执行计划
SQL> select * from TEST01 where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 515586510
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_TEST01 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("EMPNO"=7369)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL> select * from TEST02 where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 1053012716
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST02 | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_REV_TEST02 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("EMPNO"=7369)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
-- 相同范围条件查询，观察两表的执行计划
SQL> select * from TEST01 where empno between 7350 and 7500;
Execution Plan
----------------------------------------------------------
Plan hash value: 515586510
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST01 | 2 | 174 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_TEST01 | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- access("EMPNO">=7350 AND "EMPNO"<=7500)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
SQL> select * from TEST02 where empno between 7350 and 7500;
Execution Plan
----------------------------------------------------------
Plan hash value: 3294238222
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 174 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST02 | 2 | 174 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
- filter("EMPNO">=7350 AND "EMPNO"<=7500)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets0 redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
通过上面的示例可以看到，当使用between条件进行范围查询时，采用反向索引的表，并没有使用索引，而是采用了全表扫面的方式进行检索。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
