von Markus Winand.

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.

Hinweis in eigener Sache

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

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)

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

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.

Sein Buch bei Amazon kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„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