Index-Only-Scan

Bei einem Index-Only-Scan wird nicht nur der Tabellenzugriff für die where-Klausel vermieden. Der Tabellenzugriff kann vollständig vermieden werden, wenn die Abfrage die gesuchten Spalten direkt aus dem Index entnehmen kann.

Für einen Index-Only-Scan müssen daher alle Spalten der Abfrage im Index enthalten sein – insbesondere auch die Spalten der select-Klausel, wie im folgenden Beispiel:

CREATE INDEX sales_sub_eur
    ON sales
     ( subsidiary_id, eur_value )

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?

Dabei hat die Indizierung der where-Klausel natürlich Vorrang gegenüber anderen Klauseln. Die Spalte SUBSIDIARY_ID muss also an erster Stelle stehen, damit sie für das Zugriffsprädikat genutzt werden kann.

Der Ausführungsplan zeigt den Indexzugriff, allerdings ohne darauffol­genden Tabellenzugriff (TABLE ACCESS BY INDEX ROWID).

Der Index deckt also die gesamte Abfrage ab. In diesem Fall kann man einen Index auch als „Covering Index“ bezeichnen.

Da der Index eine Kopie der Spalte EUR_VALUE beinhaltet, kann die Daten­bank direkt mit dem Wert aus dem Index arbeiten. Der Tabellenzugriff ist also nicht mehr nötig, da alle Informationen zum Beantworten der Abfrage im Index gespeichert sind.

Ein Index-Only-Scan kann einen enormen Geschwindigkeitsvorteil bringen. Dazu muss man sich nur vor Augen führen, dass die Abfrage circa vierzig­tausend Zeilen aufsummiert, wie in der Rows-Spalte des Ausführungsplanes zu sehen ist. Es werden also bis zu vierzigtausend Tabellenzugriffe ver­mieden. Allerdings nur, wenn jede Zeile in einem anderen Tabellenblock gespeichert ist. Wenn der Clustering-Faktor des Indexes aber gut ist, wenn die entsprechenden Zeilen also auch in der Tabelle nahe beieinander liegen, kann der Vorteil deutlich geringer ausfallen.

Neben dem Clustering-Faktor wird der Performancevorteil eines Index-Only-Scans auch durch die Zahl der betroffenen Zeilen beschränkt. Wenn zum Beispiel nur eine einzelne Zeile gelesen wird, kann auch nur ein Tabellenzugriff eingespart werden. Das Durchwandern des Indexbaumes alleine benötigt aber schon einige Lesezugriffe. Einen Tabellenzugriff zu sparen bringt also verhältnismäßig wenig.

Mit einem Index-Only-Scan kann man aber auch unangenehme Überra­schungen erleben. Wenn man die Abfrage zum Beispiel auf aktuelle Verkäufe einschränkt:

SELECT SUM(eur_value)
  FROM sales
 WHERE subsidiary_id = ?
   AND sale_date > ?

Ohne den Ausführungsplan zu betrachten, könnte man erwarten, dass die Abfrage schneller wird. Schließlich werden weniger Zeilen abfragt. Da die Abfrage aber auf eine Spalte zugreift, die nicht im Index ist, muss nun ein Tabellenzugriff durchgeführt werden:

Durch den Tabellenzugriff wird die Abfrage langsamer – obwohl weniger Zeilen benötigt werden. Der relevante Faktor ist nicht, wie viele Zeilen benötigt werden, sondern wie viele die Datenbank laden muss, um die gesuchten zu finden.

Wenn ein Index durch die Änderung einer Abfrage nicht mehr für einen Index-Only-Scan verwendet werden kann, wählt der Optimizer den nächst­besten Ausführungsplan. Das heißt, es kann auch ein völlig anderer Index verwendet werden. Das ist oben auch geschehen. Der neue Ausführungs­plan verwendet einen Index auf SALE_DATE – ein Relikt aus dem Join-Kapitel.

Gegenüber dem vorherigen Index hat dieser, aus Sicht des Optimizers, zwei Vorteile. Der Optimizer glaubt, dass die Bedingung auf der Spalte SALE_DATE selektiver ist als jene auf der SUBSIDIARY_ID-Spalte. Das erkennt man am jeweiligen Rows-Wert für den INDEX RANGE SCAN (circa 10.000 versus 40.000). Diese Schätzungen sind jedoch völlig willkürlich, da Bind-Para­meter verwendet werden. Der Filter auf SALE_DATE könnte, zum Beispiel, alle Zeilen liefern, wenn ein entsprechend frühes Datum angegeben wird.

Der zweite Vorteil des SALES_DATE-Indexes ist, dass er einen besseren Clustering-Faktor hat. Dieser Vorteil ist stichhaltig, weil die Tabelle SALES nur chronologisch wächst. Da keine Zeilen gelöscht werden, werden neue Einträge immer an das hintere Ende der Tabelle geschrieben. Sie ist also grob chronologisch sortiert – ebenso wie der Index. Die Indexreihenfolge stimmt gut mit der Tabellenreihenfolge überein – der Index hat einen guten Clustering-Faktor.

Bei einem Indexzugriff mit gutem Clustering-Faktor liegen die Tabellen­zeilen nahe beieinander, sodass nur wenige Tabellenblöcke gelesen werden müssen, um alle Zeilen zu laden. Die Abfrage könnte mit diesem Index also auch ohne Index-Only-Scan schnell genug sein. Dann sollte man die nicht mehr benötigten Spalten aus dem alten Index löschen.

Bei diesem Beispiel ist es zu einem glücklichen Zufall gekommen. Die Erweiterung der where-Klausel hat nicht nur den Index-Only-Scan unter­bunden, sondern gleichzeitig einen neuen Zugriffsweg geöffnet. Dadurch konnte der Optimizer die Auswirkung begrenzen. Ein Index-Only-Scan kann aber auch durch neue Spalten in anderen Klauseln unterbunden werden. Es genügt, eine weitere Spalte in die select-Klausel aufzunehmen. Dadurch kann man aber keinen neuen Zugriffsweg öffnen, der die Auswirkung begrenzen könnte.

Funktions-basierende Indizes können im Zusammenhang mit Index-Only-Scans ebenfalls zu Überraschungen führen. Wenn man die Spalte LAST_NAME selektiert, hilft es nicht, den Ausdruck UPPER(last_name) zu indizieren. Daher hätte man im vorherigen Abschnitt die Spalte LAST_NAME selbst indizieren sollen, damit sie im Rahmen eines Index-Only-Scans auch für die select-Klausel benutzt werden kann.

Aggregierende Abfragen wie oben sind gute Kandidaten für einen Index-Only-Scan. Solche Abfragen greifen meist auf viele Zeilen, aber nur wenige Spalten zu. Dadurch genügt ein verhältnismäßig schmaler Index für einen Index-Only-Scan. Je mehr Spalten abgefragt werden, desto mehr Spalten muss man auch in den Index aufnehmen, damit ein Index-Only-Scan durchgeführt werden kann. Als Entwickler sollte man daher nur auf die unbedingt notwendigen Spalten zugreifen.

 CREATE INDEX empsubupnam
     ON employees
       (subsidiary_id, last_name)
INCLUDE(phone_number, first_name)