INDEX FULL SCAN vs INDEX FAST FULL SCAN

转自：http://blog.youkuaiyun.com/robinson_0612/article/details/7452310

    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  

[sql]  view plain copy print ?

1. -->创建演示表t  
2. scott@CNMMBO> create table t as select * from dba_objects where 1=2;  
3.   
4. -->为表t填充数据  
5. scott@CNMMBO> insert into t select * from dba_objects where object_id is not null;  
6.   
7. scott@CNMMBO> commit;  
8.   
9. --为表t的object_id列添加索引  
10. scott@CNMMBO> create index i_t_object_id on t(object_id);  
11.   
12. -->收集表t上的统计信息  
13. scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);  
14.   
15. scott@CNMMBO> set autot trace exp;  
16.   
17. scott@CNMMBO> select object_id from t;  
18.   
19. Execution Plan  
20. ----------------------------------------------------------  
21. Plan hash value: 1601196873  
22.   
23. --------------------------------------------------------------------------  
24. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
25. --------------------------------------------------------------------------  
26. |   0 | SELECT STATEMENT  |      | 50422 |   246K|   166   (1)| 00:00:02 |  
27. |   1 |  TABLE ACCESS FULL| T    | 50422 |   246K|   166   (1)| 00:00:02 |  
28. --------------------------------------------------------------------------  
29.   
30. --从上面的执行计划中可知，此时走了全表扫描。  
31. --由于我们需要查询的列为object_id，因此理论上只需要读取索引就应该可以返回所有数据，而此时为什么是全表扫描呢？  
32. --这是因为NULL值与索引的特性所决定的。即null值不会被存储到B树索引。因此应该为表 t 的列 object_id 添加 not null 约束。  
33. 有关null值与索引请参考  
34.     NULL 值与索引(一) http://blog.youkuaiyun.com/robinson_0612/article/details/7437561  
35.    NULL 值与索引(二) http://blog.youkuaiyun.com/robinson_0612/article/details/7438397  
36.   
37. --为列object_id添加 not null约束  
38. scott@CNMMBO> alter table t modify(object_id not null);  
39.   
40. --添加约束后的执行计划  
41. scott@CNMMBO> select object_id from t;  
42.   
43. Execution Plan  
44. ----------------------------------------------------------  
45. Plan hash value: 2036340805  
46.   
47. --------------------------------------------------------------------------------------  
48. | Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
49. --------------------------------------------------------------------------------------  
50. |   0 | SELECT STATEMENT     |               | 50422 |   246K|    26   (0)| 00:00:01 |  
51. |   1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 |   246K|    26   (0)| 00:00:01 |  
52. --------------------------------------------------------------------------------------    
53.   
54. --从上面的执行计划可知，此时走的是索引快速全扫描，整个cost比全表扫描呈数量级下降  
55.   
56. INDEX FAST FULL SCAN  
57.        类似于full table scan，使用该方式当在高速缓存中没有找到所需的索引块时，则根据db_file_multiblock_read_count的值进行多块读操  
58.     作。对于索引的分支结构只是简单的获取，然后扫描所有的叶结点。其结果是导致索引结构没有访问，获取的数据没有根据索引键的顺序排序。  
59.     INDEX FAST FULL SCAN使用multiblock_read，故产生db file scattered reads 事件。  
60.           
61. --对于上面的情形能否使用索引全扫描方式来实现呢？答案是肯定的，需要增加一个提示  
62. scott@CNMMBO> select /*+ index(t i_t_object_id) */ object_id from t;  
63.   
64. Execution Plan  
65. ----------------------------------------------------------  
66. Plan hash value: 431110666  
67.   
68. ----------------------------------------------------------------------------------  
69. | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
70. ----------------------------------------------------------------------------------  
71. |   0 | SELECT STATEMENT |               | 50422 |   246K|   113   (1)| 00:00:02 |  
72. |   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 50422 |   246K|   113   (1)| 00:00:02 |  
73. ----------------------------------------------------------------------------------  
74.   
75. INDEX FULL SCAN  
76.        与INDEX FAST FULL SCAN所不同的是，INDEX FULL SCAN会完全按照索引存储的顺序依次访问整个索引树。当访问到叶结点之后，按照双向  
77.     链表方式读取相连节点的值。换言之，对于索引上所有的数据是按照有序的方式来读取的。如果索引块没有在高速缓存中被找到时，则需要从数  
78.     据文件中单块进行读取。对于需要读取大量数据的全索引扫描而言，这将使其变得低效。INDEX FULL SCAN使用single read，故产生  
79.     db file sequential reads事件。新版的Oracle支持db file parallel reads方式。   

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

