Groß- und Kleinschreibung mit UPPER oder LOWER ignorieren

Die Groß- und Kleinschreibung bei einer SQL-Abfrage zu ignorieren, ist relativ einfach: Man kann zum Beispiel beide Seiten des Vergleiches direkt in der Abfrage auf Großbuchstaben konvertieren:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand')

Die UPPER-Funktion führt zu den gewünschten Übereinstimmungen – egal, in welcher Schreibweise die Namen gespeichert sind oder wie der Such­begriff angegeben wird.

Die Logik der UPPER-Methode ist einwandfrei. Der Ausführungsplan aber nicht:

Unser alter Freund, der Full-Table-Scan, taucht wieder auf. Obwohl ein Index auf LAST_NAME vorhanden ist, kann er nicht benutzt werden – weil nicht nach LAST_NAME gesucht wird. Die Suche erfolgt auf UPPER(LAST_NAME). Das ist aus Sicht der Datenbank etwas völlig anderes.

Es ist eine Falle, in die wir alle gerne tappen. Wir verstehen den Zusam­men­hang zwischen LAST_NAME und UPPER(LAST_NAME) und erwarten, dass die Datenbank diesen Zusammenhang auch kennt. Aus Sicht der Datenbank sieht die Abfrage aber eher so aus:

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE BLACKBOX(...) = 'WINAND'

Die UPPER-Funktion ist nur eine Blackbox. Die Parameter des Funk­ti­ons­aufrufs sind völlig unerheblich, weil zwischen den Parametern und dem Ergebnis einer Funktion kein allgemeiner Zusammenhang besteht.

Damit ein Index benutzt werden kann, muss er den tatsächlichen Such­be­griff beinhalten. Das heißt, man darf nicht LAST_NAME indizieren, sondern UPPER(LAST_NAME):

CREATE INDEX emp_up_name 
    ON employees (UPPER(last_name))

Ein Index, dessen Definition Funktionen oder Ausdrücke enthält, wird Funktions-basierender Index (FBI) genannt. Dabei werden die Tabellendaten nicht eins zu eins in den Index kopiert, sondern das Ergebnis des Ausdruckes verwendet. Der Index speichert die Namen also in Großschreibung.

Die Datenbank kann einen Funktions-basierenden Index nutzen, wenn der Ausdruck aus der Indexdefinition unverändert in der SQL-Anweisung verwendet wird – wie im Beispiel oben. Der Ausführungsplan bestätigt das:

Dabei wird ein normaler INDEX RANGE SCAN genutzt, wie er in Kapitel 1 be­schrie­ben wurde. Der Indexbaum wird durchwandert und die Blattknoten-Liste verfolgt. Für einen Funktions-basierenden Index gibt es keine eigenen Zugriffs-Operationen oder Schlüsselwörter.

Der Ausführungsplan stimmt aber noch nicht ganz mit dem ohne UPPER überein. Die geschätzte Zeilenzahl ist viel höher. Besonders auffällig ist, dass die Zeilen beim Tabellenzugriff höher sind als beim INDEX RANGE SCAN. Wie kann es hundert Tabellenzugriffe geben, wenn der Indexzugriff nur 40 ROWIDs liefert? Es ist unmöglich. Solche Widersprüche deuten häufig auf Probleme mit den Statistiken hin. In diesem Fall liegt es daran, dass die Oracle Datenbank die Tabellenstatistiken beim Anlegen eines neuen In­dexes nicht aktualisiert (siehe „Oracle-Statistiken für funktions-basierende Indizes“).

Nach dem Aktualisieren der Statistiken gibt der Optimizer eine bessere Schätzung ab:

Obwohl die Ausführungsgeschwindigkeit durch die neuen Statistiken nicht besser wird – der Index wurde ohnehin richtig genutzt – ist es immer gut, einen Blick auf die Schätzungen des Optimizers zu werfen. Gerade die Zeilenschätzung (cardinality estimate) ist ein sehr wichtiger Wert, der auch in den Ausführungsplänen von SQL Server und PostgreSQL auftaucht.

SQL Server unterstützt Funktions-basierende Indizes in dieser Form nicht. Stattdessen kann man generierte Spalten (computed columns) für denselben Zweck verwenden. Dazu muss man der Tabelle zuerst eine berechnete Spalte hinzufügen, die man dann indizieren kann:

SQL Server und MySQL können den Index verwenden, wenn der indizierte Ausdruck in der Anweisung auftaucht. In einfachen Fällen können SQL Server und MySQL diesen Index sogar nutzen, ohne die Abfrage zu ändern. Manchmal muss man die Abfrage jedoch anpassen, sodass sie den Namen der neuen Spalte verwendet. Im Zweifelsfall immer den Ausführungsplan prüfen.