索引跳躍掃描（Index Skip Scan）可以使用到複合索引的非字首索引列，達到改善效能的作用，前提是全表掃面的代價高於索引跳躍式掃描的代價。這裡給出使用HINT方法使SQL走索引跳躍掃描的方法。

1.初始化環境
1）建立表T
sec@ora10g> create table t(x number,y number);

Table created.

2）初始化1000條資料
sec@ora10g> insert into t select rownum,66 from dual connect by rownum<=1000;

1000 rows created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select * from t ;

         X          Y
---------- ----------
         1         66
         2         66
         3         66
……省略部分輸出……
       998         66
       999         66
      1000         66

1000 rows selected.

3）在表T上建立複合索引
sec@ora10g> create index t_i on t(x,y);

Index created.

4）對錶進行分析
sec@ora10g> analyze table t compute statistics;

Table analyzed.

2.使用HINT方法使SQL走索引跳躍掃描
sec@ora10g> explain plan for select /*+ index_ss(t t_i) */ * from t where y=66;

Explained.

sec@ora10g> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 597150364

-------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 1000 | 5000 | 1002   (1)| 00:00:13 |
|* 1 | INDEX SKIP SCAN | T_I | 1000 | 5000 | 1002   (1)| 00:00:13 |
-------------------------------------------------------------------------

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

   1 - access("Y"=66)
       filter("Y"=66)

14 rows selected.

3.不使用HINT檢視SQL語句的執行計劃
sec@ora10g> explain plan for select * from t where y=66;

Explained.

sec@ora10g> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3046511974

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 1000 | 5000 |     2   (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| T_I | 1000 | 5000 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter("Y"=66)

13 rows selected.

此時SQL使用的是INDEX FAST FULL SCAN方式來獲得的資料。

4.小結
瞭解並構造每一種SQL語句的執行計劃有助於我們深入瞭解SQL語句的執行方法，進而選擇最有效的方法檢索和處理資料。

Good luck.

secooler
11.09.13

-- The End --

【INDEX_SS】使用HINT使SQL用索引跳躍掃描（Index Skip Scan）方式快速獲取資料

相關文章