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, der Oracle Datenbank und PostgreSQL 15+ für eine pipelined Top-N-Abfrage genutzt werden kann. DB2 (LUW), MySQL und MariaDB0 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 biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
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)
- PostgreSQL
Seit Version 15 scheint im Ausführungsplan die „Run Condition“ auf, die die Ausführung der darunterliegenden Knoten abbrechen kann.
QUERY PLAN ------------------------------------------------------- Subquery Scan on tmp (cost=0.42..141724.98 rows=334751 width=249) (actual time=0.040..0.052 rows=10 loops=1) Filter: (tmp.rn >= 11) Rows Removed by Filter: 10 Buffers: shared hit=5 -> WindowAgg (cost=0.42..129171.80 rows=1004254 width=249) (actual time=0.028..0.049 rows=20 loops=1) Run Condition: (row_number() OVER (?) <= 20) Buffers: shared hit=5 -> Index Scan Backward using sl_dtid on sales (cost=0.42..111597.36 rows=1004254 width=241) (actual time=0.018..0.025 rows=22 loops=1) Buffers: shared hit=5
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 Unterstützung dieser Optimierung ist leider nicht sehr weit verbreitet.
- Nur mit
row_number()
- Nicht mit einer
Partition by
-Klausel (siehe unten)
Obwohl diese Optimierung konzeptionell mit allen monotonen Funktionen funktioniert, konzentrieren sich die Hersteller vor allem auf die Funktion ROW_NUMBER
.
- Nur mit einer
Where
-Klausel:OVER(ORDER BY…)
+WHERE x=?
Vorsicht ist bei der Partition by
-Klausel geboten: Selbst wenn eine Where
-Klausel auf eine einzelne Partition einschränkt, deaktiviert das vorhandensein einer Paritition by
-Klausel diese Optimierung in einigen Produkten. Das kann zum Beispiel dann passieren, wenn die partitionierte Window-Funktion in einem View verwendet wird, die äußere Abfrage aber auf eine einzelne Partition eingrenzt.
- Nicht alle Funktionen, die mit einer
Where
-Klausel funktonieren
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
SQL Server: OVER-Klausel in SQL Server