NOT NULL-Constraints

Wenn man eine IS NULL-Bedingung in der Oracle Datenbank indizieren möchte, muss der Index eine Spalte beinhalten, die niemals NULL sein kann.

Dafür genügt es nicht, dass keine NULL-Einträge vorhanden sein. Die Daten­bank muss erkennen, dass es keinen NULL-Eintrag geben kann. Ansonsten muss sie davon ausgehen, dass es Zeilen geben könnte, die nicht im Index sind.

Der folgende Index unterstützt die Abfrage daher nur, weil die Spalte LAST_NAME einen NOT NULL-Constraint hat.

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

Löscht man den NOT NULL-Constraint, kann der Index nicht mehr für diese Abfrage genutzt werden:

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

Neben Spalten mit NOT NULL-Constraint erkennt die Datenbank auch, dass konstante Ausdrücke wie im vorherigen Abschnitt nicht NULL sein können.

Bei der Indizierung mit einer Benutzer-definierten Funktion gibt es aber keinen NOT NULL-Constraint:

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

Der Funktionsname BLACKBOX macht deutlich, dass der Optimizer nicht weiß, was die Funktion macht (siehe „Groß- und Kleinschreibung mit UPPER oder LOWER ignorieren“). Wir können zwar sehen, dass die Eingabedaten einfach durchgereicht werden. Für die Datenbank ist es aber lediglich eine Funktion, die einen numerischen Wert liefert. Die NOT NULL-Eigenschaft der Spalte EMPLOYEE_ID geht verloren. Es sind zwar alle Zeilen im Index, die Datenbank weiß das aber nicht. Daher kann sie den Index nicht nutzen.

Wenn man, wie in diesem Fall, weiß, dass die Funktion niemals NULL liefert, kann man die Abfrage anpassen:

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

Durch die zusätzliche Bedingung in der where-Klausel werden nur noch Zeilen selektiert, die per Definition im Index sind.

Leider kann man Funktionen, die niemals NULL liefern, nicht kennzeichnen. Man kann den Funktionsaufruf aber in eine virtuelle Spalte (ab 11g) ausla­gern und einen NOT NULL-Constraint auf diese Spalte legen.

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

Bei manchen internen Funktionen weiß die Oracle Datenbank sogar, dass sie nur NULL liefern, wenn sie NULL als Eingangswert erhalten:

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

Bei der UPPER-Funktion bleibt die NOT NULL-Eigenschaft des Parameters erhalten. Wenn man den Constraint von der Basisspalte löscht, ist der Index für diese Abfrage nicht mehr nutzbar:

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