Benutzer-definierte Funktionen


Gilt für
DB2Nein
MySQLNein
OracleJa
PostgreSQLJa
SQL ServerJa

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);
END

Die 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) = 42

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

Für alle Anwendungsentwickler […] sollte der schmale Band […] eine Pflichtlektüre sein — ADMIN-Magazin

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.

Denksport

Wie kann man die Suche nach allen Mitarbeitern, die 42 Jahre alt sind, dennoch mit einem Index optimieren?

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

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