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

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は、関数が何をするのかオプティマイザが 分からないということを強調するものです(「UPPERとLOWERを使った 大文字・小文字を区別する検索」を参照)。 この関数は、入力値をそのまま返すものだということは見れば分かりますが、データベースから見ると、何らかの数値を返すものだということしか 分かりません。引数に対する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 |
----------------------------------------------------