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 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, SQL-Tuning und Beratung sowie dem Verkauf meines Buches „SQL Performance Explained“. Mehr 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)- 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
