為何在查詢中索引未被使用 (文件 ID 1549181.1)
文件內容
適用於:
Oracle Database - Standard Edition - 版本 8.1.7.4 和更高版本
Oracle Database - Personal Edition - 版本 8.1.7.4 和更高版本
Oracle Database - Enterprise Edition - 版本 8.1.7.4 和更高版本
本文件所含資訊適用於所有平臺
用途
這篇文章用來解答下面的問題:為什麼我的索引沒有被使用?
排錯步驟
“為什麼索引沒有被使用”是一個涉及面較廣的問題。有很多種原因會導致索引沒有被使用。下面是一些非常有用的檢查列表。請點選下面連結來檢視文章的具體內容:
快速檢查
-
表上是否存在索引?
檢查您認為應該通過索引訪問的表上是否真的有定義索引。那些索引可能已經被刪掉或者在建立的時候就失敗了 – 比如一種可能的場景是,在對錶做匯入或 load 操作後,由於軟體或人為錯誤造成索引沒有被建立。下面的語句可以用來檢查索引是否存在。
SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;
-
索引是否應該被使用?
Oracle
不會僅僅因為有索引存在就一定要使用索引。如果一個查詢需要檢索出這個表裡所有的記錄(比如說表之間做連線操作),那為什麼還要既訪問索引的所有資料又訪問表的所有資料呢?在這種情況下只訪問表的資料會更快。對所有的查詢
Oracle Optimizer 會基於統計資訊來計算各種訪問路徑,包括索引,從而選出最優的一個。
索引本身的問題
-
索引列或者索引的前置列是否在單表(non-join)查詢的 Where 條件中(predicate list)?
如果不是,至少需要索引前置列在查詢謂詞列表中,查詢才能使用索引。(例外:請見下面的 Skip Scan)。
示例:
在列
EMP.EMPNO 上定義了單列索引 EMPNO_I1,同時在列 EMP.EMPNO 和 EMP.DEPT 上定義了聯合索引
EMPNO_DEPT_I2(EMP.EMPNO為索引前置列)。那麼必須在查詢謂詞列表中(where從句)使用列
EMP.EMPNO,優化器才能使用這兩個索引中的某一個。
SELECT ename, sal, deptno FROM emp WHERE empno<100;
例外:
-
只要索引中包含查詢所需的所有列, 而且至少有一個索引列中含有非空約束,CBO 就能夠使用索引快速全掃描(INDEX_FFS)。執行
INDEX_FFS 不需要索引前置列。需要注意的是 INDEX_FFS
不能保證返回的行是排序的。結果的順序是與讀取索引塊的順序一致的,只有當使用了 'order by' 子句時才能保證結果是排序的。請參照:
-
CBO 能使用 Index Skip Scan (INDEX_SS). 執行 INDEX_SS 不需要索引前置列。請參照:
-
CBO 能夠選用一個索引來避免排序,但是索引列必須存於在 order by 子句中才可以。
請參照
-
索引列是否用在連線謂詞中(join predicates)?
例如,下面這個連線謂詞定義瞭如何在表 emp 和 dept 的 deptno 列上做連線:
如果索引列是連線謂詞的一部分,那麼查詢在執行時使用了哪種型別的連線?
-
雜湊/排序合併連線(Hash / Sort Merge Join):
對於雜湊連線和排序合併,在連線執行的時候,外部表的資訊還沒有獲得,因此無法進行對內部表的行檢索。它的處理方式是將外部表和內部表分別查詢後將結果合併。雜湊連線和排序合併的內部表不能通過連線的索引列單獨被訪問。這是連線型別的執行機制的限制。巢狀迴圈連線有所不同,它們允許通過索引查詢內部表的連線列。
-
巢狀迴圈連線(Nested Loops Join):巢狀迴圈連線讀取外部表,然後利用所收集的資訊訪問內部表。該演算法允許對內部表基於索引進行查詢。
只有巢狀迴圈連線(Nested loops join)允許索引在內部表中僅基於連線列進行查詢。
另外,連線的順序(join order)是否允許使用索引?
一個巢狀迴圈連線的外部表必須已經訪問過,才可以在內部表中使用索引。檢視 explain plan,以確定哪些訪問路徑已經使用。由於這個限制,表的連線順序是很重要的。
例如:如果我們通過"emp.deptno
= dept.deptno"來對 EMP 和 DEPT 做連線,並且在 EMP.DEPTNO 有一個索引,並假設查詢中沒有與
EMP.DEPTNO 相關的其他謂詞,EMP 是在 DEPT 前被訪問,然後沒有值可用於在 EMP.DEPTNO
索引中查詢。在這種連線順序下,要想使用這個索引我們只能使用全索引掃描或索引快速全掃描。在這種情況下,全表掃描(FTS)的成本可能更小。
-
索引列在 IN 或者多個 OR 語句中?
比如:
emp.deptno IN (10,23,34,....)
或
emp.deptno = 10
OR emp.deptno = 23
OR emp.deptno = 34
....
這種情況下查詢可能已經被轉化為不能使用索引的語句。請參照:
-
索引列是否被函式修改?
索引不能用於被函式修改的列。函式索引(function based indexes)可以用來解決這個問題。
Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
Database Concepts
Chapter 3 Indexes and Index-Organized Tables
Overview of Function-Based Indexes
http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CBBGIIFB
Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
Database Performance Tuning Guide
Chapter 2 Designing and Developing for Performance
Section 2.5.3 Table and Index Design
http://docs.oracle.com/database/121/TGDBA/pfgrf_design.htm#CJHCJIDB
-
隱式型別轉換(implicit type conversion)是什麼?
如果進行比較的兩個值的資料型別不同,則 Oracle
必須將其中一個值進行型別轉換使其能夠比較。這就是所謂的隱式型別轉換。通常當開發人員將數字儲存在字元列時會導致這種問題的產生。Oracle
在執行時會強制轉化其中一個值,(由於固定的規則)在索引字元列使用
to_number。由於新增函式到索引列所以導致索引不被使用。實際上,Oracle
也只能這麼做,型別轉換是一個應用程式設計因素。由於轉換是在每行都進行的,這會導致效能問題。詳見:
Document 232243.1 ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates
-
是否在語義(semantically)上無法使用索引?
出於對查詢整體成本的考慮,一個成本較低的執行計劃中可能是無法使用索引的。某索引可能已經被考慮在某種連線排序及方法中,但是成本最低的那個執行計劃中卻無法從“語義”角度使用該索引。
-
錯誤型別的索引掃描?
例如:快速全索引掃描而不是索引範圍掃描
這可能是優化器選擇了所需的索引,但卻使用了客戶不希望的掃描方法。在這種情況下,利用 INDEX_FFS,INDEX_ASC 和 INDEX_DESC 提示來強制使用需要的掃描型別。 請參照:
我們還可以定義索引的排序順序為遞增或遞減。Oracle 對待降序索引就好像它是基於函式的索引,因此與預設使用的升序的執行計劃不同。通過檢視執行計劃,您看不到使用升序或降序,需要額外檢查檢視 DBA_IND_COLUMNS 的'DESCEND'列。
-
是否索引列為可空?
索引不儲存 NULL 值,除非該索引為聯合索引(即多列索引),或者它是一個點陣圖索引。
只有至少有一個索引列有值,聯合索引才儲存空值。聯合索引中尾部的空值也會被存放在索引中。如果所有列的值都為空,這行將不會儲存在索引中。由於索引中缺乏
NULL 值,那麼一些結果中可能會返回 NULL
值(如count)的操作可能會被禁用索引。這是因為優化器不能保證在單獨使用索引時可以獲得準確的資訊。關於使用 NOT IN 和 NULL
的其他一些考慮,請參考
點陣圖索引允許儲存空值。因此優化器會使用這些索引,無論它們的結果可信與否。索引上的空值有時很有用,特別對於某些型別的 SQL 語句,如與聚合函式 COUNT 查詢。示例:
SELECT count(*) FROM emp;
點陣圖索引的更多資訊請參考
-
NLS_SORT是否設定為二進位制(BINARY)?
如果 NLS_SORT 未設定為二進位制,索引將不會被使用。這是因為索引是基於 Key
值的二進位制順序來建立的(pre-sorted使用二進位制值)。無論優化器設定為何種方法,NLS_SORT
不是二進位制時,將使用全表掃描,。更多關於NLS_SORT和索引的使用,請參考:
-
是否使用的是不可見索引(invisible indexes)?
從 Oracle Database 11g Release 1開始,您可以建立不可見索引或將一個已經存在的索引標記為不可見。Optimizer
不會考慮不可見索引,除非在 session 或 system 級將引數 OPTIMIZER_USE_INVISIBLE_INDEXES 設定為
TRUE。DML 操作還是會維護這些不可見索引的。詳見:
優化器和成本計算相關問題
-
是否存在準確且合適的統計資訊(Statistics)?
CBO 依賴於準確的、最新的和完整的統計資訊來確定一個特定查詢的最佳執行計劃。如果使用 CBO,請確保統計資訊已經收集。如果沒有統計資訊, CBO 將使用預定義的統計資訊,這樣是很可能不會產生良好的計劃或讓應用程式使用索引。請參照:
Document 754931.1 Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
-
請注意,CBO 會根據開銷(COST)來決定使用不同的索引。除了基本的表和索引的資訊之外,如果說在某些列上資料分佈是不均勻的,那麼還需要收集這些列的資料的分佈。關於收集統計資訊的建議,請參見以下文件:
在一般情況下,物件的資料或結構的改變會使以前的統計資訊不準確,因此應該重新收集新的統計資訊。例如,對錶裝載了大量的資料後,需要收集新的統計資訊。安裝新補丁集(Patchset)後,也建議重新收集統計資訊。表訪問最佳效果是統計資訊是在相同版本的資料庫中生成的。
下邊的文件討論了為什麼資料庫升級後查詢的效能會下降:
-
一個索引是否與其它的索引有相同的等級或者成本(cost)?
對於相同開銷(COST)的索引,CBO 會使用多種辦法將不同的索引區分開,如將索引名稱按字母順序排序,完全匹配的索引掃描會選擇更大的NDK(不同鍵值的個數)的索引(不適用於快速全掃描)或選擇葉塊數量較少的索引。請注意一般很少發生這種情況。 請檢視
-
索引的選擇度不高?
-
索引的選擇度不高
使用它可能不是一個好的選擇...
列資料不是平均分佈的。
-
CBO 假定列資料不會傾斜,並均勻分佈。如果不是這樣,那麼統計資訊可能沒有反映真實情況,那麼即使某些值的選擇度高,索引也會因為整個列的選擇度不高而不適用索引。 如果是這種情況,那麼應考慮採用直方圖記錄更準確的列的資料分佈或者採用提示(hint)。
-
統計資訊不準確導致索引看起來選擇性不高而不被選擇。可能的規避方法:
-
收集更精確的統計值。 請檢視
-
對於資料分佈不均勻的列考慮收集列的統計資訊
-
使用 hint 或 outline。請參考
-
在總體成本中,表掃描的成本佔大部分
通常來說,當使用索引的時候,我們需要再次檢索表本身來找到索引中不存在的欄位的值,這個操作比檢索索引本身的開銷要大很多。由於 optimizer
是基於總體的成本來計算執行計劃,如果通過索引檢索表的成本很大,並且超過了某個閥值,optimizer 就會考慮其他的訪問路徑。
比如
SELECT empno FROM emp WHERE empno=5
這條語句可能會使用基於列 empno的索引,因為所有需要的資料都存放在索引中所以不需要再對錶做而外的訪問。反之:
SELECT ename FROM emp WHERE empno=5
這條語句會需要對錶做而外的訪問,因為 ename 欄位沒有存放在索引中。檢索 ename 的開銷會隨著查詢返回記錄條數的增加而變得昂貴。
Optimizer 使用"Clustering Factor"來判斷如果使用 index 的話需要而外對錶做多少次訪問,詳見:
-
訪問空索引並不意味著比訪問有值的索引高效。
Reorganization, Truncation 或刪除操作不一定會影響 SQL
語句執行的成本。需要注意的是刪除操作並不會從物件中真正釋放空間。也就是說,刪除操作不會重置物件的高水位線。Truncate
操作會重置高水位線。空塊的存在會使索引/表掃描的成本比實際應該的成本高。刪掉並重建會重組物件的結構從而有可能會有幫助(也有可能變壞)。這類問題通常在比較兩個有相同資料的不同系統查詢效能時更容易看到。
-
引數設定
某些引數的設定可能會影響索引的使用。比如在大多數情況下都建議使用 DB_FILE_MULTIBLOCK_READ_COUNT 和
OPTIMIZER_INDEX_COST_ADJ
的預設值。除非某些特定的操作有特定的建議,使用其它值會使索引的成本不現實的減少或變大從而極大的降低查詢的效能。
其它問題
參考
NOTE:30779.1 - Init.ora Parameter "NLS_SORT" Reference Note
NOTE:67409.1 - When will an ORDER BY use an Index to Avoid Sorting?
NOTE:227335.1 - Linguistic Sorting - Frequently Asked Questions
NOTE:69992.1 - Why is my Hint Ignored?
NOTE:754931.1 - Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above
NOTE:10577.1 - Driving ORDER BY using an Index (Oracle7)
NOTE:62153.1 - Optimization of Large Inlists / Multiple OR Conditions
NOTE:28426.1 - Partition Views and the use of Indexes (7.1 & 7.2)
NOTE:1369591.1 - Master Note: Optimizer Statistics
NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines
NOTE:160089.1 - Troubleshooting a Server Upgrade Resulting in Slow Query Performance
NOTE:70135.1 - Index Fast Full Scan Usage To Avoid Full Table Scans
NOTE:43194.1 - Partition Views in 7.3: Examples and Tests
NOTE:50607.1 - How to specify an INDEX Hint
NOTE:212391.1 - Index Skip Scan Feature
NOTE:70067.1 - All about Bitmap Indexes
NOTE:28934.1 - Use of Indexes with NOT IN Subquery
NOTE:73167.1 - Handling of equally ranked (RBO) or costed (CBO) indexes
NOTE:199070.1 - Optimizing statements that contain views or subqueries
NOTE:68809.1 - Distributed Queries
NOTE:68084.1 - Using hints to optimize an Update with subquery not using index on updated table
NOTE:68992.1 - Predicate Selectivity
NOTE:344135.1 - Ordering of Result Data
NOTE:29236.1 - QREF: SQL Statement HINTS
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31393455/viewspace-2129678/,如需轉載,請註明出處,否則將追究法律責任。