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

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

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth)

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

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = ?
   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_BIRTHがNULLでも インデックスは全行を含むようにできます。

DROP   INDEX emp_dob

CREATE INDEX emp_dob ON employees (date_of_birth, 'X')

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