在前面的两篇中，分别描述了Index Unique Scan和Index Range Scan，今天来描述Index Skip Scan。
所谓的Index Skip Scan就是指，查询的WHERE条件中“SKIP”【跳过，没带上】了复合索引的前导列，只是指定了复合索引的其它列，这样CBO选择Index Skip Scan的路径来访问数据的方式。
如果复合索引的前导列唯一值越少（选择性低），非前导列索引唯一值越多（选择性高），则Index Skip Scan的性能越优。
事实上，在处理的过程中，Oracle将该复合索引上分为若干个logical subindexes(逻辑子索引)，逻辑子索引的具体个数取决于前导列的唯一值值个数，即前导列有几个重复值就分为几个逻辑子索引。
Index Skip Scan适用的场景：
1 Index一定是一个复合索引（索引类型可以是UNIQUE的唯一索引，也可以是NONUIQUE的非唯一索引）；
2 且复合索引的前导列重复值很多（即唯一值少），非前导列的重复值很少（唯一值多）；
3 WHERE限制条件中，并没有带上该符合索引的前导列，仅仅是使用非前导列来过滤数据；
测试与验证：
1 创建测试表：
SQL> conn hr/hr
Connected.
SQL> create table skip_t as select object_id,object_name,object_type from all_objects;
Table created.
SQL> select count(*) from skip_t;
COUNT(*)
----------
55683
SQL> select count(distinct object_type) from skip_t;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
25
SQL> select count(distinct object_id) from skip_t;
COUNT(DISTINCTOBJECT_ID)
------------------------
55683
SQL> select count(distinct object_name) from skip_t;
COUNT(DISTINCTOBJECT_NAME)
--------------------------
30902
SQL>
从上，看到skip_t表数据情况：有55683条记录，且OBJECT_ID字段均唯一，无重复值。OBJECT_TYPE唯一值只有25个。
2 接下来，在OBJECT_TYPE和OBJECT_ID字段上创建复合索引，并收集统计信息：
SQL> create index idx_skip_t on skip_t(object_type,object_id); Index created. SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'skip_t',cascade=>true); PL/SQL procedure successfully completed. SQL>
3 “Skip”掉复合索引前导列的情况,即让CBO按照预期的选择Index Skip Scan的路径来访问数据：
SQL> set autotrace trace
SQL> select object_type,object_id from skip_t where object_id=30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2119292092
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 26 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_SKIP_T | 1 | 15 | 26 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=30000)
filter("OBJECT_ID"=30000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
611 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select object_type,object_name from skip_t where object_id=30000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1237951313
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 27 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SKIP_T | 1 | 40 | 27 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_SKIP_T | 1 | | 26 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=30000)
filter("OBJECT_ID"=30000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
640 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
4 使用索引前导列的情况：
SQL> select object_type,object_id from skip_t where object_type='TABLE';
136 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3840590361
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2227 | 33405 | 9 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_SKIP_T | 2227 | 33405 | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
3592 bytes sent via SQL*Net to client
623 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
136 rows processed
SQL> select object_type,object_name from skip_t where object_type='TABLE';
136 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1483414757
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2227 | 80172 | 43 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| SKIP_T | 2227 | 80172 | 43 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_SKIP_T | 2227 | | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
6671 bytes sent via SQL*Net to client
623 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
136 rows processed
SQL>
