【MOS】為何在查詢中索引未被使用 (文件 ID 1549181.1、英文文件為67522.1)

文件內容

用途

排錯步驟

快速檢查

表上是否存在索引？

索引是否應該被使用？

索引本身的問題

索引列或者索引的前置列是否在單表（non-join）查詢的 Where 條件中（predicate list）？

索引列是否用在連線謂詞中（join predicates）？

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

索引列是否被函式修改？

隱式型別轉換（implicit type conversion）是什麼？

是否在語義（semantically）上無法使用索引？

錯誤型別的索引掃描？

是否索引列為可空？

NLS_SORT是否設定為二進位制（BINARY）？

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

最佳化器和成本計算相關問題

是否存在準確且合適的統計資訊（Statistics）？

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

索引的選擇度不高？

在總體成本中，表掃描的成本佔大部分

訪問空索引並不意味著比訪問有值的索引高效。

引數設定

其它問題

是否使用了檢視/子查詢？

是否存在遠端表(remote table)？

是否使用並行執行（PX）?

是否是包含了子查詢的Update語句？

查詢是否使用了繫結變數？

查詢是否引用了帶有延遲約束的列?

索引提示（hint）不工作

有用的 hints:

參考

---

適用於:

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' 子句時才能保證結果是排序的。請參照: 
    
    Document 344135.1 Ordering of Result Data 
    Document 70135.1 Index Fast Full Scan Usage To Avoid Full Table Scans
  • CBO 能使用 Index Skip Scan (INDEX_SS). 執行 INDEX_SS 不需要索引前置列。請參照:
    
    Document 212391.1 Index Skip Scan Feature
  • CBO 能夠選用一個索引來避免排序，但是索引列必須存於在 order by 子句中才可以。 
    請參照
    
    Document 67409.1 When will an ORDER BY use an Index to Avoid Sorting?
    Document 10577.1 Driving ORDER BY using an Index

