???? 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 自適應共享游標

INDEX FULL SCAN vs INDEX FAST FULL SCAN