INDEX FULL SCAN vs INDEX FAST FULL SCAN

     INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多，乃是一母同胞，因此既有其共性，也有其个性。两者来说其共性是不用扫描
表而是通过索引就可以直接返回所需要的所有数据。这对提高查询性能而言，无疑是一个难得的数据访问方式之一，因为索引中存储的数据通常
是远小于原始表的数据。下面具体来看看两者之间的异同。

 

一、何时INDEX FULL SCAN 或 INDEX FAST FULL SCAN
   1、select 与where子句中出现的所有列必须存在索引
   2、查询返回的数据行总数占据整个索引10%以上的比率。取决于db_file_multiblock_read_count值与并行度的值
   3、满足像统计行数这样的一些特定的标准，如count(*)这样的操作。count(*)操作几乎总是使用INDEX FAST FULL SCAN
   4、对于索引列上order by之类的操作几乎总是使用INDEX FULL SCAN

   注： 参数db_file_multiblock_read_count会在index fast full scan 像full table scan一样生效，因为整个索引都被访问，Oracle 此时
 允许出现多块读(multiblock_read)。db_file_multiblock_read_count与paralle仅仅对index fast full scan情形。其次paralle在新版Oracle
 中是否支持待证实。一旦上述几个条件满足，基于成本的优化器根据表和索引的统计信息来调用 index full scan 或者index fast full scan。
 对于index fast full scan可以通过使用提示index_ffs来实现。
 
二、何谓INDEX FULL SCAN 与 INDEX FAST FULL SCAN  

-->创建演示表t scott@CNMMBO> create table t as select * from dba_objects where 1=2; -->为表t填充数据 scott@CNMMBO> insert into t select * from dba_objects where object_id is not null; scott@CNMMBO> commit; --为表t的object_id列添加索引 scott@CNMMBO> create index i_t_object_id on t(object_id); -->收集表t上的统计信息 scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true); scott@CNMMBO> set autot trace exp; scott@CNMMBO> select object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| 166 (1)| 00:00:02 | | 1 | TABLE ACCESS FULL| T | 50422 | 246K| 166 (1)| 00:00:02 | -------------------------------------------------------------------------- --从上面的执行计划中可知，此时走了全表扫描。 --由于我们需要查询的列为object_id，因此理论上只需要读取索引就应该可以返回所有数据，而此时为什么是全表扫描呢？ --这是因为NULL值与索引的特性所决定的。即null值不会被存储到B树索引。因此应该为表 t 的列 object_id 添加 not null 约束。 有关null值与索引请参考 NULL 值与索引(一) http://blog.csdn.net/robinson_0612/article/details/7437561 NULL 值与索引(二) http://blog.csdn.net/robinson_0612/article/details/7438397 --为列object_id添加 not null约束 scott@CNMMBO> alter table t modify(object_id not null); --添加约束后的执行计划 scott@CNMMBO> select object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 2036340805 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| 26 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 | 246K| 26 (0)| 00:00:01 | -------------------------------------------------------------------------------------- --从上面的执行计划可知，此时走的是索引快速全扫描，整个cost比全表扫描呈数量级下降 INDEX FAST FULL SCAN 类似于full table scan，使用该方式当在高速缓存中没有找到所需的索引块时，则根据db_file_multiblock_read_count的值进行多块读操 作。对于索引的分支结构只是简单的获取，然后扫描所有的叶结点。其结果是导致索引结构没有访问，获取的数据没有根据索引键的顺序排序。 INDEX FAST FULL SCAN使用multiblock_read，故产生db file scattered reads 事件。 --对于上面的情形能否使用索引全扫描方式来实现呢？答案是肯定的，需要增加一个提示 scott@CNMMBO> select /*+ index(t i_t_object_id) */ object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 431110666 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| 113 (1)| 00:00:02 | | 1 | INDEX FULL SCAN | I_T_OBJECT_ID | 50422 | 246K| 113 (1)| 00:00:02 | ---------------------------------------------------------------------------------- INDEX FULL SCAN 与INDEX FAST FULL SCAN所不同的是，INDEX FULL SCAN会完全按照索引存储的顺序依次访问整个索引树。当访问到叶结点之后，按照双向 链表方式读取相连节点的值。换言之，对于索引上所有的数据是按照有序的方式来读取的。如果索引块没有在高速缓存中被找到时，则需要从数 据文件中单块进行读取。对于需要读取大量数据的全索引扫描而言，这将使其变得低效。INDEX FULL SCAN使用single read，故产生 db file sequential reads事件。新版的Oracle支持db file parallel reads方式。

三、INDEX FULL SCAN 与 INDEX FAST FULL SCAN 两者的差异

