by Hayato Matsuura.

NOT NULL制約


OracleでIS NULL条件にインデックスを作成する ためには、インデックスにNULLになり得ない列を含む必要があることを見てきました。

とは言え、それだけではNULLのエントリがないとは 言い切れません。データベースは、NULLがないのが確実 でなければ、インデックスに存在しない行があることを前提としなくてはならなくなるのです。

協力してください

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

次のインデックスは、LAST_NAME列にNOT NULL 制約がある時のみ、クエリをサポートします。

DROP INDEX emp_dob
CREATE INDEX emp_dob_name
          ON employees (date_of_birth, last_name)
SELECT *
  FROM employees
 WHERE date_of_birth IS NULL
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DOB_NAME |    1 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DATE_OF_BIRTH" IS NULL)

NOT NULL制約を削除すると、このクエリでインデックスは使えなくなってしまいます。

ALTER TABLE employees MODIFY last_name NULL
SELECT *
  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 |
----------------------------------------------------

ヒント

NOT NULL制約がないと、 Oracleではインデックスが使えません。特に、count(*)に注意しましょう。

NOT NULL制約に加えて、前節のような定数表現が NULLにならないことも、データベースが分かる必要があります。

ところが、ユーザ定義関数に対するインデックスは、インデックスの表現の上ではNOT NULLを強制するようにはなっていません。

CREATE OR REPLACE FUNCTION blackbox(id IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
   RETURN id;
END
DROP INDEX emp_dob_name
CREATE INDEX emp_dob_bb 
    ON employees (date_of_birth, blackbox(employee_id))
SELECT *
  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 |
----------------------------------------------------

関数BLACKBOXは、関数が何をするのかオプティマイザが 分からないということを強調するものです(UPPERLOWERを使った 大文字・小文字を区別する検索を参照)。 この関数は、入力値をそのまま返すものだということは見れば分かりますが、データベースから見ると、何らかの数値を返すものだということしか 分かりません。引数に対するNOT NULL制約は、なかったことに なっています。インデックスは全ての行を含んでいるはずですが、データベースはそのことが分からないので、クエリに対してインデックスを 使えないのです。

関数が絶対にNULLを返さないことがあらかじめ 分かっているなら、そのことを以下の例のようにクエリに反映させることができます。

SELECT *
  FROM employees
 WHERE date_of_birth IS NULL
   AND blackbox(employee_id) IS NOT NULL
-------------------------------------------------------------
|Id |Operation                   | Name       | Rows | Cost |
-------------------------------------------------------------
| 0 |SELECT STATEMENT            |            |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DOB_BB |    1 |    2 |
-------------------------------------------------------------

whereに追加された条件は常に真なので、 結果は変わりません。しかしこの場合は、インデックスの定義によって、インデックスに必ず含まれる行のみを検索しているのだとOracleは 認識することができるのです。

残念なことに、ある関数がNULLを返さない ことを示す方法はありませんが、関数の呼び出しをバーチャルカラム(11g以降で使用可)に関数を入れ、 その列にNOT NULL制約をつけることが可能です。

ALTER TABLE employees ADD bb_expression
      GENERATED ALWAYS AS (blackbox(employee_id)) NOT NULL
DROP   INDEX emp_dob_bb
CREATE INDEX emp_dob_bb 
    ON employees (date_of_birth, bb_expression)
SELECT *
  FROM employees
 WHERE date_of_birth IS NULL
-------------------------------------------------------------
|Id |Operation                   | Name       | Rows | Cost |
-------------------------------------------------------------
| 0 |SELECT STATEMENT            |            |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DOB_BB |    1 |    2 |
-------------------------------------------------------------

Oracleは、内部の関数はNULLが与えられた時だけ NULLを返すことを知っています。

DROP INDEX emp_dob_bb
CREATE INDEX emp_dob_upname 
    ON employees (date_of_birth, upper(last_name))
SELECT *
  FROM employees
 WHERE date_of_birth IS NULL
----------------------------------------------------------
|Id |Operation                   | Name           | Cost |
----------------------------------------------------------
| 0 |SELECT STATEMENT            |                |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_DOB_UPNAME |    2 |
----------------------------------------------------------

UPPER関数は、LAST_NAME列のNOT NULL制約を保持しています。そのため制約を削除すると、インデックスは使えなくなります。

ALTER TABLE employees MODIFY last_name NULL
SELECT *
  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 |
----------------------------------------------------

著者について

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