转载
官方文档:
Anindex skip scanuses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.
理解:当符合索引的前导列重复之比较多的时候,即使在where字句中没有出现前导列,oracle也会时候符合索引,使用方法是index skip scan。
例子:
创建测试表
SQL> create table test2 as select * from all_objects;
表已创建。
SQL> create index idx_test2 on test2(object_type,object_id);
索引已创建。
SQL> set autot traceonly explain
SQL> select object_name from test2 where object_id-20;
为执行表分析前,查询不使用索引
SQL> select object_name from test2 where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 300966803
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 330 | 277 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST2 | 11 | 330 | 277 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=20)
Note
-----
- dynamic sampling used for this statement (level=2)
进行表分析
SQL> analyze table test2 compute statistics;
表已分析。
虽然where字句中没有用到idx_test2的前导列,但是oracle自动分析以index skip scan的方式使用idx_test2
SQL> select object_name from test2 where object_id=20;
执行计划
----------------------------------------------------------
Plan hash value: 3100316192
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 28 | 42 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 28 | 42 (0)| 0
0:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST2 | 1 | | 41 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
filter("OBJECT_ID"=20)
where字句中包含前导列,则以index range scan的方式使用idx_test2
SQL> select object_name from test2 where object_type='TABLE' and object_name='TEST2';
执行计划
----------------------------------------------------------
Plan hash value: 4047680367
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 32 | 79 (0)| 0
0:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 32 | 79 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST2 | 1776 | | 8 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='TEST2')
2 - access("OBJECT_TYPE"='TABLE')
SQL> spool off;
总结:当复合索引的前导列重复值很大的时候,如果在where字句中出现复合索引的非前导列并且没有使用前导列,则oracle会自动判断以index skip scan的方式使用复合索引。(注意:一般符合索引的前导列是重复值比较少的列,如果出现使用index skip scan,请分析复合索引创建的是否合理)。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/196700/viewspace-758447/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/196700/viewspace-758447/