[zt] Oracle不使用索引的幾種情況

Oracle不使用b*tree索引的情況大致如下

1：where條件中和null比較可能導致不使用索引

2：count，sum，ave，max，min等聚集操作時可能導致不使用索引

3：顯示或者隱式的函式轉換導致不使用索引

4：在cbo模式下，統計資訊過於陳舊導致不使用索引

5：組合索引中沒有使用前導列導致沒有使用索引

6：訪問的資料量超過一定的比例導致不使用索引

下面就其中的幾點做一些說明

一：Null可以使用索引嗎

一般情況下，where條件中和null比較將會導致 ，實際上，如果table中索引建列的值都為null，那麼該行在索引（此處指b*tree，點陣圖索引和聚簇索引可以有空值）中就不會存在，因此oracle為了保證查詢結構的準確性，就會用full table scan代替 scan，這樣理解，不走索引也就在情理之中。

當然，如果某個索引列上有定義為not null，在這種情況下，不存在所有索引列都為空的情況，所以此種情況下，是可以走index scan的，因此，對於where條件中含有類似is null，=null的情況，是否走索引，還是要看索引建中是否有某個列定義為not null。

具體實驗如下：

SQL> create table t(x char(3),y char(5));

SQL> insert into t(x,y) values ('001','xxxxx');

SQL> insert into t(x,y) values ('002',null);

SQL> insert into t(x,y) values (null,'yyyyy');

SQL> insert into t(x,y) values (null,null);

SQL> commit;

SQL> create unique index t_idx on t(x,y);

SQL> analyze table t compute statistics for table for all indexes;

SQL> select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t_idx');

   BLEVEL LEAF_BLOCKS  NUM_ROWS

---------- ----------- ----------

        0          1         3

isnert四條記錄，但索引只儲存3條，最後一條沒有儲存在索引中

SQL> set autotrace traceonly explain;

SQL> select * from t where x is null;

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=8)

  1   0  TABLE ACCESS (FULL)OF 'T' (Cost=2 Card=1 Bytes=8)

 

SQL> create table t1(x char(3),y char(5) not null);

SQL> insert into t1(x,y) values ('001','xxxxx');

SQL> insert into t1(x,y) values (null,'xxxxx');

SQL> commit;

SQL> create unique index t1_idx on t1(x,y);

SQL> analyze table t1 compute statistics for table for all indexes;

SQL> select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t1_idx');

   BLEVEL LEAF_BLOCKS  NUM_ROWS

---------- ----------- ----------

        0          1         2

SQL> select * from t1 where x is null;

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=11)

  1   0  INDEX (RANGE SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=1 Byt

二：COUNT(*)等聚集函式可能導致不使用索引

在做count，sum，ave，max，min等聚集操作時，有的時候也會不用索引，因為如果最佳化器發現索引列沒有任何一個列定義為not null，而且where條件中也沒有索引鍵列，如x=x,在此情況下，索引掃描結果會不準確，此時oracle就會用全表full table scan。沿用上面的二個表來說明

SQL> select count(*) from t;

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1)

  1   0  SORT (AGGREGATE)

  2   1    TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=4)

 

SQL> select sum(x) from t;

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=5)

  1   0  SORT (AGGREGATE)

  2   1    TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=4 Bytes=20)

因為該表的索引列（x，y）沒有定義為not null，所以都走了全表掃描，即使把x=null，y=null的行刪除，同樣還是走全表掃描。

SQL> delete t where x is null and y is null;

已刪除1行。

Commit

SQL> analyze table t compute statistics for table for all indexes;

SQL> select count(*) from t;

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1)

  1   0  SORT (AGGREGATE)

  2   1    TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=3)

 

同樣的語句，對於t1表，因為索引列y定義為not null，所以oracle會選擇index scan

SQL> select count(*) from t1;

 

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1)

  1   0  SORT (AGGREGATE)

  2   1    INDEX (FULL SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=2)

SQL> select sum(x) from t1;

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=5)

  1   0  SORT (AGGREGATE)

  2   1    INDEX (FULL SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=2 By

         tes=10)

三：隱式或者顯示的函式轉換降導致全表掃描

SQL> Select * from t1 where x=001;

X     Y         COMM

------ ---------- ----------------------------

001   xxxxx     88888

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=26)

  1   0  TABLE ACCESS (FULL) OF 'T1'(Cost=2 Card=1 Bytes=26)

因為x為char類似，在這裡oracle把x=001做了隱式轉換to_number(x)=001,建在該欄位的索引將不起作用，基於函式的索引（function based index）可以在此派上用場，相對於普通索引，fbi是把經過函式轉換後的值存放到索引中

SQL> create index t1_fbi on t1(to_number(x));

SQL> analyze table t1 compute statistics for table for all indexes;

SQL> Select * from t1 where x=001;

X     Y         COMM

------ ---------- ----------------------------

001   xxxxx     88888

Execution Plan

----------------------------------------------------------

  0     SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=26)

  1   0  TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes

         =26)

  2   1    INDEX (RANGE SCAN) OF 'T1_FBI'(NON-UNIQUE) (Cost=1 Card

         =1)

四：統計資訊不是最新的，導致無法使用

五：組合索引中沒有用到前導列導致沒有用索引,如組合索引（x，y）,where條件類似where y=….,此時不走索引（如果x的不同值很少，那麼oracle9i以後就有可能走index skip scan，其原理類似於select * from t where y=…and x=(某個確定的值) union all select * from t where y=…and x=(某個確定的值)……..

六：訪問的資料比例超過一定範圍，最佳化器會認為full table scan的成本更低，此事走索引掃描反而會使總成本變大，因此，索引用來快速訪問表中的少量記錄，對於訪問表中的大量記錄是不適合用索引的。