by Hayato Matsuura.

NULLに対するインデックス


Oracleは、インデックス定義に含まれる列の全てがNULLの 時は、その行をインデックスに含めません。つまり、全てのインデックスが where句が以下のような部分インデックスであると 言えます。

CREATE INDEX idx
          ON tbl (A, B, C, ...)
       WHERE A IS NOT NULL
          OR B IS NOT NULL
          OR C IS NOT NULL
             ...

EMP_DOBという、DATE_OF_BIRTH列のみの インデックスを考えてみましょう。 DATE_OF_BIRTHの値がない行は、インデックスに追加される 事はありません。

INSERT INTO employees ( subsidiary_id, employee_id
                      , first_name   , last_name
                      , phone_number)
               VALUES ( ?, ?, ?, ?, ? )

このinsert文はDATE_OF_BIRTHを 指定していないので、デフォルトのNULLなります。そのため、 このレコードはEMP_DOBインデックスには追加されません。 結果としてこのインデックスは、DATE_OF_BIRTH IS NULL のレコードを検索するクエリをサポートできなくなります。

SELECT first_name, last_name
  FROM employees
 WHERE date_of_birth IS NULL
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DATE_OF_BIRTH" IS NULL)

そのような結果にも関わらず、インデックスに含まれる列の 最低でも1つがNULLではない場合は、複合インデックスに そこのレコードは追加されます。

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth)

SUBSIDIARY_IDNULLではないので、 上の行はインデックスに追加されます。そのためこのインデックスは、 ある子会社に属するDATE_OF_BIRTHを持たない全従業員を 検索するクエリをサポートできるのです。

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = ?
   AND date_of_birth IS NULL
--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |    1 |    2 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    2 |
|* 2 |   INDEX RANGE SCAN          | DEMO_NULL |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(?) 
          AND "DATE_OF_BIRTH" IS NULL)

このインデックスがwhere句の全体を カバーしていることに注意しましょう。全てのフィルタが INDEX RANGE SCANの中でアクセス述語として 使われています。

DATE_OF_BIRTH IS NULLになっている レコードを検索するために、元のクエリにもこの考え方を適用することが できます。そのためには、DATE_OF_BIRTHの条件が アクセス述語に使えるように、インデックスの一番左になくては なりません。実際には、2番目以降の列はクエリには必要ありませんが、 インデックスが全ての行を含むように、NULLにはなり得ない 列を追加しましょう。SUBSIDIARY_IDのような、 NOT NULL制約の付いた列ならどれでも使うことができます。

代わりに、NULLになることのない定数表現も 使えます。そうすれば、DATE_OF_BIRTHNULLでも インデックスは全行を含むようにできます。

DROP   INDEX emp_dob
CREATE INDEX emp_dob ON employees (date_of_birth, '1')

技術的には、このインデックスは関数インデックスです。この例によって、 OracleではNULLを含むインデックスは作れないという都市伝説が 誤りであることを証明できます。

ヒント

他の値と同じようにNULLに インデックスを作成したい場合は、NULLになることはない 列をインデックスに追加しましょう。

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license