von Markus Winand.

Mit Window-Funktionen blättern


Neben den zuvor gezeigten Offset-Varianten mit fetch first oder daten­bank­spezifischen 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 ge­nutzt 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 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:

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.

MariaDBMySQLOracle DBPostgreSQLSQL Server200520072009201120132015201720192021⚠ 2008R2 - 2022a⚠ 15b⊘ 9.0 - 14⚠ 12cR1 - 21cb⚠ 11gR1 - 11gR2⊘ 8.0.18 - 8.0.32⊘ 10.2 - 10.11
  1. Nur mit row_number()
  2. 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.

MariaDBMySQLOracle DBaPostgreSQLSQL Server ROW_NUMBER() RANK() DENSE_RANK() COUNT(…)
  1. 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.

MariaDBMySQLOracle DBaPostgreSQLSQL Server OVER(ORDER BY…) OVER(ORDER BY…) + WHERE x=?OVER(PARTITION BY x ORDER BY…) + WHERE x=?
  1. 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.

Über den Autor

Foto von Markus Winand

Markus Winand ist der SQL Renaissance Botschafter auf der Mission, Entwickler auf die Evolution von SQL im 21. Jahrhundert aufmerksam zu machen. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Fußnoten

  1. MySQL unterstüzt Window-Funktionen erst seit Version 8.0, MariaDB seit Version 10.2.

Nicht mit OFFSET blättern

Mehr info

Besuche meine Schwester-Seite!Seit SQL-92 hat sich einiges getan!

Die Use The Index, Luke! Tasse

Aufkleber, Bierdeckel, Bücher und Kaffeetassen. Alles was man beim Lernen braucht!

Zum Shop

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz