SQL-Abfragen mit einer order by
-Klausel müssen das Ergebnis nicht extra sortieren, wenn der verwendete Index die Einträge bereits in der gewü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:
- DB2
Explain Plan ------------------------------------------------------------ ID | Operation | Rows | Cost 1 | RETURN | | 682 2 | TBSCAN | 394 of 394 (100.00%) | 682 3 | SORT | 394 of 394 (100.00%) | 682 4 | FETCH SALES | 394 of 394 (100.00%) | 682 5 | IXSCAN SALES_DATE | 394 of 1009326 ( .04%) | 19 Predicate Information 5 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS)) STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
Die
where
-Klausel wurde wie folgt an DB2 angepasst:WHERE sale_date > CURRENT_DATE - 1 DAY
.- Oracle
--------------------------------------------------------------- |Id | Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 320 | 18 | | 1 | SORT ORDER BY | | 320 | 18 | | 2 | TABLE ACCESS BY INDEX ROWID| SALES | 320 | 17 | |*3 | INDEX RANGE SCAN | SALES_DATE | 320 | 3 | ---------------------------------------------------------------
Ein INDEX RANGE SCAN
liefert das Ergebnis aber ohnehin in der Indexreihenfolge. 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
- DB2
Explain Plan ----------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 688 2 | FETCH SALES | 394 of 394 (100.00%) | 688 3 | IXSCAN SALES_DT_PR | 394 of 1009326 ( .04%) | 24 Predicate Information 3 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS)) STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
- Oracle
--------------------------------------------------------------- |Id | Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 320 | 300 | | 1 | TABLE ACCESS BY INDEX ROWID| SALES | 320 | 300 | |*2 | INDEX RANGE SCAN | SALES_DT_PR | 320 | 4 | ---------------------------------------------------------------
Die Sortieroperation SORT ORDER BY
ist aus dem Ausführungsplan verschwunden, obwohl die order by
-Klausel nach wie vor in der SQL-Anweisung 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 schlechteren 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 Indexbereich entsprechend der order by
-Klausel sortiert ist. Im konkreten Beispiel funktioniert die Optimierung also auch, wenn das Ergebnis ausschließ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 Sortierkriterium im durchsuchten Indexbereich ist. Daher entspricht die Indexreihenfolge 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 durchsuchte 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 gestern. 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.
- DB2
Explain Plan ------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 688 2 | TBSCAN | 394 of 394 (100.00%) | 688 3 | SORT | 394 of 394 (100.00%) | 688 4 | FETCH SALES | 394 of 394 (100.00%) | 688 5 | IXSCAN SALES_DT_PR | 394 of 1009326 ( .04%) | 24 Predicate Information 5 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)
- Oracle
--------------------------------------------------------------- |Id |Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 |SELECT STATEMENT | | 320 | 301 | | 1 | SORT ORDER BY | | 320 | 301 | | 2 | TABLE ACCESS BY INDEX ROWID| SALES | 320 | 300 | |*3 | INDEX RANGE SCAN | SALES_DT_PR | 320 | 4 | ---------------------------------------------------------------
Wenn die Datenbank wider Erwarten eine explizite Sortierung durchführt, kann das zwei Ursachen haben: (1) Die Datenbank bewertet den Ausführungsplan 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 Sortieroperation 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 Indexreihenfolge 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.