Mit Window-Funktionen blättern


Gilt für
DB2Nein
MySQLNein
OracleJa
PostgreSQLNein
SQL ServerJa

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 und der Oracle Datenbank für eine pipelined Top-N-Abfrage ge­nutzt werden kann. Die PostgreSQL Datenbank bricht den Index-Zugriff nicht ab, sobald genügend Ergebnisse gefunden wurden – die Ausführung ist also sehr ineffizient. MySQL unterstützt Window-Funktionen überhaupt nicht.

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.

Für alle Anwendungsentwickler […] sollte der schmale Band […] eine Pflichtlektüre sein — ADMIN-Magazin

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)

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.

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.