This concept is easier to understand if one imagines a prefix index to be similar to a partitioned table. In a partitioned object the partition key (in this case the leading column) defines which partition data is stored within. In the index case every row underneath each key (the prefix column) would be ordered under that key. Thus in a skip scan of a prefixed index, the prefixed value is skipped and the non-prefix columns are accessed as logical sub-indexes. The trailing columns are ordered within the prefix column and so a 'normal' index access can be done ignoring the prefix.
In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.
也就是说，索引跳跃式扫描及时通过逻辑子索引消除或跳过一个复合索引，这个时候复合索引可以认为化成了几个逻辑子索引。如果在where条件中没有使用前导列就会采用索引跳跃式扫描。
在看如下例子：
SQL> select dbms_metadata.get_ddl('INDEX','EMP_IDX1','AMY') FROM DUAL; DBMS_METADATA.GET_DDL('INDEX','EMP_IDX1','AMY') -------------------------------------------------------------------------------- CREATE INDEX "AMY"."EMP_IDX1" ON "AMY"."EMP" ("EMPNO", "ENAME", "SAL") PCT SQL> SQL> set autotrace trace exp SQL> SQL> select * from emp where sal=1250; Execution Plan ---------------------------------------------------------- Plan hash value: 954130750 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 2 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | EMP_IDX1 | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SAL"=1250) filter("SAL"=1250) SQL> [oracle@oracle-one ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 21 15:24:25 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> conn amy/rhys Connected. SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. SQL> select * from emp where sal=1250; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 SQL> alter session set events '10046 trace name context off'; Session altered. SQL> SQL> select * from v$diag_info; INST_ID NAME VALUE ---------- ------------------------------------------------------------ ------------------------------------------------------------ 1 Diag Enabled TRUE 1 ADR Base /opt/app/oracle 1 ADR Home /opt/app/oracle/diag/rdbms/rhys/RHYS 1 Diag Trace /opt/app/oracle/diag/rdbms/rhys/RHYS/tra