Funktions-basierte Indizes sind eine sehr generische Lösung. Man kann damit nicht nur Datenbank-Funktionen wie UPPER „indizieren“, sondern auch Ausdrücke wie A + B. Man kann in einer Indexdefinition sogar benutzer-definierte Funktionen verwenden.
Dennoch gibt es eine wichtige Einschränkung. So ist es zum Beispiel nicht möglich, die aktuelle Zeit in einer Indexdefinition zu verwenden. Weder direkt noch indirekt, wie im folgenden Beispiel:
CREATE FUNCTION get_age(date_of_birth DATE)
RETURN NUMBER
AS
BEGIN
RETURN
TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
ENDDie Funktion GET_AGE benutzt das aktuelle Datum (SYSDATE), um das Alter zu berechnen. Diese Funktion kann in allen Teilen einer SQL-Abfrage benutzt werden: zum Beispiel in der select-Liste oder der where-Klausel:
SELECT first_name, last_name, get_age(date_of_birth)
FROM employees
WHERE get_age(date_of_birth) = 42Die Abfrage liefert alle Mitarbeiter, die 42 Jahre alt sind. Zur Optimierung liegt der Gedanke an einen Funktions-basierten Index nahe. Die Funktion GET_AGE kann aber nicht in einer Indexdefinition verwendet werden, weil sie nicht deterministisch ist. Das bedeutet, dass das Ergebnis nicht ausschließlich von den Parametern abhängig ist. Nur Funktionen, die bei denselben Eingabeparametern immer dasselbe Ergebnis liefern – deterministische Funktionen – können in einer Indexdefinition verwendet werden.
Hinweis in eigener Sache
Ich lebe von SQL-Schulungen, SQL-Tuning und Beratung sowie dem Verkauf meines Buches „SQL Performance Explained“. Mehr auf winand.at.
Der Grund für diese Einschränkung ist einfach: Beim Einfügen eines neuen Datensatzes wird die Funktion aufgerufen und das Ergebnis im Index gespeichert. Und dort bleibt es auch. Unverändert. Das Alter, das im Index gespeichert wurde, stimmt also nur bis zum nächsten Geburtstag des Mitarbeiters. Es gibt keinen Prozess, der den Index regelmäßig aktualisieren würde. Nur wenn das Geburtsdatum in der Tabelle geändert wird, wird auch der Index aktualisiert.
Abgesehen davon, dass eine Funktion deterministisch sein muss, muss sie bei PostgreSQL und der Oracle Datenbank auch entsprechend deklariert werden. Das erfolgt mit den Schlüsselwörtern DETERMINISTIC (Oracle) beziehungsweise IMMUTABLE (PostgreSQL).
Achtung
PostgreSQL und die Oracle Datenbank vertrauen den Deklarationen IMMUTABLE und DETERMINISTIC. Das heißt, sie vertrauen den Entwicklern. Man kann die GET_AGE-Funktion also entsprechend deklarieren und in einer Indexdefinition verwenden.
Das ändert aber nichts daran, dass dieser Index nicht wie gewünscht funktionieren wird, weil sich das gespeicherte Alter über die Jahre nicht ändert. Die Mitarbeiter werden nicht älter – zumindest nicht im Index.
Andere „nicht indizierbare“ Funktionen sind Zufallszahlen-Generatoren und sämtliche Funktionen, die von Umgebungsvariablen abhängig sind.
Beachte
DB2 kann keine benutzer-definierten Funktionen in Indizes nutzen - egal ob deterministisch oder nicht.
Tipp
Wie kann man die Suche nach allen Mitarbeitern, die 42 Jahre alt sind, dennoch mit einem Index optimieren?
