Index-Filterprädikate gezielt einsetzen

Index-Filterprädikate sind ein häufiges Symptom ineffizienter Indexnutzung bei einer falschen Spaltenreihenfolge. Sie können aber auch gezielt eingesetzt werden. Allerdings nicht um den Indexzugriff zu optimieren, sondern um die dazugehörenden Tabellendaten „griffbereit“ im Index abzulegen.

So kann man zum Beispiel Bedingungen, die ohnehin nicht als Zugriffs­prädikate taugen, dennoch in den Index aufnehmen, um sie absichtlich als Index-Filterprädikat zu benutzen.

SELECT first_name, last_name, subsidiary_id, phone_number
  FROM employees
 WHERE subsidiary_id = ?
   AND UPPER(last_name) LIKE '%INA%'

Der LIKE-Filter mit führendem Wildcard-Zeichen kann den Indexbaum nicht nutzen. Das bedeutet, dass das Indizieren der Spalte LAST_NAME den durchsuchten Indexbereich nicht schmälern kann. Das gilt unabhängig davon, ob die LAST_NAME-Spalte selbst oder die großgeschriebene Variante UPPER(last_name) indiziert wird. Die LIKE-Bedingung ist daher kein guter Kandidat für einen Index.

Die SUBSIDIARY_ID-Bedingung kann aber gut indiziert werden. Dazu ist nicht einmal ein neuer Index notwendig, da die SUBSIDIARY_ID ohnehin die erste Spalte im Primärschlüssel-Index ist.

Der Ausführungsplan zeigt die SUBSIDIARY_ID-Bedingung als Zugriffs­prädikat beim Indexzugriff und die LIKE-Bedingung als Filterprädikat beim Tabellenzugriff. Anhand der Cost-Werte kann man erahnen, dass der Hauptaufwand nicht der INDEX RANGE SCAN, sondern der Tabellenzugriff ist. Das ist ein typisches Muster, das für sich genommen noch kein Problem darstellt. Dennoch zeigt es, dass der Tabellenzugriff den größten Teil der Ausführungszeit beansprucht.

Der Tabellenzugriff muss aber nicht zwangsläufig ein Flaschenhals sein. Es kommt darauf an, auf wie viele Tabellenblöcke die Zeilen, die nach dem INDEX RANGE SCAN aus der Tabelle gelesen werden müssen, verteilt sind. Wenn die Tabellenzeilen in einer ähnlichen Reihenfolge wie im Index gespeichert sind, muss die Datenbank weniger Tabellenblöcke lesen, um die Zeilen zu laden. Die Zeilen, die im Index hintereinander stehen, stehen dann auch in der Tabelle eng beieinander.

Man kann die Zeilen in einer Tabelle tatsächlich so anordnen, dass die Reihenfolge mit dem Index übereinstimmt und damit weniger Tabellenblö­cke gelesen werden müssen. Diese Methode ist aber nur sehr beschränkt anwendbar, da man die Tabellendaten nur in einer Reihenfolge abspeichern kann. In den Indizes werden die Zeilen aber nach der jeweiligen Index­reihenfolge gespeichert. Man muss sich also entscheiden, für welchen Indexzugriff man die Tabelle optimieren will. Selbst wenn man sich für einen Index entscheiden kann, stellen die Datenbanken bestenfalls rudimentäre Werkzeuge zur Verfügung, die Reihenfolge in der Tabelle zu steuern. Alles in allem ist dieser Ansatz keine praktikable Lösung.

Genau hier kommt die zweite Macht der Indizierung ins Spiel. Man kann nahezu beliebig viele Spalten in einen Index aufnehmen, die automatisch mit einsortiert werden. Dadurch ist ein Index ein einfaches, aber dennoch mächtiges Werkzeug um Daten zu clustern.

Um diese Idee auf die Abfrage von oben anzuwenden, muss man alle Spalten der where-Klausel in den Index aufnehmen – auch wenn sie den durchsuchten Indexbereich nicht schmälern kann.

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name))

Die Spalte SUBSIDIARY_ID bleibt an erster Position, damit sie als Zugriffs­prädikat benutzt werden kann. Der Ausdruck UPPER(last_name) deckt den LIKE-Filter als gezieltes Index-Filterprädikat ab. Die Indizierung in Groß­schreibung spart einige CPU-Zyklen. Da die LIKE-Bedingung ohnehin nicht als Zugriffsprädikat genutzt werden kann, würde ein normaler Index auf LAST_NAME auch funktionieren. Die direkte Indizierung kann sogar Vorteile haben, wie wir im nächsten Abschnitt sehen werden.

Der neue Ausführungsplan zeigt dieselben Operationen wie zuvor. Der Cost-Wert ist insgesamt aber stark gefallen. Im Predicate Information-Bereich sieht man, dass der LIKE-Filter bereits beim INDEX RANGE SCAN angewandt wird. Die Zeilen, die den LIKE-Filter nicht erfüllen, werden also schon beim Indexzugriff verworfen. Beim Tabellenzugriff gibt es kein Filterprädikat mehr. Das bedeutet, dass es keinen Tabellenzugriff für Zeilen gibt, die die where-Klausel nicht erfüllen.

An der Rows-Spalte erkennt man den Unterschied der beiden Ausfüh­rungspläne besonders deutlich. Laut Schätzung des Optimizers liefert die Abfrage letztendlich 17 Zeilen. Beim ersten Ausführungsplan sind es nach dem Indexzugriff aber 333 Ergebnisse, die erst beim Tabellenzugriff auf 17 reduziert werden. Im zweiten Ausführungsplan liefert der Indexzugriff die Einträge, die den LIKE-Filter nicht erfüllen, erst gar nicht. Dadurch muss die TABLE ACCESS BY INDEX ROWID-Operation nur 17 Mal statt 333 Mal ausgeführt werden.

Weiters ist zu beachten, dass der Cost-Wert der INDEX RANGE SCAN-Opera­tion von zwei auf drei gestiegen ist. Das liegt daran, dass der neue Index größer ist und daher mehr Speicher belegt. Angesichts des Gewinnes ist das aber ein akzeptabler Kompromiss.

Die folgende Animation zeigt den Unterschied zwischen den beiden Ausführungsplänen:

Abbildung 5.1 Gezielte Index-Filterprädikate

Ein Index wächst auch mit der Spaltenzahl – insbesondere wenn man große Textspalten aufnimmt. Die Performance wird dadurch natürlich nicht besser. Man sollte also keineswegs alle Spalten der where-Klausel in den Index aufnehmen, sondern Index-Filterprädikate nur gezielt einsetzen, um das Datenvolumen frühzeitig zu reduzieren.

Dieses Beispiel scheint die Binsenweisheit, dass jede Spalte der where-Klausel in den Index gehört, zu bestätigen. Dabei bleibt die Spalten­reihenfolge allerdings auf der Strecke. Es ist aber ausgerechnet die Spaltenreihenfolge, die über Zugriffs- und Filterprädikate entscheidet und damit den wichtigsten Beitrag zur Performance liefert. Diese Entscheidung sollte man keinesfalls dem Zufall überlassen.