搜尋條件設定為Is Null一定不走索引嗎？

宣告：本文思路來源於laoxiong老師的博文，就此感謝！

 

在Oralce的搜尋中，如果where條件句後使用了is null搜尋條件，那麼即使該列上使用了索引，Oracle也不會進行索引搜尋。那麼，這條規則一定正確嗎？

 

構建實驗環境

 

準備資料環境，構建資料表。

 

SQL> create table t as select object_id,object_name,owner from dba_objects;

 

Table created

//設定一些object_id為null的情況

SQL> update t set object_id=null where wner='PERFSTAT';

 

139 rows updated

 

SQL> commit;

 

Commit complete

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     52806

 

//構建單值索引

SQL> create index idx_t_id on t(object_id);

 

Index created

//收集統計資訊

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all indexed columns');

 

PL/SQL procedure successfully completed

 

SQL> select * from dba_segments where segment_name='IDX_T_ID';

//索引：空間120個資料塊，0.98MB。

SEGMENT_NAME    SEGMENT_TYPE  HEADER_BLOCK      BYTES     BLOCKS    EXTENTS

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

IDX_T_ID            INDEX                66057     983040        120         15 

 

在這種情況下，使用索引列的is null條件，查詢是不會走索引的。

 

SQL> select * from t where object_id is null;

 

已選擇139行。

 

已用時間:  00: 00: 00.06

 

執行計劃

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |   139 |  4726 |    68   (3)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |   139 |  4726 |    68   (3)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID" IS NULL)

統計資訊

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

        128  recursive calls

          0  db block gets

        323  consistent gets

          0  physical reads

          0  redo size

       5416  bytes sent via SQL*Net to client

        484  bytes received via SQL*Net from client

         11  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

        139  rows processed

 

實驗說明，在有索引的情況下，使用is null的搜尋語句是不會走索引路徑的。

 

 

分析原因

 

這種情況出現的原因，本質上是因為oracle索引在生成過程中，是不會為索引列為null的資料行建索引，使之成為索引樹的葉節點。更深層的原因，筆者認為是和null在oracle中，進行比較操作的過程中，表示式取值永遠為null有關。

 

那麼，對那些索引列為空的資料行，索引樹上是沒有對應的葉節點記錄的，也就無從談起藉助索引進行搜尋。

 

有一種思路，認為如果是構建組合索引，也就是將兩個或兩個以上列作為索引列，進行單列is null檢索的時候，是可能會走索引的。原理很簡單，對資料行來說，只有所有的索引列都是空的資料行，才不會被加入到索引葉節點。只要有一個索引列有值（或者定義其不能為空），就可以透過索引獲取到。

 

在laoxiong老師的部落格中，學到了一種思路，既然索引列集合需要有非空值才能進入索引葉節點，那麼可以利用常量進行索引構建。

 

 

常量組合索引

 

構建組合索引。

 

SQL> drop index idx_t_id;

 

Index dropped

 

SQL> create index idx_t_cmp1 on t(object_id,0); //找一個常量值0，構建索引；

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all indexed columns');

 

PL/SQL procedure successfully completed

 

此時，索引資料字典。

 

SQL> select * from dba_segments where segment_name='IDX_T_CMP1';

 

//發現組合索引的體積擴大很多，達到了256個塊，2MB大小。

SEGMENT_NAME  SEGMENT_TYPE    HEADER_BLOCK      BYTES     BLOCKS    EXTENTS

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

IDX_T_CMP1    INDEX                         66057    2097152        256         17 

 

 

 

進行搜尋is null。

 

SQL> select * from t where object_id is null;

 

已選擇139行。

 

已用時間:  00: 00: 00.03

 

執行計劃

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

Plan hash value: 3648234756

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

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

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

|   0 | SELECT STATEMENT            |            |   139 |  4726 |     3   (0)|00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T          |   139 |  4726 |     3   (0)|00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_CMP1 |   139 |       |     2   (0)|00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID" IS NULL)

 

統計資訊

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

        139  recursive calls

          0  db block gets

         49  consistent gets

          0  physical reads

          0  redo size

       5416  bytes sent via SQL*Net to client

        484  bytes received via SQL*Net from client

         11  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

        139  rows processed

 

結論：雖然使用了is null，但是查詢還是選擇了索引作為執行路徑。相比較全表掃描的方法。對比如下。

 

 

對比專案中，發現效能提升比較明顯。

 

 

利弊分析

 

使用索引是我們最佳化效能的一個重要手段。is null因為其特殊性，是不能納入到最佳化範疇的。藉助本文中的常陣列合索引，可以實現is null查詢的索引，同時兼顧原有取值查詢的索引路徑。

 

但是，這種方法是存在一定折中的，就是索引體積的變化。從上面的例子看出，使用常量組合索引之後，索引的體積擴大了一倍。這種擴大主要是對常量值葉佔位和空行資訊加入上。

 

實際應用中，也要考慮全表中空值的數量比例關係，選擇合適的情景進行設定。

 

 

此外，還要注意索引列的長度體積。如果是資料值型別比較小的列，效果較好。如果資料值都比較大，Oracle最佳化器在選擇路徑的時候，可能還是會選擇全表掃描。