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.

Hinweis in eigener Sache

Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

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

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 |
----------------------------------------------------
Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„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 und DSGVO | CC-BY-NC-ND 3.0 Lizenz