解讀Oracle 索引掃描

一、 根據索引的型別與where限制條件的不同，有4種型別的Oracle索引掃描：

（1） 索引唯一掃描(index unique scan)

（2） 索引範圍掃描(index range scan)

（3） 索引全掃描(index full scan)

（4） 索引快速掃描(index fast full scan)

[@more@]

1. 索引唯一掃描(index unique scan)

透過唯一索引查詢一個數值經常返回單個ROWID。如果該唯一索引有多個列組成(即組合索引)，則至少要有組合索引的引導列參與到該查詢中，如建立一個索引：create index IDX_T_YSK_AJ_AJ_YSID on T_YSK_AJ_AJ (YSID) tablespace TBS_YYK_YSKINDEX。則select * from t_ysk_aj_aj t where ysid='tangyun'語句可以使用該索引。如果該語句只返回一行，則存取方法稱為索引唯一掃描。而select * from t_ysk_aj_aj t where csid='tangyun';語句則不會使用該索引，因為where子句種沒有引導列。如果存在UNIQUE 或PRIMARY KEY 約束(它保證了語句只存取單行)的話，Oracle經常實現唯一性掃描。

2. 索引範圍掃描(index range scan)

使用一個索引存取多行資料，同上面一樣，如果索引是組合索引，而且select * from t_ysk_aj_aj t where ysid='tangyun'and csid='tangyun';語句返回多行資料，雖然該語句還是使用該組合索引進行查詢，可此時的存取方法稱為索引範圍掃描。

在唯一索引上使用索引範圍掃描的典型情況下是在謂詞(where限制條件)中使用了範圍運算子(如>、<、<>、>=、<=、between)

在非唯一索引上，謂詞可能返回多行資料，所以在非唯一索引上都使用索引範圍掃描。

使用index rang scan的3種情況：

(1) 在唯一索引列上使用了range運算子(> < <> >= <= between)。
(2) 在組合索引上，只使用部分列進行查詢，導致查詢出多行。
(3) 對非唯一索引列上進行的任何查詢。

3. 索引全掃描(index full scan)

與全表掃描對應，也有相應的全Oracle索引掃描。在某些情況下，可能進行全Oracle索引掃描而不是範圍掃描，需要注意的是全Oracle索引掃描只在CBO模式下才有效。 CBO根據統計數值得知進行全Oracle索引掃描比進行全表掃描更有效時，才進行全Oracle索引掃描，而且此時查詢出的資料都必須從索引中可以直接得到。

還是上面的例子：

SQL> explain plan for select count(ysid) from t_ysk_aj_aj;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1790046257

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 0

| 1 | SORT AGGREGATE | | 1 | 13 | |

| 2 | INDEX FULL SCAN| IDX_T_YSK_AJ_AJ_YSID | 1 | 13 | 0 (0)| 0

--------------------------------------------------------------------------------

9 rows selected

4. 索引快速掃描(index fast full scan)

掃描索引中的所有的資料塊，與 index full scan很類似，但是一個顯著的區別就是它不對查詢出的資料進行排序，即資料不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用並行讀入，以便獲得最大吞吐量與縮短執行時間。

5. 檢視執行計劃的方法

在 sql*plus設定autotrace：

在pl/sql Developer工具

SQL> explain plan for select count(ysid) from t_ysk_aj_aj;

Explained

SQL> select * from table(dbms_xplan.display('PLAN_TABLE'));

二、 解讀執行計劃

1. Cardinality（基數）/ rows

Cardinality的值對於CBO做出正確的執行計劃來說至關重要。 如果CBO獲得的Cardinality值不夠準確（通常是沒有做分析或者分析資料過舊造成），在執行計劃成本計算上就會出現偏差，從而導致CBO錯誤的制定出執行計劃。

Cardinality值表示CBO預期從一個行源（row source）返回的記錄數，這個行源可能是一個表，一個索引，也可能是一個子查詢。 在Oracle 9i中的執行計劃中，Cardinality縮寫成Card。 在10g中，Card值被rows替換。

在多表關聯查詢或者SQL中有子查詢時，每個關聯表或子查詢的Cardinality的值對主查詢的影響都非常大，甚至可以說，CBO就是依賴於各個關聯表或者子查詢Cardinality值計算出最後的執行計劃。

對於多表查詢，CBO使用每個關聯表返回的行數（Cardinality）決定用什麼樣的訪問方式來做表關聯（如Nested loops Join 或 hash Join）。

對於子查詢，它的Cardinality將決定子查詢是使用索引還是使用全表掃描的方式訪問資料。

2. 執行計劃中含義解釋

ID：一個序號，但不是執行的先後順序。執行的先後根據縮排來判斷。

Operation： 當前操作的內容。

Rows： 當前操作的Cardinality，Oracle估計當前操作的返回結果集。

Cost（CPU）：Oracle 計算出來的一個數值（代價），用於說明SQL執行的代價。

Time：Oracle 估計當前操作的時間。

Access：表示這個謂詞條件的值將會影響資料的訪問路勁（表還是索引）。

Filter：表示謂詞條件的值不會影響資料的訪問路勁，只起過濾的作用。

3. 執行計劃中的統計資訊

db block gets ： 從buffer cache中讀取的block的數量

consistent gets： 從buffer cache中讀取的undo資料的block的數量

physical reads： 從磁碟讀取的block的數量

redo size： DML生成的redo的大小

sorts (memory) ：在記憶體執行的排序量

sorts (disk) ：在磁碟上執行的排序量
----------------------------------End-----------------------------------