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

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.