ORDER BY indizieren

SQL-Abfragen mit einer order by-Klausel müssen das Ergebnis nicht extra sortieren, wenn der verwendete Index die Einträge bereits in der ge­wünschten Reihenfolge liefert. Das heißt, derselbe Index, der die where-Klausel abdeckt, muss auch die order by-Klausel berücksichtigen.

Als Beispiel betrachten wir die folgende Abfrage, die alle Verkäufe von gestern nach Datum und Produkt sortiert liefert:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

Da es bereits einen Index auf der Spalte SALE_DATE gibt, wird dieser für die where-Klausel benutzt. Für die order by-Klausel muss aber eine explizite Sortierung durchgeführt werden:

Ein INDEX RANGE SCAN liefert das Ergebnis aber ohnehin in der Indexrei­henfolge. Damit die Sortieroperation entfallen kann, muss man den Index nur so erweitern, dass die Indexreihenfolge der order by-Klausel entspricht.

  DROP INDEX sales_date

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id)

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

Die Sortieroperation SORT ORDER BY ist aus dem Ausführungsplan ver­schwun­den, obwohl die order by-Klausel nach wie vor in der SQL-Anwei­sung steht. Die Datenbank nutzt also die Indexreihenfolge aus und führt daher keine explizite Sortierung durch.

Doch obwohl der Ausführungsplan einfacher geworden ist, ist der Cost-Wert stark gestiegen. Das liegt daran, dass der neue Index einen schlech­teren Clustering-Faktor hat (siehe „Automatisch optimierter Clustering-Faktor“). An dieser Stelle sei nur angemerkt, dass der Cost-Wert den Ausführungsaufwand nicht immer akkurat widerspiegelt.

Für diese Optimierung ist es ausreichend, dass der durchsuchte Index­bereich entsprechend der order by-Klausel sortiert ist. Im konkreten Beispiel funktioniert die Optimierung also auch, wenn das Ergebnis aus­schließlich nach PRODUCT_ID sortiert werden soll.

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

Abbildung 6.1 zeigt, dass die PRODUCT_ID das einzig relevante Sortierkrite­rium im durchsuchten Indexbereich ist. Daher entspricht die Indexreihen­folge in diesem Bereich der order by-Klausel, sodass die explizite Sortierung entfallen kann.

Abbildung 6.1 Indexreihenfolge im relevanten Bereich

Das kann in weiterer Folge zu Überraschungen führen, wenn der durch­suchte Indexbereich erweitert wird:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

Diese Abfrage liefert nicht nur die Verkäufe von gestern, sondern seit ges­tern. Sie betrifft also mehrere Tage beziehungsweise einen Indexbereich, der nicht ausschließlich nach PRODUCT_ID sortiert ist. In Abbildung 6.1 ist ersichtlich, dass eine Ausweitung des durchsuchten Indexbereiches nach unten wieder Einträge mit kleineren PRODUCT_ID-Werten liefert. Da das Ergebnis aber ausschließlich nach PRODUCT_ID sortiert sein soll, muss die Datenbank eine explizite Sortierung durchführen.

Wenn die Datenbank wider Erwarten eine explizite Sortierung durchführt, kann das zwei Ursachen haben: (1) Die Datenbank bewertet den Ausfüh­rungsplan mit expliziter Sortierung besser (kleinerer Cost-Wert); (2) Die Indexreihenfolge stimmt im entsprechenden Bereich nicht mit der order by-Klausel überein.

Eine einfache Methode herauszufinden, welcher Fall zutrifft, ist die vollständige Indexdefinition in die order by-Klausel aufzunehmen. Man passt also die Abfrage an die Indexreihenfolge an und schließt damit die zweite Ursache aus. Wenn die Datenbank dann noch immer eine explizite Sortierung durchführt, bevorzugt der Optimizer diesen Ausführungsplan aufgrund des Cost-Wertes. Wenn die Ausführung jedoch ohne Sortier­operation erfolgt, kann der Index nicht für die ursprüngliche order by-Klausel verwendet werden.

In beiden Fällen stellt sich die Frage, ob und wie man eine pipelined order by-Ausführung dennoch erreichen kann. Dazu kann man den Trick mit der vollen Indexdefinition in der order by-Klausel fortführen. Wenn man diese erweiterte Abfrage ausführt, erhält man das Ergebnis in der In­dexreihenfolge angezeigt. Meist erkennt man daran, dass man eine falsche Vorstellung des Indexes hat und die Indexreihenfolge im relevanten Bereich tatsächlich nicht der ursprünglichen order by-Klausel entspricht. Der Index kann also nicht für die ursprüngliche order by-Klausel verwendet werden.

Wenn der Optimizer die explizite Sortierung nur aufgrund des Cost-Wertes auswählt, liegt das meist daran, dass er den besten Ausführungsplan für die vollständige Ausführung der Abfrage wählt. Anders ausgedrückt, er wählt jenen Ausführungsplan, der die letzte Zeile (voraussichtlich) am schnellsten liefert. Wenn die Datenbank jedoch erkennt, dass nur ein Teil des Ergebnisses benötigt wird, kann sie wiederum den Ausführungsplan ohne explizite Sortierung bevorzugen. Kapitel 7, „Teilergebnisse“, zeigt entsprechende Optimierungsmethoden auf.