Oracle不使用索引的幾種情況列舉

本文介紹了幾種不使用索引的情況，本文實驗的資料庫版本均為11.2.0.4

情況1：

我們在使用一個B*樹索引，而且謂詞中沒有使用索引的最前列。

如果這種情況，可以假設有一個表T，在T(x,y)上有一個索引。要做以下查詢：select * from t where y=5。此時，優化器就不打算使用T(x,y)上的索引，因為謂詞中不涉及X列。在這種情況下，倘若使用索引，可能就必須檢視每個索引條目，而優化器通常更傾向于對T表做一個全表掃描。

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t </code><code>as</code> <code>select</code> <code>rownum x,rownum+1 y,rownum+2 z </code><code>from</code> <code>dual </code><code>connect</code> <code>by</code> <code>level</code> <code>< 100000;</code>

<code>Table</code> <code>created.</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(*) </code><code>from</code> <code>t;</code>

<code> </code><code>COUNT</code><code>(*)</code>

<code>zx@ORCL></code><code>create</code> <code>index</code> <code>idx_t </code><code>on</code> <code>t(x,y);</code>

<code>Index</code> <code>created.</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>user</code><code>,</code><code>'T'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>);</code>

<code>PL/SQL </code><code>procedure</code> <code>successfully completed.</code>

<code>zx@ORCL></code><code>set</code> <code>autotrace traceonly explain</code>

<code>--where條件使用y=5</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>t </code><code>where</code> <code>y=5;</code>

<code>Execution Plan</code>

<code>Plan hash value: 1601196873</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 15 | 80 (2)| 00:00:01 |</code>

<code>|* 1 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T | 1 | 15 | 80 (2)| 00:00:01 |</code>

<code>Predicate Information (identified </code><code>by</code> <code>operation id):</code>

<code> </code><code>1 - filter(</code><code>"Y"</code><code>=5)</code>

<code>--where條件使用x=5</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>t </code><code>where</code> <code>x=5;</code>

<code>Plan hash value: 1594971208</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 15 | 3 (0)| 00:00:01 |</code>

<code>| 1 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| T | 1 | 15 | 3 (0)| 00:00:01 |</code>

<code>|* 2 | </code><code>INDEX</code> <code>RANGE SCAN | IDX_T | 1 | | 2 (0)| 00:00:01 |</code>

<code> </code><code>2 - access(</code><code>"X"</code><code>=5)</code>

但這并不完全排除使用索引。如果查詢是select x,y from t where y=5，優化器就會注意到，它不必全面掃描表來得到X或Y(x和y都在索引中)，對索引本身做一個民快速的全面掃描會更合适，因為這個索引一般比底層表小得多。還要注意，僅CBO能使用這個通路路徑。

<code>zx@ORCL></code><code>select</code> <code>x,y </code><code>from</code> <code>t </code><code>where</code> <code>y=5;</code>

<code>Plan hash value: 2497555198</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 10 | 81 (2)| 00:00:01 |</code>

<code>|* 1 | </code><code>INDEX</code> <code>FAST </code><code>FULL</code> <code>SCAN| IDX_T | 1 | 10 | 81 (2)| 00:00:01 |</code>

另一種情況下CBO也會使用T(x,y)上的索引，這就是索引跳躍式掃描。當且僅當索引的最前列(在上面的例子中最前列是x)隻有很少的幾個不同值，而且優化器了解這一點，跳躍式掃描(skip scan)就能很好地發揮作用。例如，考慮(GEMDER,EMPNO)上的一個索引，其中GENDER可取值有M和F，而且EMPNO是唯一的。對于以下查詢:

select * from t where empno=5;

可以考慮使用T上的那個索引采用跳躍式掃描方法來滿足這個查詢，這說明從概念上講這個查詢會如下處理：

select * from t where GENDER='M' and empno=5

union all

select * from t where GENDER='F' and empno=5

它會跳躍式地掃描索引，以為這是兩個索引：一個對應值M，另一個對應值F。

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t1 </code><code>as</code> <code>select</code> <code>decode(mod(rownum,2),0,</code><code>'M'</code><code>,</code><code>'F'</code><code>) gender,all_objects.* </code><code>from</code> <code>all_objects;</code>

