index range scan,index fast full scan,index skip scan发生的条件

http://blog.youkuaiyun.com/robinson1988/article/details/4980611

ndex range scan(索引范围扫描)：

1.对于unique index来说，如果where 条件后面出现了<,> ,between ...and...的时候，那么就可能执行index range scan,如果where条件后面是=，那么就会执行index unique scan。

2.对于none unique index来说 如果where 条件后面出现了=,>,<,betweed...and...的时候，就有可能执行index range scan。

3.对于组合索引来说，如果where条件后面出现了组合索引的引导列，那么可能执行index range scan。

 

index fast full scan(索引快速全扫描)：

如果select 语句后面中的列都被包含在组合索引中，而且where后面没有出现组合索引的引导列，并且需要检索出大部分数据，那么这个时候可能执行index fast full scan。index fast full scan 发生的条件:

1.必须是组合索引。2.引导列不在where条件中

 

index skip scan(索引跳跃式扫描)

当查询可以通过组合索引得到结果，而且返回结果很少，并且where条件中没有包含索引引导列的时候，可能执行index skip scan

索引跳跃式扫描发生的条件:

1.必须是组合索引。

2.引导列没有出现在where条件中

 

下面通过一个简单的实验验证一下上诉理论:

SQL> create table test as select * from dba_objects;

表已创建。

SQL> create unique index ind_id on test(object_id);    ind_id是唯一索引

索引已创建。

SQL> create index ind_owner on test(owner);             ind_owner是非唯一索引

索引已创建。

SQL> create index ooo on test(owner,object_name,object_type);  ooo是组合索引

索引已创建。

SQL> exec dbms_stats.gather_table_stats('ROBINSON','TEST');

PL/SQL 过程已成功完成。

SQL> set autot trace
SQL> select owner from test where object_id=10;
执行计划
----------------------------------------------------------
Plan hash value: 2544773305

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SQL> select owner from test where object_id<10;

已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 1361604213

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     7 |    77 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     7 |    77 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ID |     7 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

对于唯一索引，发生index range scan的时候就是返回多行记录，where 后面有 >,<,between ..and..

SQL> select owner from test where owner='SCOTT';

已选择13行。
执行计划
----------------------------------------------------------
Plan hash value: 2280863269

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |  2936 | 17616 |     7   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_OWNER |  2936 | 17616 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

对于非唯一索引，即使where后面的限制条件是=,但是有可能返回多行，所以进行index range scan

SQL> select object_name,object_type from test where owner='ROBINSON';

已选择15行。
执行计划
----------------------------------------------------------
Plan hash value: 2845720098

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |  2936 |   114K|    23   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OOO  |  2936 |   114K|    23   (0)| 00:00:01 |
-------------------------------------------------------------------------

因为000不是唯一索引，而且where后面用到了索引ooo的引导列，所以进行index range scan.

SQL> select owner, object_name,object_type from test where object_name='EMP' ;
执行计划
----------------------------------------------------------
Plan hash value: 1799988433

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     2 |    80 |    19   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | OOO  |     2 |    80 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------

因为查询所需要的信息可以通过索引ooo获得，并且where后面没有引导列owner，而且返回的行数很少(这里只有一行)，所以CBO选择index skip scan,这里autotrace没有显示返回多少行，下面显示一下

SQL> set autot off
SQL> select owner, object_name,object_type from test where object_name='EMP' ;

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SCOTT                          EMP                  TABLE

SQL> select owner, object_name,object_type from test where object_type='INDEX';

已选择1701行。
执行计划
----------------------------------------------------------
Plan hash value: 3464522019

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1721 | 68840 |    70   (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| OOO  |  1721 | 68840 |    70   (3)| 00:00:01 |
-----------------------------------------------------------------------------

因为查询所需的信息可以通过索引ooo获得，并且where后面没有引导列owner,而且返回的行数较多(1701行)，所以CBO选择index fast full scan,这样避免了全表扫描。