【SQL 提示 之二】index_ss Index Skip Hint

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