【索引】索引五种扫描方式之索引跳跃扫描

示例一：


SQL> create table t4 as select object_id id, object_name name ,object_type type,created from dba_objects;


表已创建。


SQL> create index idx_t4_cmp on t4(id,name,type);


索引已创建。


SQL> exec dbms_stats.gather_table_stats('HR','T4');


PL/SQL 过程已成功完成。


SQL>


SQL> select * from t4 where name='T1' and type='TABLE';




执行计划
----------------------------------------------------------
Plan hash value: 2560505625


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    86   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T4   |     1 |    45 |    86   (4)| 00:00:02 |  <---索引被摒弃，走的全表扫描
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("NAME"='T1' AND "TYPE"='TABLE')


示例二：


SQL> create table t5 as select owner, object_name name ,object_type type,created from dba_objects;


表已创建。


SQL> create index idx_t5_cmp on t5(owner,name,type);


索引已创建。


SQL> exec dbms_stats.gather_table_stats('HR','T5');


PL/SQL 过程已成功完成。


SQL> 


SQL> select * from t5 where name='T1' and type='TABLE';




执行计划
----------------------------------------------------------
Plan hash value: 4071942617


------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    46 |    24   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5         |     1 |    46 |    24   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_T5_CMP |     1 |       |    23   (0)| 00:00:01 |  <---即使谓词上没有指定引导列，索引同样被使用
------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("NAME"='T1' AND "TYPE"='TABLE')
       filter("NAME"='T1' AND "TYPE"='TABLE')


比较t4和t5，发现一个走的是索引而一个走的是全表扫描。为什么？


Oracle 10g的文档如下：


Index skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index be split logically into smaller subindexes. 
In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.


--skip scan 让组合索引（composite index）逻辑的split 成几个子索引。如果在在查询时，第一个列没有指定，就跳过它。


The number of logical subindexes is determined by the number of distinct values in the initial column. 
Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and 
many distinct values in the nonleading key of the index.


--建议将distinct值小的列作为组合索引的引导列，即第一列。这样即使在where中引导列没有被使用，索引同样也会发生作用。不过此时就跳过了引导列。


总结：


1. 组合的建立，一定要将distinct值小的列作为组合索引的引导列。


2. 索引跳跃扫描仅仅是在组合索引下发生，并且组合索引的引导列，即第一列没有指定，而指定的非引导列情况下。