我们在使用一个B*树索引，而且谓词中没有使用索引的最前列。
如果这种情况，可以假设有一个表T，在T(x,y)上有一个索引。要做以下查询：select * from t where y=5。此时，优化器就不打算使用T(x,y)上的索引，因为谓词中不涉及X列。在这种情况下，倘若使用索引，可能就必须查看每个索引条目，而优化器通常更倾向于对T表做一个全表扫描。
zx@ORCL>create table t as select rownum x,rownum+1 y,rownum+2 z from dual connect by level < 100000;
Table created.
zx@ORCL>select count(*) from t;
COUNT(*)
----------
99999
zx@ORCL>create index idx_t on t(x,y);
Index created.
zx@ORCL>exec dbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQL procedure successfully completed.
zx@ORCL>set autotrace traceonly explain
--where条件使用y=5
zx@ORCL>select * from t where y=5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 80 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 15 | 80 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=5)
--where条件使用x=5
zx@ORCL>select * from t where x=5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 15 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=5)
但这并不完全排除使用索引。如果查询是select x,y from t where y=5，优化器就会注意到，它不必全面扫描表来得到X或Y(x和y都在索引中)，对索引本身做一个民快速的全面扫描会更合适，因为这个索引一般比底层表小得多。还要注意，仅CBO能使用这个访问路径。
zx@ORCL>select x,y from t where y=5;
Execution Plan
----------------------------------------------------------
Plan hash value: 2497555198
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 81 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_T | 1 | 10 | 81 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=5)
另一种情况下CBO也会使用T(x,y)上的索引，这就是索引跳跃式扫描。当且仅当索引的最前列(在上面的例子中最前列是x)只有很少的几个不同值，而且优化器了解这一点，跳跃式扫描(skip scan)就能很好地发挥作用。例如，考虑(GEMDER,EMPNO)上的一个索引，其中GENDER可取值有M和F，而且EMPNO是唯一的。对于以下查询:
select * from t where empno=5;
可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询，这说明从概念上讲这个查询会如下处理：
select * from t where GENDER='M' and empno=5
union all
select * from t where GENDER='F' and empno=5
它会跳跃式地扫描索引，以为这是两个索引：一个对应值M，另一个对应值F。
zx@ORCL>create table t1 as select decode(mod(rownum,2),0,'M','F') gender,all_objects.* from all_objects;
Table created.
zx@ORCL>create index idx_t1 on t1(gender,object_id);
Index created.
zx@ORCL>exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
PL/SQL procedure successfully completed.
zx@ORCL>set autotrace traceonly explain
zx@ORCL>select * from t1 where object_id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 4072187533
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_T1 | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
filter("OBJECT_ID"=42)