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

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

情況1：

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

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

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

Table created.

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

  COUNT(*)
----------
     99999

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

Index created.

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

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
--where條件使用y=5
zx@ORCL>select * from t where y=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |    80   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    15 |    80   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=5)
--where條件使用x=5
zx@ORCL>select * from t where x=5;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    15 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=5)

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

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

Execution Plan
----------------------------------------------------------
Plan hash value: 2497555198

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |    10 |    81   (2)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T |     1 |    10 |    81   (2)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=5)

另一種情況下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。

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

Table created.

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

Index created.

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

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
zx@ORCL>select * from t1 where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 4072187533

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   100 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   100 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_T1 |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=42)
       filter("OBJECT_ID"=42)

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

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

Table altered.

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

84656 rows updated.

zx@ORCL>commit;

Commit complete.

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

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain
zx@ORCL>select * from t1 where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   101 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   101 |   344   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=42)

情況2：

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

zx@ORCL>desc t;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 X                                                                                                              NUMBER
 Y                                                                                                              NUMBER
 Z                                                                                                              CHAR(23)
zx@ORCL>select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   153   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 99999 |   153   (1)| 00:00:02 |
-------------------------------------------------------------------

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

Table altered.

zx@ORCL>desc t
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 X                                                                                                              NUMBER
 Y                                                                                                     NOT NULL NUMBER
 Z                                                                                                              CHAR(23)
 
zx@ORCL>select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    80   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_T | 99999 |    80   (0)| 00:00:01 |
-----------------------------------------------------------------------

情況3：

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

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

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

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 34000 |   153   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  1000 | 34000 |   153   (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MOD("X",999)=1)

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

Index created.

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

PL/SQL procedure successfully completed.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 4125918735

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   100 |  3800 |   102   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T       |   100 |  3800 |   102   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_F |   100 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(MOD("X",999)=1)

情況4：

考慮以下情況，已經對一個字符錢建立了索引。這個列只包含數據數據。如果使用以下語法來查詢：

select * from t where indexed_colum=5;

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

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

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

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

Table created.

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

1 row created.

zx@ORCL>commit;

Commit complete.

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

PL/SQL procedure successfully completed.

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

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=5)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

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

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3365102699

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T2_PK |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("X")=5)

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

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

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3897349516

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T2_PK |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"='5')

14 rows selected.

不出所料，這會得到我們期望的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而言)只會在合理地時候才使用索引。

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

Table created.

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

PL/SQL procedure successfully completed.

zx@ORCL>set autotrace traceonly explain 
--運行一個查詢查詢相對較少的數據
zx@ORCL>select count(y) from t3 where x<50;

Execution Plan
----------------------------------------------------------
Plan hash value: 1961899233

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |              |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SYS_C0017451 |    49 |   245 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"<50)
--運行一個查詢查詢相對較多的數據
zx@ORCL>select count(y) from t3 where x<50000;

Execution Plan
----------------------------------------------------------
Plan hash value: 463314188

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    30 |   117   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T3   | 50000 |  1464K|   117   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"<50000)

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

情況6：

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

如果沒有正確的統計信息，CBO將無法做出正確的決定。

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

參考:《9I10G11G編程藝術  深入數據庫體系結構》

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