Oracle 反向索引（反转建索引） 理解

创建两张相同结构的表，内部结构及数据均引用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条件进行范围查询时，采用反向索引的表，并没有使用索引，而是采用了全表扫面的方式进行检索。

Oracle 反向索引（反转建索引） 理解

标签：环境   查询   ndt   range   color   net   系统   select   ace