INDEX SKIP SCAN:跳跃式索引能够通过使用非前缀列提高索引的查询性能.通常情况下,扫描索引块比扫描表数据块快.
跳跃式索引能够使复合索引逻辑上拆分分一个个小的子索引.在跳跃式扫描中,复合索引的首列不会被限定在查询中,因为它是跳跃式的.
数据库确定了一定数量的逻辑子索引通过在首列的去重的值中.当数据库中复合索引的前导列有很少的去重的值和索引中有非前导列有很多重复的值时跳跃式扫描就会有很多优点.
当复合索引的前导列没有被查询谓词界定时,数据库会选择跳跃式索引.

INDEX FULL SCAN:索引全扫描会排除排序操作,因为数据已经被按索引键值排序.读数据块是逐个读的.数据库在如下任何一个情况时可能会使用full scan:
An ORDER BY clause that meets the following requirements is present in the query(有order by子句存在于查询中):
All of the columns in the ORDER BY clause must be in the index.(在order by中的所有列必须被索引)
The order of the columns in the ORDER BY clause must match the order of the leading index columns.(在order by子句中排序的列必须匹配排序的前导列)
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.(order by子句中包含在索引中的所有列或者索引列的一个子集)

举例:

SESSION1:
SQL> create table  emp5 as select * from emp;

Table created.

SQL> create index skip1 on emp5(job,empno);

Index created.

SQL> commit;

Commit complete.

SQL> set autotrace traceon exp stat;
SQL> select count(*) from emp5 where empno=7900;


Execution Plan
----------------------
Plan hash value: 853491148

--------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |    13 |            |          |
|*  2 |   INDEX FULL SCAN| SKIP1 |     1 |    13 |     1   (0)| 00:00:01 |
--------------------------------------

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

   2 - access("EMPNO"=7900)
       filter("EMPNO"=7900)

Note
-----
   - dynamic sampling used for this statement (level=2) -------此处为动态信息采集,因为表没有被分析.


Statistics
----------------------
          5  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
----------可以看出新建表在没有被分析的情况下,默认是走INDEX FULL SCAN.
----------此时再开一个session2,执行如下的查询:
SESSION2:
SQL> set autotrace traceon exp stat;
SQL> set serveroutput on
SQL> select /*+ index_ss(emp5 skip1) */ count(*) from  emp5 where empno=7900;-----此处加hint强制走index skip scan,和上面的index full scan形成对比


Execution Plan
----------------------
Plan hash value: 3156092949

------------