Bisher haben wir uns nur damit beschäftigt, welche Spalten in einen Index aufgenommen werden. Mit partiellen (PostgreSQL) oder gefilterten (SQL Server) Indizes kann man auch steuern, welche Zeilen in einen Index aufgenommen werden.
Achtung
Die Oracle Datenbank hat einen besonderen Zugang zu partiellen Indizes. Der nächste Abschnitt geht darauf ein, baut aber auf diesen Abschnitt auf.
DB2 unterstützt keine partiellen Indizes. Durch EXCLUDE NULL KEY kann man ähnlich wie bei Oracle emulieren.
Partielle Indizes sind für häufig verwendete where-Bedingungen mit konstanten Werten nützlich. Im folgenden Beispiel ist das der Status-Code:
SELECT message
FROM messages
WHERE processed = 'N'
AND receiver = ?Abfragen dieser Form kommen bei Warteschlangen häufig vor. Es werden alle Nachrichten für einen bestimmten Empfänger gesucht, die noch nicht verarbeitet wurden. Nachrichten, die bereits verarbeitet wurden, werden nur sehr selten gesucht. Und falls doch, mit einem konkreten Suchkriterium wie dem Primärschlüssel.
Mit einem zweispaltigen Index kann die Suche ideal unterstützt werden. Wenn man nur diese Abfrage betrachtet, ist die Spaltenreihenfolge sogar egal, weil keine Bereichsbedingung vorkommt.
CREATE INDEX messages_todo
ON messages (receiver, processed)Der Index erfüllt zwar seinen Zweck, beinhaltet aber sehr viele Einträge, die niemals gesucht werden – alle Nachrichten, die bereits verarbeitet wurden. Durch die logarithmische Skalierung des Indexes ist die Abfrage trotzdem schnell, der Speicherplatz ist aber verschwendet.
Mit partieller Indizierung kann man den Index auf die noch nicht verarbeiteten Nachrichten einschränken. Die Syntax dafür ist erstaunlich einfach: eine where-Klausel.
CREATE INDEX messages_todo
ON messages (receiver)
WHERE processed = 'N'Der Index beinhaltet also nur die Zeilen, die die where-Klausel erfüllen. Im konkreten Fall kann man die PROCESSED-Spalte sogar aus dem Index entfernen, da sie ohnehin immer 'N' ist. Dadurch schrumpft der Index in zwei Richtungen: vertikal, weil weniger Zeilen indiziert werden, horizontal, weil eine Spalte entfällt.
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 Index ist also sehr klein. Im Falle einer Warteschlange kann das sogar so weit gehen, dass die Indexgröße konstant bleibt, obwohl die Tabelle wächst. Schließlich ist nicht die ganze Tabelle indiziert, sondern nur die Nachrichten, die noch nicht verarbeitet wurden.
Die where-Klausel eines partiellen Indexes kann grundsätzlich beliebig komplex sein. Nur bei Funktionen gibt es eine Einschränkung: Man kann nur deterministische Funktionen verwenden – wie in der restlichen Indexdefinition auch. SQL Server hat jedoch sehr restriktive Einschränkungen und erlaubt weder Funktionen noch den OR-Operator in Index-Prädikaten.
Die Datenbank kann einen partiellen Index natürlich nur verwenden, wenn die where-Klausel der Indexdefinition unverändert in der Abfrage verwendet wird.
Denksport
Welche Besonderheit hat der kleinstmögliche Index für die folgende Abfrage:
SELECT message
FROM messages
WHERE processed = 'N'