<code>zx@ORCL></code><code>create</code> <code>index</code> <code>idx_t1 </code><code>on</code> <code>t1(gender,object_id);</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>user</code><code>,</code><code>'T1'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>);</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>t1 </code><code>where</code> <code>object_id=42;</code>

<code>Plan hash value: 4072187533</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 100 | 4 (0)| 00:00:01 |</code>

<code>| 1 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| T1 | 1 | 100 | 4 (0)| 00:00:01 |</code>

<code>|* 2 | </code><code>INDEX</code> <code>SKIP SCAN | IDX_T1 | 1 | | 3 (0)| 00:00:01 |</code>

<code> </code><code>2 - access(</code><code>"OBJECT_ID"</code><code>=42)</code>

<code> </code><code>filter(</code><code>"OBJECT_ID"</code><code>=42)</code>

INDEX SKIP SCAN 步驟告訴Oralce要跳躍式掃描這個索引，查詢GENDER值有改變的地方，并從那裡開始向下讀樹，然後在所考慮的各個虛拟索引中查詢OBJECT_id=42。如果大幅增加GENDER的可取值，如下：

<code>zx@ORCL></code><code>alter</code> <code>table</code> <code>t1 </code><code>modify</code> <code>GENDER varchar2(2);</code>

<code>Table</code> <code>altered.</code>

<code>zx@ORCL></code><code>update</code> <code>t1 </code><code>set</code> <code>gender=(chr(mod(rownum,1024)));</code>

<code>84656 </code><code>rows</code> <code>updated.</code>

<code>zx@ORCL></code><code>commit</code><code>;</code>

<code>Commit</code> <code>complete.</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 101 | 344 (1)| 00:00:05 |</code>

<code>|* 1 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T1 | 1 | 101 | 344 (1)| 00:00:05 |</code>

<code> </code><code>1 - filter(</code><code>"OBJECT_ID"</code><code>=42)</code>

情況2：

在使用select count(*) from t查詢(或類似的查詢)，而且在表T上有一個B*樹索引。不過，優化器并不是統計索引條目，而是在全面掃描這個表(盡管索引比表要小)。在這種情況下，索引可能建立在一個允許有NULL值的列上。由于對于索引鍵完全為null的行不會建立相應的索引條目，是以索引中的行數可能并不是表中的行數。這裡優化器的選擇是對的，如若不然，倘若它使用索引來統計行數，則可能會得到一個錯誤的答案。

<code> </code><code>X NUMBER</code>

<code> </code><code>Y NUMBER</code>

<code>Plan hash value: 2966233522</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 153 (1)| 00:00:02 |</code>

<code>| 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T | 99999 | 153 (1)| 00:00:02 |</code>

<code>zx@ORCL></code><code>alter</code> <code>table</code> <code>t </code><code>modify</code> <code>y </code><code>not</code> <code>null</code><code>;</code>

<code> </code><code>Y </code><code>NOT</code> <code>NULL</code> <code>NUMBER</code>

<code>Plan hash value: 2371838348</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 80 (0)| 00:00:01 |</code>

<code>| 2 | </code><code>INDEX</code> <code>FAST </code><code>FULL</code> <code>SCAN| IDX_T | 99999 | 80 (0)| 00:00:01 |</code>

情況3：

對于一個有索引的列，做以下查詢：

select * from t where function(indexed_column)=value;

卻發現沒有使用indexed_colum上的索引。原因是這個列上使用了函數。如果是對indexed_column的值建立了索引，而不是對function(indexed_column)的值建索引。在此不能使用這個索引。如果願意，可以另外對函數建立索引。

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>t </code><code>where</code> <code>mod(x,999)=1;</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1000 | 34000 | 153 (1)| 00:00:02 |</code>

<code>|* 1 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T | 1000 | 34000 | 153 (1)| 00:00:02 |</code>

<code> </code><code>1 - filter(MOD(</code><code>"X"</code><code>,999)=1)</code>

<code>zx@ORCL></code><code>create</code> <code>index</code> <code>idx_t_f </code><code>on</code> <code>t(mod(x,999));</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>USER</code><code>,</code><code>'T'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>);</code>

<code>Plan hash value: 4125918735</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 100 | 3800 | 102 (0)| 00:00:02 |</code>

<code>| 1 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| T | 100 | 3800 | 102 (0)| 00:00:02 |</code>

