Oracle執行計劃——使用index full scan的幾種情況

常見有三種情況都有用到index full scan.

1. 查詢列就是索引列

2. 對索引列進行order by時

3. 對索列進行聚合計算時

通過案例學調優之--Index FULL SCAN和Index FAST FULL SCAN

Index FULL SCAN 和ndex FAST FULL SCAN工作原理：    

   Index FULL SCAN 和Index FAST FULL SCAN的適用情況：適用于我們想選擇的列都包含在索引里邊時，這時候就可以使用IFS或者FFS來代替全表掃描來得到想要的結果。

     INDEX FULL SCAN：
HINT寫法：INDEX(表名 索引名)
原理：ORACLE定位到索引的ROOT BLOCK，然后到BRANCH BLOCK（如果有的話），再定位到第一個LEAF BLOCK, 然后根據LEAF BLOCK的雙向鏈表順序讀取。它所讀取的塊都是有順序的，也是經過排序的。
    INDEX FAST FULL SCAN：
HINT寫法：INDEX_FFS(表名 索引名)
原理：從段頭開始，讀取包含位圖塊，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK，讀取的順序完全有物理存儲位置決定，并采取多塊讀，每次讀取DB_FILE_MULTIBLOCK_READ_COUNT個塊。查詢某個表記錄總數的時候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。

Fast Full Index Scans :
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

http://download-west.oracle.com/doc…imops.htm#51111

Full Table Scans : 
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement’s WHERE clause.

When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

http://download-west.oracle.com/doc…imops.htm#44852

出處：

http://blog.51cto.com/tiany/1582044