我们在使用一个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)