<code>|* 2 | </code><code>INDEX</code> <code>RANGE SCAN | IDX_T_F | 100 | | 1 (0)| 00:00:01 |</code>

<code> </code><code>2 - access(MOD(</code><code>"X"</code><code>,999)=1)</code>

情況4：

考慮以下情況，已經對一個字元錢建立了索引。這個列隻包含資料資料。如果使用以下文法來查詢：

select * from t where indexed_colum=5;

注意查詢中的數字5是常數5(而不是一個字元串)，此時就沒有使用INDEXED_COLUMN上的索引。這是因為，前面的查詢等價于以下查詢：

select * from t where to_number(indexed_column)=5;

我們對這個列隐式地應用了一個函數，如情況3所述，這就會禁止使用這個索引。

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t2 (x </code><code>char</code><code>(1) </code><code>constraint</code> <code>t2_pk </code><code>primary</code> <code>key</code> <code>,y </code><code>date</code><code>);</code>

<code>zx@ORCL></code><code>insert</code> <code>into</code> <code>t2 </code><code>values</code><code>(</code><code>'5'</code><code>,sysdate);</code>

<code>1 row created.</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>USER</code><code>,</code><code>'T2'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>);</code>

<code>zx@ORCL>explain plan </code><code>for</code> <code>select</code> <code>* </code><code>from</code> <code>t2 </code><code>where</code> <code>x=5;</code>

<code>Explained.</code>

<code>zx@ORCL></code><code>select</code> <code>* </code><code>from</code> <code>table</code><code>(dbms_xplan.display);</code>

<code>PLAN_TABLE_OUTPUT</code>

<code>Plan hash value: 1513984157</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |</code>

<code>|* 1 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T2 | 1 | 12 | 3 (0)| 00:00:01 |</code>

<code> </code><code>1 - filter(TO_NUMBER(</code><code>"X"</code><code>)=5)</code>

<code> </code><code>- </code><code>dynamic</code> <code>sampling used </code><code>for</code> <code>this statement (</code><code>level</code><code>=2)</code>

可以看到，它會全面掃描表；另外即使我們對查詢給出了以下提示：

<code>zx@ORCL>explain plan </code><code>for</code> <code>select</code> <code>/*+ </code><code>index</code><code>(t2 t2_pk) */ * </code><code>from</code> <code>t2 </code><code>where</code> <code>x=5;</code>

<code>Plan hash value: 3365102699</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |</code>

<code>| 1 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| T2 | 1 | 10 | 2 (0)| 00:00:01 |</code>

<code>|* 2 | </code><code>INDEX</code> <code>FULL</code> <code>SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |</code>

<code> </code><code>2 - filter(TO_NUMBER(</code><code>"X"</code><code>)=5)</code>

在此使用了索引，但是并不像我們想像中那樣對索引完成唯一掃描(UNIQUE SCAN)，而是完成了全面掃描(FULL SCAN)。原因從最後一行輸出可以看出:filter(TO_NUMBER("X")=5)。這裡對這個資料庫列應用了一個隐式函數。X中存儲的字元串必須轉換為一個數字，之後才能與值5進行比較。在此無法把5轉換為一個串，因為我們的NLS(國家語言支援)設定會控制5轉換成串時的具體形式(而這是不确定的，不同的NLS設定會有不同的控制)，是以應當把串轉為資料。而這樣一樣(由于應用也函數)，就無法使用索引來快速地查找這一行了。如果隻是執行串與串的比較：

<code>zx@ORCL>explain plan </code><code>for</code> <code>select</code> <code>* </code><code>from</code> <code>t2 </code><code>where</code> <code>x=</code><code>'5'</code><code>;</code>

<code>Plan hash value: 3897349516</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |</code>

<code>| 1 | </code><code>TABLE</code> <code>ACCESS </code><code>BY</code> <code>INDEX</code> <code>ROWID| T2 | 1 | 12 | 1 (0)| 00:00:01 |</code>

<code>|* 2 | </code><code>INDEX</code> <code>UNIQUE</code> <code>SCAN | T2_PK | 1 | | 1 (0)| 00:00:01 |</code>

<code> </code><code>2 - access(</code><code>"X"</code><code>=</code><code>'5'</code><code>)</code>

<code>14 </code><code>rows</code> <code>selected.</code>

不出所料，這會得到我們期望的INDEX UNIQUE SCAN,而且可以看到這裡沒有應用函數。一定要盡可能地避免隐式轉換。