[sql]  view plain copy print ?

1. --还是使用上面的查询，我们为原来的语句增加order by子句  
2. scott@CNMMBO> set autot trace ;  
3. scott@CNMMBO> select object_id from t order by object_id ;  
4.   
5. 50422 rows selected.  
6.   
7. Execution Plan  
8. ----------------------------------------------------------  
9. Plan hash value: 431110666  
10.   
11. ----------------------------------------------------------------------------------  
12. | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
13. ----------------------------------------------------------------------------------  
14. |   0 | SELECT STATEMENT |               | 50422 |   246K|   113   (1)| 00:00:02 |  
15. |   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 50422 |   246K|   113   (1)| 00:00:02 |  
16. ----------------------------------------------------------------------------------  
17.   
18. Statistics  
19. ----------------------------------------------------------  
20.           1  recursive calls  
21.           0  db block gets  
22.        3467  consistent gets                     
23.           0  physical reads  
24.           0  redo size  
25.      918087  bytes sent via SQL*Net to client  
26.       37463  bytes received via SQL*Net from client  
27.        3363  SQL*Net roundtrips to/from client  
28.           0  sorts (memory)  
29.           0  sorts (disk)  
30.       50422  rows processed  
31.         
32. --当增加order by子句之后，我们发现此时未添加任何提示的情形下，CBO优化器选择了INDEX FULL SCAN方式进行扫描        
33. --观察上面的统计信息可知，consistent gets 为3467，sorts (memory)为0  
34.   
35. --下面通过提示来使得走INDEX FAST FULL SCAN扫描方式  
36. scott@CNMMBO> select /*+ index_ffs(t i_t_object_id) */ object_id from t order by object_id;  
37.   
38. 50422 rows selected.  
39.   
40. Execution Plan  
41. ----------------------------------------------------------  
42. Plan hash value: 2527678987  
43.   
44. -----------------------------------------------------------------------------------------------  
45. | Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
46. -----------------------------------------------------------------------------------------------  
47. |   0 | SELECT STATEMENT      |               | 50422 |   246K|       |   185   (3)| 00:00:03 |  
48. |   1 |  SORT ORDER BY        |               | 50422 |   246K|  1208K|   185   (3)| 00:00:03 |  
49. |   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 |   246K|       |    26   (0)| 00:00:01 |  
50. -----------------------------------------------------------------------------------------------  
51.   
52. Statistics  
53. ----------------------------------------------------------  
54.           1  recursive calls  
55.           0  db block gets  
56.         118  consistent gets  
57.           0  physical reads  
58.           0  redo size  
59.      918087  bytes sent via SQL*Net to client  
60.       37463  bytes received via SQL*Net from client  
61.        3363  SQL*Net roundtrips to/from client  
62.           1  sorts (memory)  
63.           0  sorts (disk)  
64.       50422  rows processed  
65.   
66. --执行计划中提示得以生效，即按照INDEX FAST FULL SCAN方式扫描  
67. --注意执行计划中的第2步为SORT ORDER BY操作，而对于INDEX FULL SCAN操作则没有这样一部。此执行计划中多出一列TempSpc，值为1208K  
68. --此时的consistent gets为118，较3467呈数量级下降，其次可以看到sorts (memory)的值为1，而上一步的sorts (memory)的值为0。  
69.   
70. --下面使用提示使其按全表扫描方式来观察其统计信息  
71. scott@CNMMBO> select /*+ full(t) */ object_id from t order by object_id;  
72.   
73. 50422 rows selected.  
74.   
75. Execution Plan  
76. ----------------------------------------------------------  
77. Plan hash value: 961378228  
78.   
79. -----------------------------------------------------------------------------------  
80. | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
81. -----------------------------------------------------------------------------------  
82. |   0 | SELECT STATEMENT   |      | 50422 |   246K|       |   324   (2)| 00:00:04 |  
83. |   1 |  SORT ORDER BY     |      | 50422 |   246K|  1208K|   324   (2)| 00:00:04 |  
84. |   2 |   TABLE ACCESS FULL| T    | 50422 |   246K|       |   166   (1)| 00:00:02 |  
85. -----------------------------------------------------------------------------------  
86.   
87. Statistics  
88. ----------------------------------------------------------  
89.           1  recursive calls  
90.           0  db block gets  
91.         726  consistent gets  
92.           0  physical reads  
93.           0  redo size  
94.      918087  bytes sent via SQL*Net to client  
95.       37463  bytes received via SQL*Net from client  
96.        3363  SQL*Net roundtrips to/from client  
97.           1  sorts (memory)  
98.           0  sorts (disk)  
99.       50422  rows processed  
100.   
101. --执行计划中按全表方式读取数据  
102. --全表扫描方式等同于第二步的INDEX FAST FULL SCAN，在执行计划中的第二步多出了SORT ORDER BY操作。以及列TempSpc，值为1208K  
103. --此次全表扫描的consistent gets为726，高于INDEX FAST FULL SCAN的consistent gets，低于INDEX FULL SCAN的consistent gets  
104.   
105. --下面是使用降序的情形  
106. scott@CNMMBO> set autot trace exp;  
107. scott@CNMMBO> select object_id from t order by 1 desc;  
108.   
109. Execution Plan  
110. ----------------------------------------------------------  
111. Plan hash value: 2808014233  
112.   
113. --------------------------------------------------------------------------------------------  
114. | Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |  
115. --------------------------------------------------------------------------------------------  
116. |   0 | SELECT STATEMENT           |               | 50422 |   246K|   113   (1)| 00:00:02 |  
117. |   1 |  INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 50422 |   246K|   113   (1)| 00:00:02 |  
118. --------------------------------------------------------------------------------------------  
119.   
120. --从上面的执行计划中可以看出，只要是涉及到排序操作，Oracle会毫不犹豫地选择INDEX FULL SCAN，因为INDEX FULL SCAN方式扫描一定是  
121. --按创建索引是的方式来排序的。当order by使用降序时，可以看到操作1种相应的为降序操作INDEX FULL SCAN DESCENDING  

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

