von Markus Winand.

Partielle Indizes

Ausgewählte Zeilen indizieren


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 ver­ar­bei­teten 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 biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.

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 Ein­schränk­ungen 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'
Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz