使用一个B*树索引，而且谓词中没有使用索引的最前列。

SQL> create table t as select object_id,object_name,object_type from dba_objects;
Table created.

--在object_id,object_name上建立索引

SQL> create index t_idx_1 on t(object_id,object_name);
Index created.
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> set autotrace traceonly

--全表扫描：select出来的字段里包含非索引键，Oracle会认为扫描索引不如全表扫描快速

SQL> select object_id,object_name,object_type from t where object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)|    Time  |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    2 |    64 |      13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |    2 |    64 |      13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 24 recursive calls
 0 db block gets
 53 consistent gets
 0 physical reads
 0 redo size
 544 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

--快速索引扫描：select出来的字段只包含索引键，Oracle会认为只扫描索引键比全表要快

SQL> select object_id,object_name from t where object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 3278571932
--------------------------------------------------------------------------------
| Id | Operation            | Name     | Rows | Bytes | Cost (%CPU)|     Time |
--------------------------------------------------------------------------------
| 0  | SELECT STATEMENT     |          |    2 |    42 |      12 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN | T_IDX_1  |    2 |    42 |      12 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 52 consistent gets
 0 physical reads
 0 redo size
 477 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

--更新使OBJECT_TYPE只包含几个有限值

SQL> set autotrace off
SQL> update t set object_type='INDEX' where object_type not in ('INDEX','TABLE','PACKAGE');
7585 rows updated.
SQL> commit;
Commit complete.
SQL> set autotrace traceonly
SQL> analyze table t compute statistics;
Table analyzed.

--哦，索引跳跃式扫描

SQL> select object_id,object_name,object_type from t where t.object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 602818099
---------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |     1|    27 |      5 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID | T        |     1|    27 |      5 (0) | 00:00:01 |
|* 2 | INDEX SKIP SCAN             | T_IDX_2  |     1|       |      4 (0) | 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_NAME"='T')
 filter("T"."OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 22 consistent gets
 0 physical reads
 0 redo size
 544 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed

--下面继续增大object_type可取值数看看，超过12个不同值就开始全表扫描了

SQL> set autotrace off
SQL> update t set object_type=chr(mod(rownum,12));
9930 rows updated.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> set autotrace traceonly
SQL> select object_id,object_name,object_type from t where t.object_name='T';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    22 |      13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |    1 |    22 |      13 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."OBJECT_NAME"='T')
Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 51 consistent gets
 0 physical reads
 0 redo size
 540 bytes sent via SQL*Net to client
 385 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed