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