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

說明

相關文章連結：

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

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

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

前言

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

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

1. 表上是否真的存在索引？

檢查被訪問的表上是否真的有定義索引。因為那些索引可能已經被刪掉或者在建立的時候就失敗了。 比如，在對錶做匯入或load操作後，由於軟體或人為錯誤造成索引沒有被建立。下面的語句可以用來檢查索引是否存在。

SELECT index_name FROM user_indexes WHERE table_name = XXX;

2.你建立的索引是否真的應該被使用？

Oracle不會僅僅因為有索引存在就一定要使用索引。如果一個查詢需要檢索出這個表裡所有的記錄（比如說表之間進行關聯），那為什麼還要既訪問索引的所有資料又訪問表的所有資料呢？在這種情況下只訪問表的資料會更快（全表掃描）。對所有的查詢Oracle最佳化器會基於統計資訊來計算各種訪問路徑，包括索引，從而選出最優的一個（統計資訊準的話，最佳化器的方案一般都是最優的， 人為hint操作需慎重）。

3.索引列或者索引的前導列是否在單表查詢的 Where條件中？

如果不是，至少需要索引前導列在查詢謂詞列表中，查詢才能使用索引（除索引跳躍掃描Skip Scan這種方式以外）。

如:

在列EMP.EMPNO 上定義了單列索引 IDX_EMPNO，同時在列EMP.EMPNO和EMP.DEPT上定義了聯合索引IDX_EMPNO_DEPT（EMP.EMPNO為索引前導列）。那麼必須在查詢謂詞列表中（where從句）使用列EMP.EMPNO，最佳化器才能使用這兩個索引中的某一個。

SELECT ename, sal, deptno FROM emp WHERE empno < 100;

另外:

（1）只要索引中包含查詢所需的所有列, 而且至少有一個索引列中含有非空約束，CBO就能夠使用索引快速全掃描 (INDEX_FFS)。執行INDEX_FFS不需要索引前置列。需要注意的是 INDEX_FFS不能保證返回的行是已排序的。結果的順序是與讀取索引塊的順序一致的，只有當使用了 'order by' 子句時才能保證結果是排序的。

（2）CBO能使用 Index Skip Scan (INDEX_SS)，執行 INDEX_SS不需要索引前置列，但是這種掃描方式效率很低。

（3）CBO能夠選用一個索引來避免排序，但是索引列必須存於在 order by 子句中才可以（利用索引自動排序的特性，可避免執行計劃中SORT ORDER BY的出現，提升SQL效能）。

4.索引列是否用在連線謂詞中 ？

舉個例子，下面這個連線謂詞定義瞭如何在表 emp 和 dept 的 deptno 列上做連線：

emp.deptno = dept.deptno

如果索引列是連線謂詞的一部分，那麼查詢在執行時使用了哪種型別的連線？

（1）雜湊/排序合併連線（Hash / Sort Merge Join，簡稱SMJ）: 對於雜湊連線和排序合併連線，在連線執行的時候，外部表的資訊還沒有獲得，因此無法進行對內部表的行檢索。 它的處理方式是將外部表和內部表分別查詢後將結果合併。雜湊連線和排序合併連線的內部表不能透過連線的索引列單獨被訪問。這是連線型別的執行機制的限制。巢狀迴圈連線有所不同，它們允許透過索引查詢內部表的連線列。

（2）巢狀迴圈連線（Nested Loops Join，簡稱NL）:巢狀迴圈連線讀取外部表，然後利用所收集的資訊訪問內部表。該演算法允許對內部表基於索引進行查詢。

只有巢狀迴圈連線允許索引在內部表中僅基於連線列進行查詢。

另外，連線的順序（join order）是否允許使用索引?

一個巢狀迴圈連線的外部表必須已經訪問過，才可以在內部表中使用索引 。檢視explain plan，以確定哪些訪問路徑已經使用。由於這個限制，表的連線順序是很重要的。

例如：如果我們透過"emp.deptno = dept.deptno"來對EMP和DEPT做連線，並且在 EMP.DEPTNO有一個索引，並假設查詢中沒有與 EMP.DEPTNO相關的其他謂詞，EMP是在 DEPT前被訪問，然後沒有值可用於在EMP.DEPTNO索引中查詢。在這種連線順序下，要想使用這個索引我們只能使用全索引掃描或索引快速全掃描。在這種情況下，全表掃描（FTS）的成本可能更小。

