Index Skip Hint 在如下情况下使用：当在一个联合索引中，某些谓词条件不在联合索引的第一列时比如 id，object_name 在where条件中使用了object_name 时，可以通过使用Index Skip Hint 来访问数据。
SQL> create table t as select 1 id ,object_name from dba_objects;
表已创建。
SQL> insert into t select 2 id ,object_name from dba_objects;
已创建68301行。
SQL> insert into t select 3 id ,object_name from dba_objects;
已创建68301行。
SQL> insert into t select 4 id ,object_name from dba_objects;
已创建68301行。
SQL> commit;
提交完成。
SQL> create index t_idx on t(id,object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL 过程已成功完成。
SQL> set autot trace exp stat
SQL> select * from t where object_name='T';
执行计划
----------------------------------------------------------
Plan hash value: 3670166625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 | 6 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_IDX | 7 | 189 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='T')
filter("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
129 recursive calls
0 db block gets
37 consistent gets
5 physical reads
0 redo size
531 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> select /*+ full(t) */ * from t where object_name='T';
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 | 337 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 7 | 189 | 337 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1232 consistent gets
295 physical reads
0 redo size
531 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> select * from t where object_name='T';
执行计划
----------------------------------------------------------
Plan hash value: 3670166625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 189 | 6 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_IDX | 7 | 189 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='T')
filter("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> create table t2 as select object_id id,object_name from dba_objects;
表已创建。
SQL> create index idx_t2 on t2 (id,object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade => true);
PL/SQL 过程已成功完成。
SQL> select * from t2 where object_name ='T';
执行计划
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 91 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 2 | 58 | 91 (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
129 recursive calls
0 db block gets
348 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
有以上两个执行计划可以看出t 表的id字段有4个不同的值，t2表上则有6万多个不同的值，对于前者，当谓词中没有联合索引的第一个字段是，cbo会选择index_ss。而对于第一个索引字段重复率很低的情况，选择index_ss 反而比fts 消耗跟多的资源。
SQL> select /*+ index_ss(t2 idx_t2) */ * from t2 where object_name='T';
执行计划
----------------------------------------------------------
Plan hash value: 2401255812
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 68326 (1)| 00:13:40 |
|* 1 | INDEX SKIP SCAN | IDX_T2 | 2 | 58 | 68326 (1)| 00:13:40 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='T')
filter("OBJECT_NAME"='T')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
387 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
