Top-N-Zeilen abfragen


Top-N-Abfragen sind Abfragen, die das Ergebnis auf eine bestimmte Zeilen­zahl beschränken. Das sind häufig Abfragen nach den aktuellsten oder „besten“ Einträgen. Für eine effiziente Ausführung dieser Abfragen muss das Ranking mittels pipelined order by durchgeführt werden.

Die einfachste Möglichkeit nur die ersten Zeilen einer Abfrage zu laden ist — nur die ersten Zeilen zu laden und die Ausführung durch die Ap­pli­ka­ti­on abzubrechen. Das kann der Optimizer allerdings nicht vorausahnen, wenn er den Ausführungsplan erstellt. Um den besten Aus­füh­rungs­plan auszuwählen, muss der Optimizer aber wissen, ob letztendlich alle Zeilen geladen werden. Schließlich kann in diesem Fall ein Full-Table-Scan mit expliziter Sortierung die beste Variante sein. Werden jedoch nur die ersten zehn Zeilen benötigt, ist ein pipelined order by oft besser – obwohl die Datenbank dabei jede Zeile einzeln lesen muss. Der Optimizer muss also von Anfang an wissen, ob alle Zeilen benötigt werden, um den besten Ausführungsplan zu erstellen.

Tweet this tip

Tipp

Sag es der Datenbank, wenn du nicht alle Zeilen brauchst.

Der SQL-Standard hat diese Notwendigkeit lange ignoriert. Die ent­sprech­ende Erweiterung (fetch first) wurde erst mit SQL:2008 eingeführt und ist nur in aktuellen Versionen von IBM DB2, PostgreSQL, SQL Server 2012 und Oracle 12c verfügbar. Das kommt einerseits daher, dass diese Erweiterung ein „non-core feature“ ist. Andererseits aber auch daher, dass die einzelnen Datenbanken eigene Lösungen anbieten, die seit vielen Jahren etabliert sind.

Die folgenden Beispiele zeigen die jeweilige Syntax, um die aktuellsten zehn Verkäufe aus der SALES-Tabelle abzufragen. Die Basis ist dabei immer dieselbe: die Auflistung aller Verkäufe beginnend mit dem neusten. Der jeweilige Top-N-Zusatz bricht die Ausführung nur ab, sobald zehn Zeilen geladen wurden.

DB2

DB2 unterstützt die fetch first-Syntax mindestens seit Version 9 (LUW und zOS).

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY

Das propreritäre limit-Schlüsselwort wird seit DB2 LUW 9.7 unterstützt (benötigt db2set DB2_COMPATIBILITY_VECTOR=MYS).

MySQL

Bei MySQL und PostgreSQL kann man die gewünschte Zeilenzahl durch die limit-Klausel beschränken.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10
Oracle

Die Oracle Datenbank unterstützt die fetch first Erweiterung erst seit Version 12c. Bei älteren Versionen muss man die Pseudo-Spalte ROWNUM verwenden, mit der jede Zeile nummeriert wird. Durch eine zusätzliche Ver­schach­telung kann man einen entsprechenden Filter formulieren.

SELECT *
  FROM (
       SELECT *
         FROM sales
        ORDER BY sale_date DESC
       )
 WHERE rownum <= 10
PostgreSQL

PostgreSQL unterstützt die fetch first-Erweiterung seit Version 8.4. Die zuvor verwendete limit-Syntax (analog zu MySQL) kann aber auch noch bei aktuellen Versionen verwendet werden.

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 FETCH FIRST 10 ROWS ONLY
SQL Server

Bei SQL Server kann man die Zeilenzahl durch den Zusatz TOP be­schrän­ken:

SELECT TOP 10 *
  FROM sales
 ORDER BY sale_date DESC

Ab Release 2012 setzt SQL Server die fetch first-Erweiterung um.

Das besondere an diesen SQL-Abfragen ist, dass die Datenbanken sie jeweils als Top-N-Abfragen erkennen.

Wichtig

Die Datenbank kann eine Abfrage nur dann für ein Teilergebnis optimieren, wenn sie das von Anfang an weiß.

Wenn die Datenbank weiß, dass nur zehn Zeilen geladen werden, kann sie ein pipelined order by bevorzugen.

DB2
Explain Plan
-----------------------------------------------------------------
ID | Operation                      |               Rows |   Cost
 1 | RETURN                         |                    |     24
 2 |  FETCH SALES                   |      10 of 1009326 | 458452
 3 |   IXSCAN (REVERSE) SALES_DT_PR | 1009326 of 1009326 |   2624

Predicate Information

Das Top-N-Verhalten kann man in einem DB2-Ausführungsplan nicht unmittelbar ablesen, wenn keine Sortieroperation notwendig ist (ansonsten zeigt es der last_explained-View bei der Sortieroperation in Klammern an: SORT (TOP-N) – siehe nächstes Beispiel)

In diesem konkreten Beispiel kann man aber vermuten, dass es sich um eine Top-N-Abfrage handelt, da es einen plötzlichen Abfall der Zeilenschätzung gibt, die nicht durch Filterprädikate zu erklären wäre (die Predicate Information-Sektion des Ausführungsplanes ist leer).

