von Markus Winand.

NULL Indizieren


Die Oracle Datenbank nimmt eine Zeile nicht in einen Index auf, wenn alle indizierten Spalten NULL sind. Damit ist jeder Index ein partieller Index – so als hätte er eine where-Klausel:

CREATE INDEX idx
          ON tbl (A, B, C, ...)
       WHERE A IS NOT NULL
          OR B IS NOT NULL
          OR C IS NOT NULL
             ...

Betrachten wir dafür den Index EMP_DOB. Er besteht nur aus der Spalte DATE_OF_BIRTH. Eine Zeile ohne DATE_OF_BIRTH wird in diesen Index nicht aufgenommen:

INSERT INTO employees ( subsidiary_id, employee_id
                      , first_name   , last_name
                      , phone_number)
               VALUES ( ?, ?, ?, ?, ? )

Da die insert-Anweisung die Spalte DATE_OF_BIRTH nicht anführt, verwendet die Datenbank NULL dafür, sodass die Zeile nicht in den Index aufgenommen wird. Daraus folgt, dass man den Index nicht benutzten kann, um Einträge mit DATE_OF_BIRTH IS NULL zu finden.

SELECT first_name, last_name
  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 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DATE_OF_BIRTH" IS NULL)

Dennoch werden Zeilen in einen mehrspaltigen Index aufgenommen, wenn zumindest eine Indexspalte nicht NULL ist:

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth)

Die obere Zeile wird in diesen Index aufgenommen, da die SUBSIDIARY_ID-Spalte nicht NULL ist. Daher kann man mit diesem Index alle Mit­arbei­ter einer Zweigstelle suchen, zu denen kein Geburtsdatum gespeichert ist:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = ?
   AND date_of_birth IS NULL
--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
|  0 | SELECT STATEMENT            |           |    1 |    2 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    1 |    2 |
|* 2 |   INDEX RANGE SCAN          | DEMO_NULL |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(?) 
          AND "DATE_OF_BIRTH" IS NULL)

Beachte, dass der Index die vollständige where-Klausel abdeckt; alle Bedingungen werden während der Operation INDEX RANGE SCAN als Zugriffsprädikat verwendet.

Diese Methode kann man auch für die ursprüngliche Abfrage verwenden, um alle Mitarbeiter zu suchen, deren Geburtsdatum unbekannt ist. Dafür muss die Spalte DATE_OF_BIRTH an erster Stelle im Index stehen, damit sie als Zugriffsprädikat genutzt werden kann. Obwohl man für diese Abfrage keine zweite Indexspalte benötigt, muss man eine hinzufügen, damit jede Zeile in den Index aufgenommen wird. Dafür kann man jede beliebige Spalte in den Index aufnehmen, die wie die SUBSIDIARY_ID einen NOT NULL-Constraint hat.

Man kann aber auch einen konstanten Ausdruck verwenden, der niemals NULL sein kann. Dadurch ist sichergestellt, dass jede Zeile im Index ist – auch wenn die Spalte DATE_OF_BIRTH NULL ist.

DROP   INDEX emp_dob
CREATE INDEX emp_dob ON employees (date_of_birth, '1')

Technisch gesehen, handelt es sich bei diesem Index um einen Funktions-basierten Index. Damit ist dann wohl auch der Mythos widerlegt, dass die Oracle Datenbank NULL nicht indizieren kann.

Tipp

Durch eine Spalte, die niemals NULL sein kann, kann man NULL wie jeden normalen Wert indizieren.

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