[sql]  view plain copy print ?

1. scott@CNMMBO> set autot trace exp;  
2. scott@CNMMBO> select count(*) from t;  
3.   
4. Execution Plan  
5. ----------------------------------------------------------  
6. Plan hash value: 3095383276  
7.   
8. -------------------------------------------------------------------------------  
9. | Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |  
10. -------------------------------------------------------------------------------  
11. |   0 | SELECT STATEMENT      |               |     1 |    26   (0)| 00:00:01 |  
12. |   1 |  SORT AGGREGATE       |               |     1 |            |          |  
13. |   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 |    26   (0)| 00:00:01 |  
14. -------------------------------------------------------------------------------  
15.   
16. scott@CNMMBO> select /*+ index(t i_t_object_id) */ count(*) from t;  
17.   
18. Execution Plan  
19. ----------------------------------------------------------  
20. Plan hash value: 3079973526  
21.   
22. --------------------------------------------------------------------------  
23. | Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     |  
24. --------------------------------------------------------------------------  
25. |   0 | SELECT STATEMENT |               |     1 |   113   (1)| 00:00:02 |  
26. |   1 |  SORT AGGREGATE  |               |     1 |            |          |  
27. |   2 |   INDEX FULL SCAN| I_T_OBJECT_ID | 50422 |   113   (1)| 00:00:02 |  
28. --------------------------------------------------------------------------  
29. --> Author : Robinson Cheng  --> Blog : http://blog.youkuaiyun.com/robinson_0612  
30. 使用count及count(*)的注意事项  
31.     如果是基于可以为 null 值列进行count，则该查询优化器会选择包含该列的任意索引  
32.     如果是基于not null值列进行count，或count(*)，则至少包含一个非null列且最小的索引会被原则，因为null值不会被B树索引存储。  
33.   
34. --附寻找INDEX FULL SCAN的sql语句  
35. SELECT p.sql_id,sql_text  
36. FROM   v$sqlarea t, v$sql_plan p  
37. WHERE  t.hash_value = p.hash_value AND p.operation = 'INDEX' AND p.options = 'FULL SCAN'  
38. 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通过牺牲内存与临时表空间换取性能，因此在内存不足或饱和状态应进行合理权衡