Index-organisierte Tabellen und Clustered Indizes

Ein Index-Only-Scan führt eine SQL-Anweisung ausschließlich unter Nut­zung der redundanten Daten aus dem Index aus. Die Originaldaten in der Heap-Tabelle werden nicht benötigt. Wenn man das Konzept weiterführt und alle Spalten in den Index aufnimmt, stellt sich die Frage, wozu man die Heap-Tabelle überhaupt braucht?

Bei manchen Datenbanken kann man den Index tatsächlich als primären Tabellenspeicher benutzen. Bei der Oracle Datenbank nennt man dieses Konzept Index-organisierte Tabellen (IOT), andere Datenbanken verwenden den Begriff Clustered-Index. Im Folgenden werden beide Begriffe gemischt verwendet, je nachdem, ob der Index- oder Tabellencharakter im Vorder­grund steht.

Eine Index-organisierte Tabelle ist also ein B-Tree-Index ohne Heap-Tabelle. Daraus ergeben sich zwei Vorteile: (1) der Speicherplatz für die Heap-Struktur wird gespart; (2) jeder Zugriff auf den Clustered-Index ist automa­tisch ein Index-Only-Scan. Beide Vorteile klingen vielversprechend, können in der Praxis aber nur selten genutzt werden.

Sobald man weitere Indizes auf einer Index-organisierten Tabelle anlegt, kommen die Nachteile zum Vorschein. Analog zu einem normalen Index enthält ein solcher sekundärer Index ebenfalls Verweise auf die Tabellen­daten – die allerdings im Clustered-Index gespeichert sind. Dort sind die Tabellendaten aber nicht feststehend, wie in einer Heap-Tabelle, sondern können jederzeit verschoben werden, damit die Indexreihenfolge gewahrt bleibt. Daher kann man die physischen Adressen der Zeilen im Clustered-Index nicht im sekundären Index speichern. Stattdessen muss ein logischer Schlüssel verwendet werden.

Die folgenden Abbildungen zeigen, wie eine Suche nach allen Verkäufen vom 23. Mai 2012 über einen sekundären Index abläuft. Zum Vergleich betrachten wir in Abbildung 5.2 zunächst die Situation mit einer Heap-Tabelle. Die Ausführung erfolgt in zwei Schritten: (1) die Indexsuche (INDEX RANGE SCAN); (2) der Tabellenzugriff (TABLE ACCESS BY INDEX ROWID).

Abbildung 5.2 Indexgestützter Zugriff auf eine Heap-Tabelle

Der Tabellenzugriff kann zwar durchaus der limitierende Faktor sein, führt aber maximal einen Lesezugriff pro Zeile durch. Diese Begrenzung ergibt sich daraus, dass der Index die ROWID der Zeile speichert. Die exakte Spei­cherstelle in der Heap-Tabelle ist also bekannt, sodass die Zeile direkt geladen werden kann. Bei einem sekundären Index sieht das jedoch anders aus. Er speichert nicht die physische Position (ROWID) der Zeile, sondern den Schlüssel für den Clustered-Index – den sogenannten Clustering-Schlüssel. Das ist meist der Primärschlüssel der Index-organisierten Tabelle.

Der Zugriff auf einen Sekundär-Index liefert also keine ROWID, sondern den Schlüssel für den Zugriff auf den Clustered-Index. Das heißt, dass ein Tabellenzugriff über einen sekundären Index zwei Indizes durchsucht. Einmal den sekundären Index (INDEX RANGE SCAN) und dann den Clustered-Index für jede Zeile (INDEX UNIQUE SCAN).

Abbildung 5.3 Sekundärer Indexzugriff auf eine IOT

In Abbildung 5.3 wird deutlich, dass der Indexbaum des Clustered-Indexes zwischen dem Sekundär-Index und den Tabellendaten steht.

Der Zugriff auf eine Index-organisierte Tabelle über einen sekundären Index ist also sehr ineffizient. Man kann ihn jedoch genauso vermeiden, wie man den Tabellenzugriff bei einer Heap-Tabelle vermeiden kann: mit ei­nem Index-Only-Scan – in diesem Fall vielleicht besser als „Only-Secondary-Index-Scan“ umschrieben. Diese Methode ist bei einem sekundären Index sogar noch gewinnbringender, da nicht nur ein Zugriff pro Zeile, sondern ein ganzer INDEX UNIQUE SCAN eingespart wird.

Anhand dieser Idee kann man wieder einmal zeigen, dass Datenbanken sämtliche Redundanzen ausnutzen. Konkret ist es der Clustering-Schlüssel, der in jeden sekundären Index automatisch aufgenommen wird. Daraus folgt, dass man den Clustering-Schlüssel direkt aus dem sekundären Index beziehen kann:

SELECT sale_id
  FROM sales_iot
 WHERE sale_date = ?

-------------------------------------------------
| Id | Operation        | Name           | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT |                |    4 |
|* 1 |  INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)

Die Tabelle SALES_IOT ist eine Index-organisierte Tabelle mit der Spalte SALE_ID als Clustering-Schlüssel. Obwohl der Index SALES_IOT_DATE an sich nur die Spalte SALE_DATE beinhaltet, kann der Clustering-Schlüssel – die Spalte SALE_ID – direkt aus dem sekundären Index geliefert werden.

Wenn andere Spalten selektiert werden, muss zusätzlich ein INDEX UNIQUE SCAN auf dem Clustered-Index durchgeführt werden:

SELECT eur_value
  FROM sales_iot
 WHERE sale_date = ?

---------------------------------------------------
| Id  | Operation         | Name           | Cost |
---------------------------------------------------
|   0 | SELECT STATEMENT  |                |   13 |
|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK   |   13 |
|*  2 |   INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)
   2 - access("SALE_DATE"=:DT)

Zusammengefasst sind Index-organisierte Tabellen oder Clustered-Indizes nicht so nützlich, wie es auf den ersten Blick scheint. Der Performancegewinn auf dem Clustered-Index geht bei Zugriffen über sekundäre Indizes schnell verloren. Der Clustering-Schlüssel ist häufig länger als eine ROWID, sodass die sekundären Indizes oft größer sind als ein entsprechender Index auf einer Heap-Tabelle. Dadurch wird die Platzersparnis ebenfalls zunichtegemacht. Die Stärke einer Index-organisierten Tabelle oder eines Clustered-Indexes liegt vor allem bei Tabellen, die keinen sekundären Index benötigen. Heap-Tabellen haben hingegen den Vorteil, dass sie ein stationäres Original zur Verfügung stellen, auf das mehrere Indizes einfach verweisen können.

Die Unterstützung von Index-organisierten Tabellen und Clustered-Indizes ist von Datenbank zu Datenbank sehr unterschiedlich. Die folgende Übersicht erklärt die wichtigsten Besonderheiten.