NOT NULL制約


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

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

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

次のインデックスは、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 |
----------------------------------------------------
Tweet this tip

ヒント

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
   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 |
-------------------------------------------------------------

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