Letzte Woche bin ich während eines Vortrages über folgenden Code-Schnipsel gestolpert:
select rownum ident
from dba_objects
where rownum <=500;
Mal eben ein paar Zeilen erzeugen ist eine Standardanforderung, der sich jeder SQL-Entwickler von Zeit zu Zeit stellen muss. Egal ob ein paar Demo-Zeilen erzeugt werden müssen oder für die Basis eines Outerjoins die vollständige Liste aller Tage in einem Zeitraum benötigt werden, Anwendungsmöglichkeiten gibt es viele.
Erschreckend häufig werden zu diesem Zweck Abfragen auf System-Views durchgeführt.
Schauen wir es uns einmal genauer an:
Dieses Statement liefert 500 Zeilen zurück. Das ist aber auch schon alles, was man ihm positives unterstellen kann.
Zunächst einmal wird hier auf eine DBA-View zugegriffen, auf die ein normales Schema ohne DBA-Berechtigungen gar keinen Zugriff hat. Das Verwenden von ALL_OBJECTS hilft dem zwar ab, aber kann man sich immer sicher sein, dass jeder Benutzer mehr als 500 Objekte im Zugriff hat?
Außerdem ist DBA_OBJECTS eine System-View, die auf verschiedene andere Views und Tabellen zugreift. Wenn man mal nachschaut, was man der Datenbank für diese einfache Aufgabe abverlangt, bekommt man das bestätigt:
select rownum id
from dba_objects
where rownum <=500;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | | 8 (13)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | DBA_OBJECTS | 500 | | 8 (13)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN | | 533 | 154K| 7 (15)| 00:00:01 |
| 6 | INDEX FULL SCAN | I_USER2 | 87 | 348 | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 533 | 97K| 6 (17)| 00:00:01 |
| 8 | INDEX FULL SCAN | I_USER2 | 87 | 1914 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | OBJ$ | 534 | 44322 | 4 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 13 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 17 | 1 (0)| 00:00:01 |
| 16 | INDEX FULL SCAN | I_LINK1 | 1 | 13 | 0 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_USER2 | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Dabei liefert Oracle-SQL doch einen eigenen Generator mit:
select rownum id
from dual
connect by rownum <= 500;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | COUNT | | | | |
|* 2 | CONNECT BY WITHOUT FILTERING| | | | |
| 3 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Diese wenig bekannte Anwendung der hierarchischen Abfrage ist schlank, schön und vor allem viel billiger.
Funktionieren tut das seit mindestens Oracle 9i – frühere Datenbankversionen zum Überprüfen habe ich leider zur Zeit nicht zur Hand.
Leider hat es die Oracle-Schreibweise für hierarchische Abfragen nicht in den ANSI-Standard geschafft, sodass man diese Art Abfragen in den meisten anderen Datenbanken nicht ausführen kann.
Für hierarchische Abfragen sieht ANSI SQL-99 rekursives “subquery refactoring with with-clause” vor.
Komplexe SQL-Abfragen durch with-Blöcke zu vereinfachen, beherrscht Oracle schon seit mindestens Oracle 9i. Die rekursive Aufruf-Erweiterung ist ein neues Feature der 11gR2, sodass man hierarchische Abfragen jetzt auch ANSI-konform formulieren kann:
with
generator (id)
as ( select 1 id from dual
union all
select id+1 from generator
where id < 500 )
select id from generator;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 4 (0)| 00:00:01 |
| 1 | VIEW | | 2 | 26 | 4 (0)| 00:00:01 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | RECURSIVE WITH PUMP | | | | | |
--------------------------------------------------------------------------------------------------
Das ist fast so billig und beinahe genauso schön wie die Connect-by-Variante.
Durch das für die Rekursion nötige UNION ALL schränkt man bei dieser Variante auf “< 500″ ein, im Gegensatz zu den “<= 500″ bei dem Connect-by
