skip scan会探测出索引前导列的唯一值个数，每个唯一值都会作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询。例如：
表t1中有一个组合索引(owner,object_id)，其中owner只有test和sys两个值。在索引跳跃的情况下，我们可以逻辑上把他们看成两个索引，一个是（'TEST'，object_id)，一个是('SYS'，object_id).
select * from t1 where object_id=1;
发出这个查询后，oracle先进入owner为test的入口，查找object_id=1的条目。再进入owner为sys的入口，查找object_id=1的条目。最后合并两个结果集。

另外可以使用index_ss来强制选择skip index scan索引。

 

一个疑惑：

SQL> create table t1 as select * from dba_objects;

SQL> create index i_t1_owner on t1(owner,object_id);

SQL> exec dbms_stats.gather_table_stats(user,'t1');

 

SQL> select owner,count(*) from t1 group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
PUBLIC                               3360
OUTLN                                   9
TEST                                    4
SYSTEM                                527
ORACLE_OCM                              8
SCOTT                                   6
DBSNMP                                 55
APPQOSSYS                               5
SYS                                  9094
WMSYS                                 318

已选择10行。

 

SQL> set autot trace

SQL> select count(*) from t1 where object_id=2 and owner>'TEST';

已用时间:  00: 00: 00.01

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

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |            |     1 |    10 |            |          |
|*  2 |   INDEX SKIP SCAN| I_T1_OWNER |     1 |    10 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("OWNER">'TEST' AND "OBJECT_ID"=2 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=2)

统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        422  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

为什么这里明明使用了整个索引，为什么还是走了index skip scan呢？不是应该index range scan吗？

从谓词信息里看：

   2 - access("OWNER">'TEST' AND "OBJECT_ID"=2 AND "OWNER" IS NOT NULL)
       filter("OBJECT_ID"=2)

虽然扫描了整个索引块，但是却对索引块进行了 filter("OBJECT_ID"=2)过滤。