理解index skip scan

官方文档：

Anindex skip scanuses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.

理解：当符合索引的前导列重复之比较多的时候，即使在where字句中没有出现前导列，oracle也会时候符合索引，使用方法是index skip scan。

例子：

创建测试表
SQL> create table test2 as select * from all_objects;

表已创建。

SQL> create index idx_test2 on test2(object_type,object_id);

索引已创建。

SQL> set autot traceonly explain
SQL> select object_name from test2 where object_id-20;
为执行表分析前，查询不使用索引
SQL> select object_name from test2 where object_id=20;

执行计划
----------------------------------------------------------                     
Plan hash value: 300966803                                                     
                                                                               
---------------------------------------------------------------------------    
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |    
---------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |       |    11 |   330 |   277   (1)| 00:00:04 |    
|*  1 |  TABLE ACCESS FULL| TEST2 |    11 |   330 |   277   (1)| 00:00:04 |    
---------------------------------------------------------------------------    
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   1 - filter("OBJECT_ID"=20)                                                  
                                                                               
Note                                                                           
-----                                                                          
   - dynamic sampling used for this statement (level=2)                        
进行表分析
SQL> analyze table test2 compute statistics;

表已分析。
虽然where字句中没有用到idx_test2的前导列，但是oracle自动分析以index skip scan的方式使用idx_test2
SQL> select object_name from test2 where object_id=20;

执行计划
----------------------------------------------------------                     
Plan hash value: 3100316192                                                    
                                                                               
--------------------------------------------------------------------------------
---------                                                                      
                                                                               
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |                                                                      
                                                                               
--------------------------------------------------------------------------------
---------                                                                      
                                                                               
|   0 | SELECT STATEMENT            |           |     1 |    28 |    42   (0)| 0
0:00:01 |                                                                      
                                                                               
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2     |     1 |    28 |    42   (0)| 0
0:00:01 |                                                                      
                                                                               
|*  2 |   INDEX SKIP SCAN           | IDX_TEST2 |     1 |       |    41   (0)| 0
0:00:01 |                                                                      
                                                                               
--------------------------------------------------------------------------------
---------                                                                      
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   2 - access("OBJECT_ID"=20)                                                  
       filter("OBJECT_ID"=20)                                                  
where字句中包含前导列，则以index range scan的方式使用idx_test2
SQL> select object_name from test2 where object_type='TABLE' and object_name='TEST2';

执行计划
----------------------------------------------------------                     
Plan hash value: 4047680367                                                    
                                                                               
--------------------------------------------------------------------------------
---------                                                                      
                                                                               
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |                                                                      
                                                                               
--------------------------------------------------------------------------------
---------                                                                      
                                                                               
|   0 | SELECT STATEMENT            |           |     1 |    32 |    79   (0)| 0
0:00:01 |                                                                      
                                                                               
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST2     |     1 |    32 |    79   (0)| 0
0:00:01 |                                                                      
                                                                               
|*  2 |   INDEX RANGE SCAN          | IDX_TEST2 |  1776 |       |     8   (0)| 0
0:00:01 |                                                                      
                                                                               
--------------------------------------------------------------------------------
---------                                                                      
                                                                               
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   1 - filter("OBJECT_NAME"='TEST2')                                           
   2 - access("OBJECT_TYPE"='TABLE')                                           

SQL> spool off;

总结：当复合索引的前导列重复值很大的时候，如果在where字句中出现复合索引的非前导列并且没有使用前导列，则oracle会自动判断以index skip scan的方式使用复合索引。（注意：一般复合索引的前导列是重复值比较少的列，如果出现使用index skip scan，请分析复合索引创建的是否合理）。