von Markus Winand.

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

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

Tipp

Ein fehlender NOT NULL-Constraint kann bei der Oracle Datenbank die Indexnutzung einschränken – insbesondere bei count(*)-Abfragen.

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

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz