Neben den zuvor gezeigten Offset-Varianten mit fetch first
oder datenbankspezifischen Erweiterungen kann man eine Blätterabfrage auch mit Window-Funktionen umsetzten. Damit steht eine flexible und vor allem auch standardkonforme Variante zur Verfügung, die aber leider nur von SQL Server und der Oracle Datenbank für eine pipelined Top-N-Abfrage genutzt werden kann. MySQL0 und PostgreSQL brechen den Index-Zugriff nicht ab, sobald genügend Ergebnisse gefunden wurden – die Ausführung ist also sehr ineffizient.
Das folgende Beispiel zeigt verwendet eine Window-Funktion zum Blättern:
SELECT *
FROM ( SELECT sales.*
, ROW_NUMBER() OVER (ORDER BY sale_date DESC
, sale_id DESC) rn
FROM sales
) tmp
WHERE rn between 11 and 20
ORDER BY sale_date DESC, sale_id DESC
Die Window-Funktion ROW_NUMBER
liefert die Zeilennummer entsprechend der in der over
-Klausel angegebenen Reihenfolge. Die Einschränkung auf die Zeilen der zweiten Seite (Zeilen 11 bis 20) erfolgt in der where
-Klausel der äußeren Abfrage.
Hinweis in eigener Sache
Ich lebe von SQL-Schulungen, anderen SQL-Dienstleistungen und dem Verkauf meines Buches. Mehr dazu auf winand.at.
Die Oracle Datenbank erkennt die Abbruchbedingung und verwendet den Index auf SALE_DATE
und SALE_ID
für eine pipelined Top-N-Ausführung:
- DB2
Explain Plan ------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 65658 2 | FILTER | 100933 of 1009326 | 65658 3 | FETCH SALES | 1009326 of 1009326 | 65295 4 | IXSCAN (REVERSE) SL_DTID | 1009326 of 1009326 | 5679 Predicate Information 2 - RESID (11 <= Q3.$C8) RESID (Q3.$C8 <= 20)
Hierbei ist zu beachten, dass DB2 LUW 10.5 diese Abfrage nicht als Top-N-Abfrage ausführt. Obwohl die Sortierung vermieden wird, wird der ganze Index gelesen – die Ausführung wird nach Zeile 20 nicht abgebrochen.
Um einen Top-N-Abbruch nach 20 Zeilen zu erhalten, muss man die Abfrage doppelt verschachteln: zuerst um einen Top-N-Abbruch nach 20 Zeilen zu erreichen, dann einen Filter um die ersten 10 Zeilen zu entefernen.
SELECT * FROM (SELECT * FROM (SELECT sales.* , ROW_NUMBER() OVER (ORDER BY sale_date DESC , sale_id DESC) rn FROM sales ) tmp WHERE rn <= 20 ) tmp2 WHERE rn > 10 ORDER BY sale_date DESC, sale_id DESC;
Explain Plan ----------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 21 2 | FILTER | 7 of 20 | 21 3 | FETCH SALES | 20 of 1009326 | 65352 4 | IXSCAN (REVERSE) SALES_DT_ID | 1009326 of 1009326 | 5736 Predicate Information 2 - RESID (10 < Q3.$C8)
Man beachte, dass der Filter
rn <= 20
nicht in den Prädikat-Informationen aufscheint, die Zeilenschätzung die Auswirkung des Filters sehr wohl anzeigt.- Oracle
--------------------------------------------------------------- |Id | Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1004K| 36877 | |*1 | VIEW | | 1004K| 36877 | |*2 | WINDOW NOSORT STOPKEY | | 1004K| 36877 | | 3 | TABLE ACCESS BY INDEX ROWID | SALES | 1004K| 36877 | | 4 | INDEX FULL SCAN DESCENDING | SL_DTID | 1004K| 2955 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RN">=11 AND "RN"<=20) 2 - filter(ROW_NUMBER() OVER ( ORDER BY "SALE_DATE" DESC, "SALE_ID" DESC )<=20)
Die Operation WINDOW NOSORT STOPKEY
zeigt, dass keine explizite Sortierung durchgeführt wird (NOSORT
) und dass die Datenbank die Verarbeitung nach Erreichen des Schwellwertes abbricht (STOPKEY
). Die Ausführung erfolgt also „am Fließband“ und ist genauso effizient wie die Offset-Methode aus dem vorherigen Abschnitt.
Die Stärke der Window-Funktionen liegt jedoch nicht beim Blättern, sondern bei analytischen Aufgaben. Wenn man noch nie mit Window-Funktionen gearbeitet hat, sollte man sich unbedingt ein paar Stunden Zeit nehmen, sich in das Thema einzuarbeiten.
Links
Oracle: Analytic Functions in 11g
PostgreSQL: Window Functions in 9.6
SQL Server: OVER-Klausel in 2008R2 und 2012 (erweitert)