【TUNE_ORACLE】你建立的索引為什麼不工作了？（二）

說明

相關文章連結：

你建立的索引為什麼不工作了？（一）： http://blog.itpub.net/69992972/viewspace-2766087/

你建立的索引為什麼不工作了？（二）： http://blog.itpub.net/69992972/viewspace-2766688/

你建立的索引為什麼不工作了？（三）： http://blog.itpub.net/69992972/viewspace-2766797/

前言

你是否有時會困惑：你剛建立的索引怎麼不工作了？如果你正因此感到困惑的同時看到這篇文章，那麼恭喜你，你的困惑即將被解決！我將常見的問題一一列出，並提供瞭解決辦法，下面和我一起來看看你的困惑是哪種吧！

索引未被使用的可能原因（二）

1.統計資訊是否準確 ？

任何SQL語句在執行前都需要檢查統計資訊是否是準確且最新 ，因為從10g開始，最佳化器預設是基於成本的最佳化器（CBO），而CBO又依賴於準確的、最新的和完整的統計資訊來確定一個特定查詢的最佳執行計劃。所以一旦使用CBO，必須確保統計資訊已經收集。如果沒有統計資訊， CBO 將使用預定義的統計資訊或者level 2級別的動態取樣，這樣是很可能導致統計資訊不準，進而可能導致不走索引。

注：

（1）CBO 會根據開銷(COST)來決定使用不同的索引。除了基表和索引的資訊之外，如果說在某些列上資料分佈是不均勻且傾斜的，那麼還需要收集這些列的資料的分佈，比如直方圖。

（2）level 2動態取樣規則（Oracle預設取樣級別，一共有11級）：對沒有收集過統計資訊的表啟用動態取樣，並且取樣的塊數是64個。如果表的塊數小於64個，則會收集這張表的所有塊。

在一般情況下，物件的資料或結構的改變會使以前的統計資訊不準確，因此應該重新收集新的統計資訊（ 根據我的經驗，資料變化量高於10%的時候，統計資訊就會過期）。安裝新補丁集後，也建議重新收集統計資訊。 表訪問最佳效果是統計資訊是在相同版本的資料庫中生成的，不應該跨版本。

2.一個索引是否與其它的索引有相同的等級或者成本 ？

對於相同成本（COST）的索引，CBO會使用多種辦法將不同的索引區分開，如將索引名稱按字母順序排序，完全匹配的索引掃描會選擇更大的NDK(不同鍵值的個數)的索引（不適用於索引快速全掃描—FFS）或選擇葉塊數量較少的索引。但是一般很少發生這種情況。  

3.索引的選擇性是否很低？

（1）索引的選擇性低（資料重複率高了）， 使用它可能不是一個好的選擇，因為 列資料不是平均分佈的。

（2）CBO會假定列資料不會傾斜，並均勻分佈。如果不是這樣，那麼統計資訊可能沒有反映真實情況，那麼即使某些值的選擇性高，索引也會因為整個列的選擇性不高而不適用索引，那麼應考慮收集直方圖或者使用HINT。

（3）統計資訊不準確導致索引看起來選擇性不高而不被選擇。需要 收集更精確的統計值。 對於資料分佈不均勻的列考慮收集列的統計資訊 。

4.索引回表掃描的成本是否太高？

當使用索引的時候，最佳化器需要再次去檢索表本身來找到索引中不存在的欄位的值，這個操作就是回表，而且 回表比檢索索引本身的開銷要大很多。由於最佳化器是基於總體的成本來計算執行計劃，如果最佳化器發現回表成本很大，並且超過了某個閥值，就會考慮其他的訪問路徑。

比如：

SELECT empno FROM emp WHERE empno=7876

這條語句可能會使用基於列 empno的索引，因為所有需要的資料都存放在索引中所以不需要再回表訪問。

稍微改變下：

SELECT ename FROM emp WHERE empno=7876

ename欄位沒有存放在索引中，需要回表訪問，並且檢索ename的開銷會隨著查詢返回記錄 條數的增加而變得昂貴。

注：最佳化器使用"Clustering Factor"來判斷如果使用index還需要額外對錶做多少次訪問。

5.訪問空索引並不一定比訪問有值的索引高效

Reorganize, truncate或delete操作不一定會影響SQL語句執行的成本。但是delete操作並不會從物件中真正釋放空間，所以delete操作不會重置物件的高水位線，但truncate操作會重置高水位線。 空塊的存在會使索引/表掃描的成本比實際應該的成本高 。刪掉並重建會重組物件的結構從而有可能會有幫助，但也有可能變壞。這類問題通常出現在比較兩個有相同資料的不同系統查詢效能時。

6.引數預設值被改動

改變某些引數的預設設定可能會影響索引的使用。比如，在大多數情況下都建議使用DB_FILE_MULTIBLOCK_READ_COUNT和 OPTIMIZER_INDEX_COST_ADJ的預設值。除非某些特定的操作有特定的建議，使用其它值會使索引的成本不可預測地 減少或變大從而極大的降低查詢的效能，因此 不建議改動這些預設引數。