--还是使用上面的查询，我们为原来的语句增加order by子句 scott@CNMMBO> set autot trace ; scott@CNMMBO> select object_id from t order by object_id ; 50422 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 431110666 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| 113 (1)| 00:00:02 | | 1 | INDEX FULL SCAN | I_T_OBJECT_ID | 50422 | 246K| 113 (1)| 00:00:02 | ---------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3467 consistent gets 0 physical reads 0 redo size 918087 bytes sent via SQL*Net to client 37463 bytes received via SQL*Net from client 3363 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50422 rows processed --当增加order by子句之后，我们发现此时未添加任何提示的情形下，CBO优化器选择了INDEX FULL SCAN方式进行扫描 --观察上面的统计信息可知，consistent gets 为3467，sorts (memory)为0 --下面通过提示来使得走INDEX FAST FULL SCAN扫描方式 scott@CNMMBO> select /*+ index_ffs(t i_t_object_id) */ object_id from t order by object_id; 50422 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2527678987 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| | 185 (3)| 00:00:03 | | 1 | SORT ORDER BY | | 50422 | 246K| 1208K| 185 (3)| 00:00:03 | | 2 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 | 246K| | 26 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 118 consistent gets 0 physical reads 0 redo size 918087 bytes sent via SQL*Net to client 37463 bytes received via SQL*Net from client 3363 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50422 rows processed --执行计划中提示得以生效，即按照INDEX FAST FULL SCAN方式扫描 --注意执行计划中的第2步为SORT ORDER BY操作，而对于INDEX FULL SCAN操作则没有这样一部。此执行计划中多出一列TempSpc，值为1208K --此时的consistent gets为118，较3467呈数量级下降，其次可以看到sorts (memory)的值为1，而上一步的sorts (memory)的值为0。 --下面使用提示使其按全表扫描方式来观察其统计信息 scott@CNMMBO> select /*+ full(t) */ object_id from t order by object_id; 50422 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 961378228 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| | 324 (2)| 00:00:04 | | 1 | SORT ORDER BY | | 50422 | 246K| 1208K| 324 (2)| 00:00:04 | | 2 | TABLE ACCESS FULL| T | 50422 | 246K| | 166 (1)| 00:00:02 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 726 consistent gets 0 physical reads 0 redo size 918087 bytes sent via SQL*Net to client 37463 bytes received via SQL*Net from client 3363 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50422 rows processed --执行计划中按全表方式读取数据 --全表扫描方式等同于第二步的INDEX FAST FULL SCAN，在执行计划中的第二步多出了SORT ORDER BY操作。以及列TempSpc，值为1208K --此次全表扫描的consistent gets为726，高于INDEX FAST FULL SCAN的consistent gets，低于INDEX FULL SCAN的consistent gets --下面是使用降序的情形 scott@CNMMBO> set autot trace exp; scott@CNMMBO> select object_id from t order by 1 desc; Execution Plan ---------------------------------------------------------- Plan hash value: 2808014233 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50422 | 246K| 113 (1)| 00:00:02 | | 1 | INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 50422 | 246K| 113 (1)| 00:00:02 | -------------------------------------------------------------------------------------------- --从上面的执行计划中可以看出，只要是涉及到排序操作，Oracle会毫不犹豫地选择INDEX FULL SCAN，因为INDEX FULL SCAN方式扫描一定是 --按创建索引是的方式来排序的。当order by使用降序时，可以看到操作1种相应的为降序操作INDEX FULL SCAN DESCENDING

四、聚合操作count(*)时的INDEX FULL SCAN 与 INDEX FAST FULL SCAN

scott@CNMMBO> set autot trace exp; scott@CNMMBO> select count(*) from t; Execution Plan ---------------------------------------------------------- Plan hash value: 3095383276 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 | 26 (0)| 00:00:01 | ------------------------------------------------------------------------------- scott@CNMMBO> select /*+ index(t i_t_object_id) */ count(*) from t; Execution Plan ---------------------------------------------------------- Plan hash value: 3079973526 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 113 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| I_T_OBJECT_ID | 50422 | 113 (1)| 00:00:02 | -------------------------------------------------------------------------- --> Author : Robinson Cheng --> Blog : http://blog.csdn.net/robinson_0612 使用count及count(*)的注意事项 如果是基于可以为 null 值列进行count，则该查询优化器会选择包含该列的任意索引 如果是基于not null值列进行count，或count(*)，则至少包含一个非null列且最小的索引会被原则，因为null值不会被B树索引存储。 --附寻找INDEX FULL SCAN的sql语句 SELECT p.sql_id,sql_text FROM v$sqlarea t, v$sql_plan p WHERE t.hash_value = p.hash_value AND p.operation = 'INDEX' AND p.options = 'FULL SCAN' and p.object_owner not in('SYS','SYSTEM');

五、位图索引时的INDEX FULL SCAN 与 INDEX FAST FULL SCAN  
    在使用位图索引时，index full SCAN 与 index fast full与使用B树索引有相同的表现。需要注意的是位图索引存储null值，在很大程度
上对索引进行压缩。其次位图索引不支持降序扫描方式。此处不再演示位图索引的index full scan 与 index fast full。

 

六、总结
   1、当select和where中出现的列都存在索引是发生index full scan与index fast full scan的前提
   2、查询返回的数据行总数占据整个索引10%以上的比率
   3、基于上述前提count(*)操作几乎总是选择index fast full scan，而索引列上的order by子句几乎总是选择index full scan
   4、index fast full scan使用多块读的方式读取索引块，产生db file scattered reads 事件，读取时高效，但为无序读取
   5、index full scan使用单块读方式有序读取索引块，产生db file sequential reads事件，当采用该方式读取大量索引全扫描，效率低下
   6、绝大多数情况下，index fast full scan性能优于index full scan，但前者在有order by时，一定会存在对读取的块重新排序的过程
   7、index fast full scan通过牺牲内存与临时表空间换取性能，因此在内存不足或饱和状态应进行合理权衡

 

七、更多参考

NULL 值与索引(一)

NULL 值与索引(二)

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标