• 索引列是否用在連線謂詞中（join predicates）？

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

   emp.deptno = 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
  ....

  
  這種情況下查詢可能已經被轉化為不能使用索引的語句。請參照: 
  
  Document 62153.1 Optimization of large inlists/multiple OR`s

• 索引列是否被函式修改？

  索引不能用於被函式修改的列。函式索引（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
  
  
  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
  

• 隱式型別轉換（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 提示來強制使用需要的掃描型別。  請參照:
  
  Document 62339.1 Init.ora Parameter "FAST_FULL_SCAN_ENABLED" Reference Note
  
  我們還可以定義索引的排序順序為遞增或遞減。Oracle 對待降序索引就好像它是基於函式的索引，因此與預設使用的升序的執行計劃不同。透過檢視執行計劃，您看不到使用升序或降序，需要額外檢查檢視 DBA_IND_COLUMNS 的'DESCEND'列。

• 是否索引列為可空？

  索引不儲存 NULL 值，除非該索引為聯合索引（即多列索引），或者它是一個點陣圖索引。 
  只有至少有一個索引列有值，聯合索引才儲存空值。聯合索引中尾部的空值也會被存放在索引中。如果所有列的值都為空，這行將不會儲存在索引中。由於索引中缺乏 NULL 值，那麼一些結果中可能會返回 NULL 值（如count）的操作可能會被禁用索引。這是因為最佳化器不能保證在單獨使用索引時可以獲得準確的資訊。關於使用 NOT IN 和 NULL 的其他一些考慮,請參考
  
  Document 28934.1 Use of indexes with NOT IN subquery
  
  點陣圖索引允許儲存空值。因此最佳化器會使用這些索引,無論它們的結果可信與否。索引上的空值有時很有用，特別對於某些型別的 SQL 語句，如與聚合函式 COUNT 查詢。示例: 
  

  SELECT count(*) FROM emp;

  
  點陣圖索引的更多資訊請參考 
  
  Document 70067.1 All about Bitmap Indexes 

• NLS_SORT是否設定為二進位制（BINARY）？

  如果 NLS_SORT 未設定為二進位制，索引將不會被使用。這是因為索引是基於 Key 值的二進位制順序來建立的（pre-sorted使用二進位制值）。無論最佳化器設定為何種方法，NLS_SORT 不是二進位制時，將使用全表掃描，。更多關於NLS_SORT和索引的使用，請參考:
  
  Document 30779.1 Init.ora Parameter "NLS_SORT" Reference 
  Document 227335.1 Linguistic Sorting - Frequently Asked Questions (section 4.)

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

  從 Oracle Database 11g Release 1開始，您可以建立不可見索引或將一個已經存在的索引標記為不可見。Optimizer 不會考慮不可見索引，除非在 session 或 system 級將引數 OPTIMIZER_USE_INVISIBLE_INDEXES 設定為 TRUE。DML 操作還是會維護這些不可見索引的。詳見：
  
  
  Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
  Database Administrator's Guide
  Understand When to Use Unusable or Invisible Indexes
  

最佳化器和成本計算相關問題

• 是否存在準確且合適的統計資訊（Statistics）？

  CBO 依賴於準確的、最新的和完整的統計資訊來確定一個特定查詢的最佳執行計劃。如果使用 CBO，請確保統計資訊已經收集。如果沒有統計資訊， CBO 將使用預定義的統計資訊，這樣是很可能不會產生良好的計劃或讓應用程式使用索引。請參照:
  
  Document 754931.1 Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above

• 請注意，CBO 會根據開銷(COST)來決定使用不同的索引。除了基本的表和索引的資訊之外，如果說在某些列上資料分佈是不均勻的，那麼還需要收集這些列的資料的分佈。關於收集統計資訊的建議，請參見以下文件：
  
  Document 1369591.1 Optimizer Statistics - Central Point
  Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
   
  在一般情況下，物件的資料或結構的改變會使以前的統計資訊不準確，因此應該重新收集新的統計資訊。例如，對錶裝載了大量的資料後，需要收集新的統計資訊。安裝新補丁集（Patchset）後,也建議重新收集統計資訊。表訪問最佳效果是統計資訊是在相同版本的資料庫中生成的。 下邊的文件討論了為什麼資料庫升級後查詢的效能會下降：
  
  Document 160089.1 TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

• 一個索引是否與其它的索引有相同的等級或者成本（cost）？

  對於相同開銷（COST）的索引，CBO 會使用多種辦法將不同的索引區分開，如將索引名稱按字母順序排序，完全匹配的索引掃描會選擇更大的NDK(不同鍵值的個數)的索引（不適用於快速全掃描）或選擇葉塊數量較少的索引。請注意一般很少發生這種情況。 請檢視
  
  Document 73167.1 Handling of equally ranked (RBO) or costed (CBO) indexes

• 索引的選擇度不高？

  • 索引的選擇度不高
    使用它可能不是一個好的選擇...
    列資料不是平均分佈的。
  • CBO 假定列資料不會傾斜，並均勻分佈。如果不是這樣，那麼統計資訊可能沒有反映真實情況，那麼即使某些值的選擇度高，索引也會因為整個列的選擇度不高而不適用索引。 如果是這種情況，那麼應考慮採用直方圖記錄更準確的列的資料分佈或者採用提示（hint）。
  • 統計資訊不準確導致索引看起來選擇性不高而不被選擇。可能的規避方法:
  • 收集更精確的統計值。 請檢視 
    
    Document 1369591.1 Optimizer Statistics - Central Point
    Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines
      
  • 對於資料分佈不均勻的列考慮收集列的統計資訊
  • 使用 hint 或 outline。請參考
    
    Document 29236.1 QREF: SQL Statement HINTS 
    Document 50607.1 How to specify an INDEX Hint
     

• 在總體成本中，表掃描的成本佔大部分

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

  SELECT empno FROM emp WHERE empno=5

  這條語句可能會使用基於列 empno的索引，因為所有需要的資料都存放在索引中所以不需要再對錶做而外的訪問。反之：
  

   SELECT ename FROM emp WHERE empno=5

  這條語句會需要對錶做而外的訪問，因為 ename 欄位沒有存放在索引中。檢索 ename 的開銷會隨著查詢返回記錄條數的增加而變得昂貴。
  
  Optimizer 使用"Clustering Factor"來判斷如果使用 index 的話需要而外對錶做多少次訪問，詳見：
  
  Document 39836.1 Clustering Factor
   

• 訪問空索引並不意味著比訪問有值的索引高效。

  Reorganization, Truncation 或刪除操作不一定會影響 SQL 語句執行的成本。需要注意的是刪除操作並不會從物件中真正釋放空間。也就是說，刪除操作不會重置物件的高水位線。Truncate 操作會重置高水位線。空塊的存在會使索引/表掃描的成本比實際應該的成本高。刪掉並重建會重組物件的結構從而有可能會有幫助（也有可能變壞）。這類問題通常在比較兩個有相同資料的不同系統查詢效能時更容易看到。

• 引數設定

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

其它問題

• 是否使用了檢視/子查詢？

  查詢涉及到檢視或者子查詢時可能會被改寫，導致不使用索引（儘管該改寫的目標之一是擴充套件更多的訪問路徑）。這些改寫(rewrite)一般來說都是合併(merging)操作。請檢視 
  
  Document 199070.1 Optimizing statements that contain views or subqueries

• 是否存在遠端表(remote table)？

  通常遠端表不會使用索引。索引在分散式查詢中的使用依賴於被髮送到遠端的查詢。CBO 將評估遠端訪問的成本，並評估比較傳送或者不傳送索引的謂詞到遠端站點的成本。因此，CBO 可以做出有關遠端表上使用索引的更加明智的決定。一個非常有效的方法就是,在遠端建立包含相關謂詞的檢視並強制使用索引，之後在本地查詢中使用這個檢視。 請參考
  
  Document 68809.1 Distributed Queries

• 是否使用並行執行（PX）?

  在並行執行時索引的採用比在序列執行((serial execution))時更加嚴格。一個快速檢測的方法就是禁用並行，然後檢視該索引是否被使用。

• 是否是包含了子查詢的Update語句？

  在一些情況下，基於成本的考慮，索引沒有被選使用是因為它依賴於一個子查詢返回的值。這種情況下，可以使用提示（hint）來強制使用索引。請參考 
  
  Document 68084.1 Using hints to optimize an Update with a subquery that is not using an index on the updated table. 

• 查詢是否使用了繫結變數？

  CBO 對 like 或範圍謂詞的繫結變數不能產生準確的成本(cost)。這可能會導致索引不被選擇。 請參考
  
  Document 68992.1 Predicate Selectivity

• 查詢是否引用了帶有延遲約束的列?

  如果一個表中的某一列上含有延遲約束（比如 NOT NULL）並且這一列上有索引，那麼不管這個約束當前是延遲狀態或是被顯式地設定為立即使用，我們都不會考慮使用這一列上的索引。例如：
  

  CREATE TABLE tdc
  ( x INT CONSTRAINT x_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED RELY,
    y INT CONSTRAINT y_not_null NOT NULL,
    z VARCHAR2(30)
  );
  CREATE INDEX t_idx ON tdc(x);

  SET CONSTRAINTS ALL IMMEDIATE; <-- 將所有延遲約束置為立即使用
  
  SET AUTOTRACE TRACEONLY EXPLAIN

  SELECT COUNT(1) FROM tdc;        <-- 索引不會被使用
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 2532426293
  
  -------------------------------------------------------------------
  | Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
  -------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
  |   1 |  SORT AGGREGATE    |      |     1 |            |          |
  |   2 |   TABLE ACCESS FULL| TDC  |     1 |     2   (0)| 00:00:01 |
  -------------------------------------------------------------------

  這個現象在以下 bug 中記錄，關閉為"not a bug":

    optimizer ignoring deferrable constraints even when not deffered and not in tx

• 索引提示（hint）不工作

  請使用表的別名。 請參考 
  
  Document 69992.1 Why is my hint ignored?
  Document 50607.1 How to specify an INDEX Hint
    有用的 hints:

  FIRST_ROWS	相當於提示使用索引
  ORDERED	強制查詢的關聯順序（join order of a query）。Oracle 推薦使用 LEADING hint 因為它更好用。
  LEADING	這個 hint 告訴 optimizer 先使用指定的表做連線。它比 ORDERED 更好用。
  INDEX	強制使用索引掃描， 並禁用快速模式（INDEX_FFS）
  INDEX_FFS	強制使用快速索引掃描INDEX_FFS
  INDEX_ASC	強制使用升序的索引範圍掃描（Ascending Index Range Scan）
  INDEX_DESC	強制使用降序的索引範圍掃描（Descending Index Range Scan）

  
  參見：
  
  Document 29236.1 QREF: SQL Statement HINTS

參考

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

About Me

............................................................................................................................... ● 本文來自MOS（1549181.1、67522.1） ● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● QQ群：230161599     微信群：私聊 ● 聯絡我請加QQ好友(646634621)，註明新增緣由 ● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................... 拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。