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.

Der SQL-Standard hat diese Notwendigkeit lange ignoriert. Die ent­sprech­ende Erweiterung (fetch first) wurde erst mit SQL:2008 eingeführt und ist in IBM Db2, PostgreSQL, SQL Server (ab 2012) und Oracle (ab 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.

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

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

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

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.

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.

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.

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.