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 DESCDie 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 <= 20nicht 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)
Seit SQL-92 hat sich einiges getan!