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 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:

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 Server20072009201120132015201720192021⚠ 2008R2 - 2022a⚠ 15b⊘ 9.0 - 14⚠ 12cR1 - 21cb⚠ 11gR1 - 11gR2⊘ 8.0.18 - 8.0.34⊘ 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.

Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. 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.

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