索引失效系列——說說is null

 

索引是我們進行最佳化的一種重要方式。實際工作中，一個簡單的索引，可能就會大大提升提高關鍵業務作業效率，最終提升使用者滿意度。在CBO時代，DBA和開發人員經常為索引為什麼不出現在執行計劃中而困惑。

 

 

問題提出

 

我們在一些SQL最佳化的規則中（雖然已經進入了CBO時代），經常看到“where條件中出現is null，搜尋不走索引”這樣的說法。這樣說法的原因是什麼呢？我們透過實驗一起來看一下。

 

 

SQL> create table t as select * from dba_objects;

 

Table created

 

SQL> update t set wner=null where wner='SCOTT';

 

34 rows updated

 

SQL> commit;

 

Commit complete

 

 

資料總量為：

 

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     51367

 

 

針對owner列為空的情況來說，超過五萬的資料列中只有34行，選擇性是相當好的，起碼對於null來說。那麼，我們加入索引。

 

SQL> create index idx_t_owner on t(owner);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

下面我們實驗對is null條件的檢索路徑。

 

 

SQL> explain plan for select * from t where owner is null;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |    70 |  6510 |   158   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    |    70 |  6510 |   158   (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OWNER" IS NULL)

 

13 rows selected

 

SQL> rollback;

 

Rollback complete

 

 

現象和流傳的規則相同，當我們條件中出現is null，也就是試圖搜尋列值為空的資料行，索引路徑是不出現在執行計劃中的。

 

如果不使用is null，轉為對取值列進行搜尋，索引路徑是可行的。

 

 

SQL> explain plan for select * from t where wner='PERFSTAT';

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1516787156

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

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

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

|   0 | SELECT STATEMENT            |             |   136 | 12648 |     5   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T       |   136 | 12648 |     5   (0)|

|*  2 |   INDEX RANGE SCAN     | IDX_T_OWNER |   136 |       |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - access("OWNER"='PERFSTAT')

 

14 rows selected

 

 

對應is null條件，還存在一個is not null條件。如果選擇率合適，is not null執行計劃中時會帶入索引路徑嗎？

 

 

SQL> explain plan for select * from t where owner is not null;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3329754318

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

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

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

|   0 | SELECT STATEMENT            |             | 51297 |  4658K|    49   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           | 51297 |  4658K|    49   (0)|

|*  2 |   INDEX FULL SCAN           | IDX_T_OWNER |   139 |       |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - filter("OWNER" IS NOT NULL)

 

14 rows selected

 

 

 

一系列的實驗，結論是：當我們在where語句中加入is null條件時，對應列索引是不會出現在執行計劃中的。Is not null條件在選擇率合適的情況下，還是可能引入索引執行計劃的。

 

原因分析

 

 

從is null不走索引和is not null走索引這個現象，我們可以看出問題的一些端倪。Is null雖然不是通常的where條件格式，但是絕對不是引起執行計劃問題的核心因素。

 

核心原因還是在於資料列中出現null值的特殊性。我們思考一下索引構建的過程，將索引列值進行建樹，其中必然涉及到諸多的比較操作。Null值的特殊性就在於參與的運算大多取值為null。這樣的話，null值實際上是不能參與進建索引的過程。也就是說，null值不會像其他取值一樣出現在索引樹的葉子節點上。

 

 

這種情況是比較容易被證明的。我們在一些null值較多的列上建索引，會發現形成的索引樹體積很小，與列值不相匹配。

 

SQL> select count(*) from t where owner is not null;

 

  COUNT(*)

----------

       139

 

SQL> select num_rows from dba_indexes where index_name='IDX_T_OWNER';

 

  NUM_ROWS

----------

       139

 

 

從字典上看出，索引中對應的函式正好等於列非空的行數。證明空值的確是不會進入索引。這樣is null不走索引就好理解了，因為對應的行根本就沒有在索引樹上，所以也就不存在執行計劃中出現索引路徑了。

 

 

如何選擇

 

我們瞭解了is null的特性，那麼我們如何進行列選擇呢？如果我們希望is null的時候出現索引路徑，應該怎麼做呢？

 

答案是藉助適當的預設值和詳細的功能需求分析。在筆者之前的文章《非空+預設值——一種選擇方案思路》（http://space.itpub.net/17203031/viewspace-692784）中已經對這個問題進行過比較詳細的說明。

 

對null列的處理，我們可以透過列預設值+非空欄位的方法保證列不為空。這樣每行資料必然出現在索引樹上，對原有null的檢索條件就變為等於預設值的檢索條件，是可能出現索引執行計劃的。但是，要注意選擇的原則：

 

ü        原有對is null檢索的業務需求是否強烈，是否重要。如果通常沒有對該列is null的需求，或者該列絕大多數情況都是為null（即使有索引也會因為成本原因不走），那麼還是不建議設定預設值方案。因為小索引樹結構還是有很多優勢的；

ü        在對is null選擇是關鍵業務用例需求，並且取值null的行比較少，選擇率較好的時候，才考慮使用預設值方法來進行索引最佳化；

 

 

進入CBO時代之後，最佳化器變得越來越強大，同時也越來越複雜。只有透過不斷的實驗，結合實際需求和最佳化方向，才能在資源和效能之間選擇出一個最優的平衡點。