索引跳跃扫描(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语句的执行方法,进而选择最有效的方法检索和处理数据。