5.索引列在 IN 或者多個 OR 語句中嗎？

比如：

emp.deptno IN (10,23,34,....)

或

emp.deptno = 10

OR emp.deptno = 23

OR emp.deptno = 34

....

這種情況下查詢可能已經被轉化為不能使用索引的語句，所以儘可能少的使用in和or。

6.索引列是否被函式修改？

索引不能用於被函式修改的列。建立函式索引可以用來解決這個問題。

7.是否出現隱式型別轉換 ？

如果進行比較的兩個值的資料型別不同，則 Oracle 必須將其中一個值進行型別轉換使其能夠比較。這就是所謂的隱式型別轉換。

通常當開發人員將數字儲存在字元列時會導致這種問題的產生。Oracle 在執行時會強制轉化其中一個值，（由於固定的規則）在索引字元列使用to_number。 由於新增函式到索引列所以導致索引不被使用。實際上，Oracle 也只能這麼做，型別轉換是一個應用程式設計因素。由於轉換是在每行都進行的，這會導致效能問題，因此儘量寫全轉換函式來避免隱式轉換。

8.是否在語義上無法使用索引？

出於對查詢整體成本的考慮，一個成本較低的執行計劃中可能是無法使用索引的。某索引可能已經被考慮在某種連線排序及方法中，但是成本最低的那個執行計劃中卻無法從“語義”角度使用該索引。

9.使用了錯誤型別的索引掃描？

例如：快速全索引掃描而不是索引範圍掃描

這可能是最佳化器選擇了所需的索引，但卻使用了使用者不希望看到的掃描方法。在這種情況下，利用 INDEX_FFS，INDEX_ASC 和 INDEX_DESC 提示來強制使用需要的掃描型別。

我們還可以定義索引的排序順序為遞增或遞減。Oracle對待降序索引就好像它是基於函式的索引，因此與預設使用的升序的執行計劃不同。透過檢視執行計劃，您看不到使用升序或降序，需要額外檢查檢視DBA_IND_COLUMNS的'DESCEND'列。

10.是否索引列為可空？

索引不儲存 NULL 值，除非該索引為聯合索引（即多列索引），或者它是一個點陣圖索引。

只有至少有一個索引列有值，組合索引才儲存空值 。 如，某個含有空值的列想讓它單獨走索引，可以這麼建立組合索引來解決：idx_name( 列名, 任意阿拉伯數字)。

組合索引中尾部的空值也會被存放在索引中。如果所有列的值都為空，這行將不會儲存在索引中。由於索引中缺乏 NULL 值，那麼一些結果中可能會返回 NULL 值（如count）的操作可能會被禁用索引。這是因為最佳化器不能保證在單獨使用索引時可以獲得準確的資訊。

點陣圖索引允許儲存空值 。因此最佳化器會使用這些索引，無論它們的結果可信與否。索引上的空值有時很有用，特別對於某些型別的 SQL 語句，如與聚合函式 COUNT 查詢。如:

SELECT count(*) FROM emp;

11.NLS_SORT 是否設定為二進位制 ？

如果 NLS_SORT 未設定為二進位制，索引將不會被使用。這是因為索引是基於 Key 值的二進位制順序來建立的（presorted使用二進位制值）。無論最佳化器設定為何種方法，NLS_SORT 不是二進位制時，將使用全表掃描。

12.是否使用的是不可見索引（ invisible indexes ）？

從 Oracle 11gR1開始，可以建立不可見索引或將一個已經存在的索引標記為不可見（就可以簡單認為該索引已經被“刪除”了，但是還佔用著空間）。 這種方式可以在白天遮蔽掉不想要的索引，重建新索引即可，而不是去直接刪除再去建立索引（因為這樣導致效能降低，影響業務），等晚上系統空閒了再刪除不可見索引即可。

最佳化器不會考慮不可見索引，除非在 session或 system級將引數 OPTIMIZER_USE_INVISIBLE_INDEXES設定為TRUE。但是DML操作還是會維護這些不可見索引的。