von Markus Winand.

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.

Wichtig

Wenn die Indexreihenfolge der order by-Klausel entspricht, kann die explizite Sortierung entfallen.

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.

Automatisch optimierter Clustering-Faktor

Die Oracle Datenbank hält den Index-Clustering-Faktor klein, indem sie die ROWID bei der Indexreihenfolge mitberücksichtigt. Wenn also zwei Indexeinträge dieselben Schlüsselwerte haben, entscheidet die ROWID über die endgültige Reihenfolge. Da die ROWID die physische Adresse der Tabellenzeile widerspiegelt, ist der Index damit auch nach der Tabellenreihenfolge sortiert, sodass er automatisch den kleinst­möglichen Clustering-Faktor hat.

Wenn man einen Index um eine Spalte erweitert, fügt man damit ein zusätzliches Sortierkriterium vor der ROWID ein. Die Datenbank hat also weniger Freiraum, die Indexeinträge entsprechend der Tabellen­reihenfolge anzuordnen. Der Index-Clustering-Faktor kann durch zusätzliche Spalten also nur schlechter werden.

Das ändert aber nichts daran, dass die Indexreihenfolge weiterhin grob mit der Tabellenreihenfolge übereinstimmen kann. Das heißt, die Einträge für einen Tag sind sowohl im Index als auch in der Tabelle jeweils eng beieinander abgelegt – wenn auch nicht mehr in der exakt selben Reihenfolge. Bei einem Zugriff über den SALES_DT_PR-Index werden die Tabellenblöcke zwar mehrfach gelesen, aber eben nur dieselben Tabellenblöcke wie zuvor. Durch das Caching der Datenbank kann der Performanceunterschied daher deutlich geringer ausfallen, als es der Cost-Wert vermuten lässt.

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.

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.

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.

Tipp

Verwende die volle Indexdefinition in der order by-Klausel, um den Grund für eine explizite Sortierung zu finden.

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.

Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz