Ein Index-Only-Scan führt eine SQL-Anweisung ausschließlich unter Nutzung 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 Vordergrund 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 automatisch 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 Tabellendaten – 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.
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.
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 Speicherstelle 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 einem 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.
Wichtig
Ein Tabellenzugriff über einen sekundären Index ist sehr ineffizient.
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.
Wichtig
Tabellen, die nur einen Index haben, werden am besten als Clustered-Index oder Index-organisierte Tabelle umgesetzt.
Tabellen mit mehreren Indizes können von der Heap-Struktur
profitieren. Dennoch kann man die Indizes jeweils für einen
Index-Only-Scan auslegen, sodass der Zugriff auf die Heap-Struktur
entfällt. Damit erhält man die select
-Performance eines Clustered-Indexes,
ohne die Zugriffe über andere Indizes zu verlangsamen.
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.
- DB2
DB2 kennt keine Index-organisierte Tabellen, verwendet den Begriff „clustered index“ jedoch für eine andere Funktion. Dabei wird weiterhin eine Heap-Tabelle verwendet, die Datenbank versucht jedoch neue Einträge in denselben Tabellenblock zu legen, wie die Einträge die im Index nahe sind.
- MySQL
MySQL verwendet bei der MyISAM-Engine nur Heap-Tabellen, mit InnoDB aber nur Clustered-Indizes. Das bedeutet, dass man keine direkte Wahlmöglichkeit hat.
- Oracle
Die Oracle Datenbank verwendet per Default Heap-Tabellen. Einzelne Tabellen können durch den Zusatz
ORGANIZATION INDEX
auch als Index-organisierte Tabelle angelegt werden:CREATE TABLE ( id NUMBER NOT NULL PRIMARY KEY, [...] ) ORGANIZATION INDEX
Dabei wird immer der Primärschlüssel als Clustering-Schlüssel verwendet.
- PostgreSQL
PostgreSQL verwendet nur Heap-Tabellen.
Es gibt jedoch die Möglichkeit, den Tabelleninhalt nach einem Index auszurichten (
CLUSTER
-Klausel).- SQL Server
SQL Server verwendet per Default Clustered-Indizes (Index-organisierte Tabellen) mit dem Primärschlüssel als Clustering-Schlüssel. Man kann aber einen beliebigen Clustering-Schlüssel verwenden – sogar non-unique Spaltenkombinationen.
Zum Anlegen einer Heap-Tabelle muss man die
NONCLUSTERED
-Klausel bei der Definition des Primärschlüssels angeben:CREATE TABLE ( id NUMBER NOT NULL, [...] CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id) )
Löscht man einen Clustered-Index, wird die Tabelle in eine Heap-Tabelle umgebaut.
Das Standardverhalten von SQL Server führt häufig zu Performanceproblemen bei Zugriffen über einen Sekundär-Index.