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.

Die passende Tasse zu dieser Website findest du in unserem Shop.
Sieht gut aus und unterstützt meine Arbeit hier.

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

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.