Oracle
-------------------------------------------------------------
| Operation                     | Name        | Rows | Cost |
-------------------------------------------------------------
| SELECT STATEMENT              |             |   10 |    9 |
|  COUNT STOPKEY                |             |      |      |
|   VIEW                        |             |   10 |    9 |
|    TABLE ACCESS BY INDEX ROWID| SALES       | 1004K|    9 |
|     INDEX FULL SCAN DESCENDING| SALES_DT_PR |   10 |    3 |
-------------------------------------------------------------

Der Ausführungsplan zeigt den geplanten Abbruch durch die Operation COUNT STOPKEY an. Das heißt, dass die Top-N-Abfrage erkannt wurde.

Tipp

Anhang A, „Ausführungspläne, fasst die entsprechenden Operationen für DB2, MySQL, Oracle, PostgreSQL und SQL Server zusammen.

Die korrekte Verwendung der entsprechenden Syntax ist aber erst die halbe Miete. Die Ausführung kann nur dann effizient abgebrochen werden, wenn die darunter liegenden Operationen „am Fließband“ ausgeführt werden. Das heißt, dass die order by-Klausel durch einen Index abgedeckt sein muss. Im konkreten Beispiel ist das der SALES_DT_PR-Index auf SALE_DATE, PRODUCT_ID. Dadurch entfällt die explizite Sortierung und die Datenbank kann die Zeilen direkt in der Indexreihenfolge ausgeben. Es werden also nur die Zeilen gelesen, die auch tatsächlich ausgegeben werden.

Wichtig

Eine Top-N-Abfrage, die „am Fließband“ ausgeführt wird, muss nicht alle Daten lesen und sortieren.

Wenn es keinen Index gibt, der für ein pipelined order by herangezogen werden kann, muss die Datenbank die Tabelle vollständig lesen und sortieren. Erst nachdem die letzte Zeile aus der Tabelle eingelesen wurde, kann das Ergebnis ausgegeben werden.

DB2
Explain Plan
-----------------------------------------------------------
ID | Operation       |                         Rows |  Cost
 1 | RETURN          |                              | 59835
 2 |  TBSCAN         |           10 of 10 (100.00%) | 59835
 3 |   SORT (TOP-N)  |      10 of 1009326 (   .00%) | 59835
 4 |    TBSCAN SALES | 1009326 of 1009326 (100.00%) | 59739

Predicate Information
Oracle
--------------------------------------------------
| Operation               | Name  | Rows |  Cost |
--------------------------------------------------
| SELECT STATEMENT        |       |   10 | 59558 |
|  COUNT STOPKEY          |       |      |       |
|   VIEW                  |       | 1004K| 59558 |
|    SORT ORDER BY STOPKEY|       | 1004K| 59558 |
|     TABLE ACCESS FULL   | SALES | 1004K|  9246 |
--------------------------------------------------

Dieser Ausführungsplan entspricht im Wesentlichen der Variante, dass die Abfrage von der Applikation abgebrochen wird. Dennoch ist die Verwendung der Top-N-Syntax etwas effizienter, da die Datenbank nicht das gesamte Ergebnis, sondern nur die zehn aktuellsten Einträge zwi­schenspeichern muss. Der Speicherbedarf ist also signifikant geringer. Im Ausführungsplan der Oracle Datenbank wird diese Optimierung durch den STOPKEY-Zusatz bei der Operation SORT ORDER BY angezeigt.

Die Stärke einer pipelined Top-N-Abfrage liegt aber nicht nur im unmit­telbaren Performancegewinn, sondern auch in der besseren Skalierung. Während die Antwortzeit einer Top-N-Abfrage ohne pipelined order by mit der Tabellengröße wächst, ist die Geschwindigkeit bei einer Ausführung „am Fließband“ nur von der Anzahl der selektierten Zeilen abhängig. Anders ausgedrückt ist eine pipelined Top-N-Abfrage immer gleich schnell – unabhängig von der Tabellengröße. Nur wenn die Tiefe des Indexbaumes wächst, wird die Ausführung geringfügig langsamer.

Abbildung 7.1 stellt das Performanceverhalten bei wachsender Datenmen­ge dar. Das lineare Wachstum der Antwortzeit mit steigender Datenmenge ist bei der Ausführung ohne pipelined order by deutlich zu erkennen. Bei einer Ausführung „am Fließband“ bleibt die Antwortzeit konstant.

Abbildung 7.1. Skalierung von Top-N-Abfragen


Die Ausführungszeit einer pipelined Top-N-Abfrage hängt zwar nicht von der Tabellengröße ab, steigt aber dennoch mit der Anzahl der selektie­rten Zeilen. Die Antwortzeit verdoppelt sich also, wenn man doppelt so viele Zeilen abfragt. Das betrifft insbesondere Blätter-Abfragen, die weitere Ergebnisse nachladen. Dabei müssen Einträge der ersten Seite überlesen werden, bevor die gesuchten Einträge der zweiten Seite kommen. Doch auch dafür gibt es Abhilfe, wie der nächste Abschnitt zeigt.

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.