NULLに対するインデックス Oracleの場合


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を含むインデックスは作れないという都市伝説が 誤りであることを証明できます。

Tweet this tip

ヒント

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

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