???? INDEX FULL SCAN 與 INDEX FAST FULL SCAN兩個(gè)長相差不多，乃是一母同胞，因此既有其共性，也有其個(gè)性。兩者來說其共性是不用掃描
表而是通過索引就可以直接返回所需要的所有數(shù)據(jù)。這對提高查詢性能而言，無疑是一個(gè)難得的數(shù)據(jù)訪問方式之一，因?yàn)樗饕写鎯Φ臄?shù)據(jù)通常
是遠(yuǎn)小于原始表的數(shù)據(jù)。下面具體來看看兩者之間的異同。

?

一、何時(shí)INDEX FULL SCAN 或 INDEX FAST FULL SCAN
? ?1、select 與where子句中出現(xiàn)的所有列必須存在索引
? ?2、查詢返回的數(shù)據(jù)行總數(shù)占據(jù)整個(gè)索引10%以上的比率。取決于db_file_multiblock_read_count值與并行度的值
? ?3、滿足像統(tǒng)計(jì)行數(shù)這樣的一些特定的標(biāo)準(zhǔn)，如count(*)這樣的操作。count(*)操作幾乎總是使用INDEX FAST FULL SCAN
?? 4、對于索引列上order by之類的操作幾乎總是使用INDEX FULL SCAN

?? 注： 參數(shù)db_file_multiblock_read_count會在index fast full scan 像full table scan一樣生效，因?yàn)檎麄€(gè)索引都被訪問，Oracle 此時(shí)
?允許出現(xiàn)多塊讀(multiblock_read)。db_file_multiblock_read_count與paralle僅僅對index fast full scan情形。其次paralle在新版Oracle
?中是否支持待證實(shí)。一旦上述幾個(gè)條件滿足，基于成本的優(yōu)化器根據(jù)表和索引的統(tǒng)計(jì)信息來調(diào)用 index full scan 或者index fast full scan。
?對于index fast full scan可以通過使用提示index_ffs來實(shí)現(xiàn)。
?
二、何謂INDEX FULL SCAN 與 INDEX FAST FULL SCAN??

-->創(chuàng)建演示表t scott@CNMMBO> create table t as select * from dba_objects where 1=2; -->為表t填充數(shù)據(jù) 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上的統(tǒng)計(jì)信息 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 | -------------------------------------------------------------------------- --從上面的執(zhí)行計(jì)劃中可知，此時(shí)走了全表掃描。 --由于我們需要查詢的列為object_id，因此理論上只需要讀取索引就應(yīng)該可以返回所有數(shù)據(jù)，而此時(shí)為什么是全表掃描呢？ --這是因?yàn)镹ULL值與索引的特性所決定的。即null值不會被存儲到B樹索引。因此應(yīng)該為表 t 的列 object_id 添加 not null 約束。 有關(guān)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); --添加約束后的執(zhí)行計(jì)劃 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 | -------------------------------------------------------------------------------------- --從上面的執(zhí)行計(jì)劃可知，此時(shí)走的是索引快速全掃描，整個(gè)cost比全表掃描呈數(shù)量級下降 INDEX FAST FULL SCAN 類似于full table scan，使用該方式當(dāng)在高速緩存中沒有找到所需的索引塊時(shí)，則根據(jù)db_file_multiblock_read_count的值進(jìn)行多塊讀操 作。對于索引的分支結(jié)構(gòu)只是簡單的獲取，然后掃描所有的葉結(jié)點(diǎn)。其結(jié)果是導(dǎo)致索引結(jié)構(gòu)沒有訪問，獲取的數(shù)據(jù)沒有根據(jù)索引鍵的順序排序。 INDEX FAST FULL SCAN使用multiblock_read，故產(chǎn)生db file scattered reads 事件。 --對于上面的情形能否使用索引全掃描方式來實(shí)現(xiàn)呢？答案是肯定的，需要增加一個(gè)提示 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會完全按照索引存儲的順序依次訪問整個(gè)索引樹。當(dāng)訪問到葉結(jié)點(diǎn)之后，按照雙向 鏈表方式讀取相連節(jié)點(diǎn)的值。換言之，對于索引上所有的數(shù)據(jù)是按照有序的方式來讀取的。如果索引塊沒有在高速緩存中被找到時(shí)，則需要從數(shù) 據(jù)文件中單塊進(jìn)行讀取。對于需要讀取大量數(shù)據(jù)的全索引掃描而言，這將使其變得低效。INDEX FULL SCAN使用single read，故產(chǎn)生 db file sequential reads事件。新版的Oracle支持db file parallel reads方式。

