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
).
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.
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.
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 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 verwendet bei der MyISAM-Engine nur Heap-Tabellen, mit InnoDB aber nur Clustered-Indizes. Das bedeutet, dass man keine direkte Wahlmöglichkeit hat.
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 verwendet nur Heap-Tabellen.
Es gibt jedoch die Möglichkeit, den Tabelleninhalt nach einem Index auszurichten (CLUSTER
-Klausel).
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.
Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Bluesky oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.
Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how