Oracle DB – Fallstricke für Entwickler: Zeilenausgabe limitieren

Als Softwareentwickler stolpern wir immer wieder über Applikationsfehler, deren Ursache in der Datenbankschicht liegen. Oft sind es sehr banale Fehler, die sich bereits mit geringen Grundkenntnissen über die Datenbank leicht vermeiden lassen.
Daher möchte ich in meinen Beiträgen alltägliche Fallstricke aufzeigen, über die Softwareentwickler nur allzu gerne stolpern.

In diesem Beitrag soll es um die Limitierung von Zeilenausgaben gehen. Viele RDBM-Systeme (relationale Datenbankmanagementsysteme) erlauben bereits mit einfacher SQL Syntax die Limitierung der Ergebnismenge. Bei Oracle erfolgt dies mit Hilfe eines Tricks, und genau hier verbirgt sich der erste Fallstrick…

Die Aufgabe:
Die Geschäftsführung unseres fiktiven Unternehmens „ClearCode GmbH“ möchte im Rahmen der alljährlichen Weihnachtsfeier die vermeidlich produktivsten Softwareentwickler küren. Hierfür sollen die drei Entwickler mit den meisten geschriebenen Codezeilen ermittelt werden.
Die Datenbank Tabelle T_DEV_PRODUCTIVITY enthält bereits alle Entwickler mit deren Anzahl programmierten Codezeilen

SELECT DEV_PRODUCTIVITY_ID,
   DEVELOPER_NAME,
   TO_CHAR(LINES_OF_CODE,'999G999') LINES_OF_CODE
FROM T_DEV_PRODUCTIVITY;

Im ersten Schritt verwenden wir nun die für Oracle übliche vorgehensweiße zur Limitierung der Zeilen mittels der Pseudospalte ROWNUM.
Die Oracle Dokumentation beschreibt die Funktion der Spalte wie folgt:
“For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 11;”
(siehe ROWNUM Pseudocolumn – Oracle Documentation)

Somit gibt folgende Abfrage nur die ersten 3 Zeilen der ermittelten Treffer zurück:

SELECT 
   DEV_PRODUCTIVITY_ID,
   DEVELOPER_NAME,
   TO_CHAR(LINES_OF_CODE,'999G999') LINES_OF_CODE,
   ROWNUM
FROM T_DEV_PRODUCTIVITY
WHERE ROWNUM < = 3
;

Um nun die drei Entwickler mit den meist geschriebenen Codezeilen zu ermitteln, verändern wir nun die Reihenfolge der Ausgabe. Wir beginnen mit der höchsten Anzahl an produzierten Codezeilen. Dies wird mittels ORDER BY in absteigender (DESC) Reihenfolge der Spalte LINES_OF_CODE umgesetzt:

SELECT 
   DEV_PRODUCTIVITY_ID,
   DEVELOPER_NAME,
   TO_CHAR(LINES_OF_CODE,'999G999') LINES_OF_CODE,
   ROWNUM
FROM T_DEV_PRODUCTIVITY
WHERE ROWNUM < = 3
ORDER BY LINES_OF_CODE DESC;

Diese Abfrage liefert nun folgendes Ergebnis:

Wie wir sehen können wird nun die Ausgabe in absteigender Reihenfolge der programmierten Codezeilen sortiert. Auch die Einschränkung auf drei Entwickler ist weiterhin gegeben.
Doch halt, irgendetwas passt hier nicht. Betrachten wir hier im Vergleich die Einträge in der Tabelle T_DEV_PRODUCTIVITY. Sollten hier nicht die Entwickler „Hugo Hacker“, "Tim Trainee“ und „Debbie Debug“ angezeigt werden? Wieso erhalten wir hier ein falsches Ergebnis?
Um die besinnliche Weihnachtsfeier nicht zu gefährden sollten wir uns unser Statement noch einmal genauer ansehen.
Hier hat uns die Datenbank einen Streich gespielt, da wir ein wichtiges Detail übersehen haben: Die Sortierung der ermittelten Zeilen mittels ORDER BY erfolgt erst NACHDEM das komplette Ergebnis des SELECT Statements, inklusive der Vergabe der ROWNUM-Werte, generiert wurde. Somit erhält der erste Datensatz weiterhin die ROWNUM 1, der zweite Datensatz die ROWNUM 2, … und erst im Anschluss erfolgt die Sortierung dieser ermittelten Zeilen mittels ORDER BY.

Um dieses Problem zu umgehen muss die Sortierung mittels ORDER BY vor der Vergabe der ROWNUM geschehen. Dies lässt sich wie folgt formulieren:

SELECT dev.*, ROWNUM
FROM
   (SELECT 
      DEV_PRODUCTIVITY_ID,
      DEVELOPER_NAME,
      TO_CHAR(LINES_OF_CODE,'999G999') LINES_OF_CODE,
      ROWNUM inner_rownum
   FROM T_DEV_PRODUCTIVITY
   ORDER BY LINES_OF_CODE DESC
   ) dev
WHERE ROWNUM < =3;

Hiermit erhalten wir nun folgendes Ergebnis:

Nun konnten wir tatsächlich die drei Entwickler mit den meisten Zeilen an erstelltem Code ermitteln und den Frieden der Weihnachtsfeier damit retten.
Die Verschachtelung der SELECT-Abfragen lässt sich hier leider nicht vermeiden. Hier bietet Oracle zur Limitierung von Zeilen keine LIMIT-OFFSET-Klausel an, wie es bei anderen Datenbanken der Fall ist. Daher muss diese etwas umständliche Formulierung mit der Verschachtelung gewählt werden.
Erst mit Oracle 12c ist es möglich, die neu eingeführte row_limiting_clause (siehe auch Database SQL Language Reference) zu verwenden. Diese sähe dann für unser Beispiel wie folgt aus:

SELECT 
   DEV_PRODUCTIVITY_ID, 
   DEVELOPER_NAME, 
   TO_CHAR(LINES_OF_CODE,'999G999') LINES_OF_CODE 
FROM T_DEV_PRODUCTIVITY 
ORDER BY LINES_OF_CODE DESC 
FETCH FIRST 3 ROWS ONLY; 

Wem das jetzt noch nicht genug war, dem sei der Artikel “On ROWNUM and Limiting Results” von Tom Kyte empfohlen.
http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Ausblick:
Die Limitierung ist eine von mehreren Möglichkeiten die gegebene Aufgabenstellung zu lösen. Eine etwas „fortgeschrittenere“-Variante zur Ermittlung der Entwickler mit der höchsten Anzahl an programmiertem Code wäre mittels der Oracle Funktion RANK zu realisieren. Doch dazu mehr in einem anderen Blogbeitrag.