oracle hint_skip scan_index_ss

oracle hint 系列3
oracle skip scan
1,適用於組合索引
2,僅應用於組合索引的非前導列
3,會根據組合索引的前導列的不重複值把組合索引分為多個邏輯上的子索引
4,如果前導列選擇性差,非前導列選擇列高,最能發揮效能
5,可以看到如果不走skip scan,成本陡增N倍

oracle11g自動選擇oracle skip scan
SQL> explain plan for select * from t_skip where emp_id=59;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 796694006

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |     1 |     8 |     4   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_T_SKIP |     1 |     8 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - access("EMP_ID"=59)
       filter("EMP_ID"=59)

已選擇14行。

SQL> select /*+ no_index_ss(t_skip idx_t_skip) */ * from t_skip where emp_id=59;

       SEX     EMP_ID
---------- ----------
         1         59

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  ff8p1zzyaxyz3, child number 0
-------------------------------------
select /*+ no_index_ss(t_skip idx_t_skip) */ * from t_skip where
emp_id=59

Plan hash value: 384721346

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |   307 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  1 |  TABLE ACCESS FULL| T_SKIP |     1 |     8 |   307   (3)| 00:00:04 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMP_ID"=59)

已選擇19行。

SQL> select /*+ index_ss(t_skip idx_t_skip) */ * from t_skip where emp_id=59;

       SEX     EMP_ID
---------- ----------
         1         59

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  4hh6sp1m5v6vd, child number 0
-------------------------------------
select /*+ index_ss(t_skip idx_t_skip) */ * from t_skip where emp_id=59

Plan hash value: 796694006

-------------------------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |       |       |     4 (100)|          |
|*  1 |  INDEX SKIP SCAN | IDX_T_SKIP |     1 |     8 |     4   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP_ID"=59)
       filter("EMP_ID"=59)

已選擇19行。