還經常出現一個關于日期的問題，如果做以下查詢：

select * from t where trunc(date_col)=trunc(sysdate);

而且發現這個查詢沒有使用DATE_COL上的索引，為了解決這個問題，可以對trunc(date_col)建立索引，或者使用區間比較運算符來查詢(也許這是更容易的做法)。下面來看對日期使用大于或小于運算符的一個例子。可以認識到以下條件：

trunc(date_col)=trunc(sysdate)

與下面的條件是一樣的：

date_col>= trunc(sysdate) and date_col<trunc(sysdate+1)

如果可能的話，倘若謂詞中有函數，盡量不要對資料庫列應用這些函數。這樣做不僅可以使用更多的索引，還能減少處理資料庫所需的工作。使用轉換的條件查詢時隻會計算一次TRUNC值，然後就能使用索引來查找滿足條件的值。使用trunc(date_col)=trunc(sysdate)時，trunc(date_col)則必須對整個表(而不是索引)中的每一行計算一次。

情況5：

另一種情況，如果使用了索引，實際上反而會更慢。Oracle(對于CBO而言)隻會在合理地時候才使用索引。

<code>zx@ORCL></code><code>create</code> <code>table</code> <code>t3 (x,y </code><code>null</code><code>,</code><code>primary</code> <code>key</code> <code>(x) ) </code><code>as</code> <code>select</code> <code>rownum x,object_name y </code><code>from</code> <code>all_objects;</code>

<code>zx@ORCL></code><code>exec</code> <code>dbms_stats.gather_table_stats(</code><code>USER</code><code>,</code><code>'T3'</code><code>,</code><code>cascade</code><code>=></code><code>true</code><code>);</code>

<code>zx@ORCL></code><code>set</code> <code>autotrace traceonly explain </code>

<code>--運作一個查詢查詢相對較少的資料</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(y) </code><code>from</code> <code>t3 </code><code>where</code> <code>x<50;</code>

<code>Plan hash value: 1961899233</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |</code>

<code>| 1 | SORT AGGREGATE | | 1 | 5 | | |</code>

<code>|* 2 | </code><code>INDEX</code> <code>RANGE SCAN| SYS_C0017451 | 49 | 245 | 2 (0)| 00:00:01 |</code>

<code> </code><code>2 - access(</code><code>"X"</code><code><50)</code>

<code>--運作一個查詢查詢相對較多的資料</code>

<code>zx@ORCL></code><code>select</code> <code>count</code><code>(y) </code><code>from</code> <code>t3 </code><code>where</code> <code>x<50000;</code>

<code>Plan hash value: 463314188</code>

<code>| 0 | </code><code>SELECT</code> <code>STATEMENT | | 1 | 30 | 117 (1)| 00:00:02 |</code>

<code>| 1 | SORT AGGREGATE | | 1 | 30 | | |</code>

<code>|* 2 | </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| T3 | 50000 | 1464K| 117 (1)| 00:00:02 |</code>

<code> </code><code>2 - filter(</code><code>"X"</code><code><50000)</code>

這個例子顯示出優化器不一定會使用索引，而且實際上，它會做出正确的選擇。對查詢調優時，如果發現你認為本該使用的某個索引實際上并沒有用到，就不要冒然強制使用這個索引，而應該先做個測試，并證明使用這個索引後确實會加快速度(通過耗用時間和I/O次數來評判)，然後再考慮讓CBO就範(強制它使用這個索引)。總得先給出個理由吧。

情況6：

有一段時間沒有分析表了。這些表起先很小，但等到檢視時，它們已經增長得非常大。現在索引就有很有意義(盡管原先并非如此)。如果此時分析這個表，就會使用索引。

如果沒有正确的統計資訊，CBO将無法做出正确的決定。

以上介紹了6種不使用索引的情況，歸根結底原因通常就是“不能使用索引，使用索引會傳回不正确的結果”，或者“不應該使用，如果使用了索引，性能會變得很糟糕”。

參考:《9I10G11G程式設計藝術深入資料庫體系結構》

MOS文檔：Diagnosing Why a Query is Not Using an Index (文檔 ID 67522.1)

本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1893241，如需轉載請自行聯系原作者

Oracle不使用索引的幾種情況列舉

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List