ORDER BY indizieren


Gilt für
DB2Ja
MySQLJa
OracleJa
PostgreSQLJa
SQL ServerJa

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:

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 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
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 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.

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üh­rungsplan mit expliziter Sortierung besser (kleinerer Cost-Wert); (2) Die Indexreihenfolge stimmt im entsprechenden Bereich nicht mit der order by-Klausel überein.

Die passende Tasse zu dieser Website findest du in unserem Shop.
Sieht gut aus und unterstützt meine Arbeit hier.

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.

Tweet this tip

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, zeigt entsprechende Optimierungsmethoden auf.

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.