Partielle Indizes in der Oracle Datenbank emulieren


Man kann die merkwürdige NULL-Indizierung ausnutzen, um partielle Indizes in der Oracle Daten­bank umzusetzen. Dafür verwendet man einfach NULL für Zeilen, die nicht indiziert werden sollen.

Als Beispiel werden wir den folgenden partiellen Index in der Oracle Datenbank umsetzen:

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

Dafür benötigen wir zuerst eine Funktion, die den RECEIVER-Wert nur liefert, wenn der PROCESSED-Wert 'N' ist.

CREATE OR REPLACE
FUNCTION pi_processed(processed CHAR, receiver NUMBER)
RETURN NUMBER
DETERMINISTIC
AS BEGIN
   IF processed IN ('N') THEN
      RETURN receiver;
   ELSE
      RETURN NULL;
   END IF;
END

Die Funktion muss natürlich deterministisch sein, damit sie in einer Index­de­finition verwendet werden kann.

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

Damit kann man einen Index anlegen, der nur Zeilen mit dem PROCESSED-Wert 'N' beinhaltet:

CREATE INDEX messages_todo
          ON messages (pi_processed(processed, receiver))

Damit der Index benutzt wird, muss man in der Abfrage den indizierten Ausdruck verwenden:

SELECT message
  FROM messages
 WHERE pi_processed(processed, receiver) = ?
----------------------------------------------------------
|Id | Operation                   | Name          | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT            |               | 5330 |
| 1 |  TABLE ACCESS BY INDEX ROWID| MESSAGES      | 5330 |
|*2 |   INDEX RANGE SCAN          | MESSAGES_TODO | 5303 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PI_PROCESSED"("PROCESSED","RECEIVER")=:X)

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Partielle Indizes, Teil II

Ab Release 11g gibt es eine weitere – nicht weniger seltsame – Methode, partielle Indizes in der Oracle Datenbank zu emulieren. Dabei verwendet man absichtlich defekte Index-Partitionen und den SKIP_UNUSABLE_INDEX-Parameter.

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