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

そのような結果にも関わらず、インデックスに含まれる列の 最低でも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

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

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

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/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license