三、INDEX FULL SCAN 與 INDEX FAST FULL SCAN 兩者的差異

--還是使用上面的查詢，我們?yōu)樵瓉淼恼Z句增加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 --當(dāng)增加order by子句之后，我們發(fā)現(xiàn)此時(shí)未添加任何提示的情形下，CBO優(yōu)化器選擇了INDEX FULL SCAN方式進(jìn)行掃描 --觀察上面的統(tǒng)計(jì)信息可知，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 --執(zhí)行計(jì)劃中提示得以生效，即按照INDEX FAST FULL SCAN方式掃描 --注意執(zhí)行計(jì)劃中的第2步為SORT ORDER BY操作，而對于INDEX FULL SCAN操作則沒有這樣一部。此執(zhí)行計(jì)劃中多出一列TempSpc，值為1208K --此時(shí)的consistent gets為118，較3467呈數(shù)量級下降，其次可以看到sorts (memory)的值為1，而上一步的sorts (memory)的值為0。 --下面使用提示使其按全表掃描方式來觀察其統(tǒng)計(jì)信息 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 --執(zhí)行計(jì)劃中按全表方式讀取數(shù)據(jù) --全表掃描方式等同于第二步的INDEX FAST FULL SCAN，在執(zhí)行計(jì)劃中的第二步多出了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 | -------------------------------------------------------------------------------------------- --從上面的執(zhí)行計(jì)劃中可以看出，只要是涉及到排序操作，Oracle會毫不猶豫地選擇INDEX FULL SCAN，因?yàn)镮NDEX FULL SCAN方式掃描一定是 --按創(chuàng)建索引是的方式來排序的。當(dāng)order by使用降序時(shí)，可以看到操作1種相應(yīng)的為降序操作INDEX FULL SCAN DESCENDING

四、聚合操作count(*)時(shí)的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(*)的注意事項(xiàng) 如果是基于可以為 null 值列進(jìn)行count，則該查詢優(yōu)化器會選擇包含該列的任意索引 如果是基于not null值列進(jìn)行count，或count(*)，則至少包含一個(gè)非null列且最小的索引會被原則，因?yàn)閚ull值不會被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');

五、位圖索引時(shí)的INDEX?FULL?SCAN 與 INDEX FAST?FULL SCAN??
??? 在使用位圖索引時(shí)，index full SCAN 與 index fast full與使用B樹索引有相同的表現(xiàn)。需要注意的是位圖索引存儲null值，在很大程度
上對索引進(jìn)行壓縮。其次位圖索引不支持降序掃描方式。此處不再演示位圖索引的index full scan 與 index fast full。

?

六、總結(jié)
?? 1、當(dāng)select和where中出現(xiàn)的列都存在索引是發(fā)生index full scan與index fast full scan的前提
?? 2、查詢返回的數(shù)據(jù)行總數(shù)占據(jù)整個(gè)索引10%以上的比率
?? 3、基于上述前提count(*)操作幾乎總是選擇index fast full scan，而索引列上的order by子句幾乎總是選擇index full scan
?? 4、index fast full scan使用多塊讀的方式讀取索引塊，產(chǎn)生db file scattered reads 事件，讀取時(shí)高效，但為無序讀取
?? 5、index full scan使用單塊讀方式有序讀取索引塊，產(chǎn)生db file sequential reads事件，當(dāng)采用該方式讀取大量索引全掃描，效率低下
?? 6、絕大多數(shù)情況下，index fast full scan性能優(yōu)于index full scan，但前者在有order by時(shí)，一定會存在對讀取的塊重新排序的過程
?? 7、index fast full scan通過犧牲內(nèi)存與臨時(shí)表空間換取性能，因此在內(nèi)存不足或飽和狀態(tài)應(yīng)進(jìn)行合理權(quán)衡

?

七、更多參考

NULL 值與索引(一)

NULL 值與索引(二)

SQL tuning 步驟

高效SQL語句必殺技

父游標(biāo)、子游標(biāo)及共享游標(biāo)

綁定變量及其優(yōu)缺點(diǎn)

dbms_xplan之display_cursor函數(shù)的使用

dbms_xplan之display函數(shù)的使用

執(zhí)行計(jì)劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執(zhí)行計(jì)劃

啟用 AUTOTRACE 功能

函數(shù)使得索引列失效

Oracle 綁定變量窺探

Oracle 自適應(yīng)共享游標(biāo)

INDEX FULL SCAN vs INDEX FAST FULL SCAN