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行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-751319/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-751319/