its-people Blog – Der Oracle Blog neben Beiträgen aus den Bereichen: Datenbanken, Business Intelligence & Datawarehouse, IT-Management Service & ERP-Beratung

Etwas Großes kommt… (Oracle 12c)

Mehr als fünf Jahre sind seit der Einführung der Datenbank 11g vergangen. Soviel Zeit hat sich Oracle zwischen zwei Hauptversionen noch nie gelassen. Langsam ist es an der Zeit für das nächste große Datenbankrelease. Auf der diesjährigen Oracle Openworld Conference (OOW) im Oktober in San Francisco war es dann soweit. Es dürfte keinen mehr überraschen, dass es Oracle 12c heißen wird.

Larry Ellison, der Gründer von Oracle, hat im Mai in einem Interview von einem Erscheinungstermin im Dezember diesen oder Januar nächsten Jahres gesprochen. Auf der OOW wurde der Korridor mit „im Jahr 2013“ etwas weiter gefasst.

In etlichen Sessions wurden neue Features und Änderungen der nächsten Version vorgestellt. Immer allerdings unter dem Vorbehalt, dass die neuen vorgestellten Features es nicht zwingend ins neue Release schaffen müssen und ohne, die gezeigten Folien zu veröffentlichen.

Durch welche Features wir tatsächlich in der neuen Version beglückt werden, werden wir also erst am Tag der Veröffentlichung erfahren. Aber natürlich lohnt es sich, einen vorsichtigen Blick auf das zu werfen, was da nächstes Jahr auf uns zukommt. read more…

Benutzer-Wechsel in einem Package

Immer wieder möchte man aus einem PL/SQL-Package heraus einen Befehl absetzen, der nicht unter dem aktuellen sondern unter einem anderen Benutzer abgesetzt wird. Das ist so ohne weiteres nicht möglich, da ein Package einen Reconnect oder Benutzer-Wechsel nicht unterstützt. Hier leistet der Oracle Scheduler wertvolle Hilfe. Man kann mit ihm aus einem Package heraus einen synchronen Job aufrufen, der unter einem beliebigen Benutzer gestartet werden kann.

Hierzu ein Beispiel:

create or replace package my_utilities

is

--

-- Ausführen eines beliebigen SQL-Statements

procedure exec_with_other_user(p_sql in varchar2);

--

-- Erstellen eines Scheduler Jobs

procedure exec_sql(p_user in varchar2

                  ,p_sql  in varchar2

                  );

/

end my_utilities;

Oracle goes Public Cloud

Eine echte Datenbank- und Enterprise-Applikationsumgebung für Entwicklung und Betrieb in einer Public Cloud bietet Oracle als Platform Services an. Dabei verbindet die Oracle Public Cloud die Datenbank Oracle 11g R2 mit dem Oracle Weblogic Server auf einer hochverfügbaren, skalierbaren und sicheren IT-Infrastruktur.

Innerhalb der Oracle Public Cloud stehen Shared Enterprise Services (z. B. Adminstration, Benutzerverwaltung, Datei- und Datentransfer) zur Verfügung und es werden Funktionen zur Service-Integration und zur Verbindung mit anderen Services außerhalb der Public Cloud geboten.

Darüber hinaus werden Business Application Services für Customer Relationship Management, Human Capital Management und Social Network angeboten.

Die Plattform ist powered by Oracle Exadata und Oracle Exalogic.

Ausblick Oracle Portfolio

Oracle führt die nächste Produktfamilie unter dem Label 12c. Dabei wird mit dem „c“ der Strategie zur Entwicklung und Bereitstellung von „cloud-ready“ Products Rechnung getragen.

So wurde im Herbst der Oracle Enterprise Manager 12c veröffentlicht und aktuell der Oracle WebLogic Server 12c angekündigt. Mit der Bereitstellung einer Oracle Database 12c kann noch in diesem Jahr gerechnet werden.

Alle Jahre wieder…

Alle Jahre wieder Ende November in Nürnberg …Nein, es geht hier nicht um den berühmten Christkindlmarkt der alljährlich seine Pforten für die zahlreichen Besucher aus nah und fern öffnet, sondern es geht hier um die größte Oracle-Konferenz in Europa, die DOAG 2011, die auch dieses Jahr mit mehreren tausend Besuchern, wieder im Messezentrum in Nürnberg stattfand.

„Mehr aus seinen Oracle-Produkten herausholen“ unter diesem Motto fand die diesjährige 24. Auflage der DOAG Konferenz und Ausstellung vom 15.-17. November 2011 statt und bot wieder die ideale Plattform für den Erfahrungsaustausch zwischen den Oracle-Anwendern. Auf dieser größten Oracle-Konferenz in Europa trifft sich das „who is who“ der Oracle Welt. Rund 400 Fachvorträge vermitteln die neuesten Erkenntnisse zum erfolgreichen Einsatz der Oracle-Produkte. Das angebotene Rahmenprogramm bietet ausreichend Raum zum Networking.

read more…

5 Freunde von its-people auf der DOAG Konferenz 2011

Die DOAG Konferenz 2011, die im November  wieder in Nürnberg stattfindet stellt laut DOAG die größte Oracle-Veranstaltung in Europa dar.

Seit der Gründung 2003 ist its-people jedes Jahr auf der DOAG Konferenz mit aktuellen Vorträgen vertreten.

its-people ist schon seit seinem Bestehen jedes Jahr mit Vorträgen in der Konferenz vertreten. Dieses Jahr sind wir wieder mit fünf Referenten und fünf Themen dabei.

Sowohl Strategie- und Management-Themen (Cloud Computing / Organic Computing) als auch aktuelle Technologien (jQuery in Apex) und handfeste Aufklärungsarbeit (Mythen und Wahrheiten über Performance-Optionen mit Partitioning) sind vertreten. Im Einzelnen können sie mehr erfahren zu:

read more…

Ein Jahr auf dem Weg zum Oracle SOA Certified Expert

Aller Anfang ist schwer: Als ich im Frühjahr 2010 mit der Vorbereitung auf die SOA-Zertifizierungsprüfung begann, befand ich mich in einer Umbruchphase. Durch den erst im Oktober 2009 vollzogenen Wechsel in die freiberufliche Tätigkeit gab es einige kleinere Veränderungen im familiären Umfeld. Auch wenn die Situation zunächst äußerlich weitgehend unverändert schien, gab es doch unter der Oberfläche ein paar Spannungen. Eltern wissen: Kinder wittern so etwas und reagieren entsprechend unkooperativ.

Obwohl weiterhin bei meinem ehemaligen Arbeitgeber unter Vertrag, hatte ich ein neues, inhaltlich komplexes Projekt vor der Brust.

In dieser Situation startete ich also im Februar 2010 in die Auseinandersetzung mit der Oracle SOA-Suite 11g. Mein Kerngeschäft ist bis heute eigentlich die Entwicklung von Ladeprozessen für analytische Datenbanken und dem darauf aufsetzenden Berichtswesen, oder auch kurz: klassische Business Intelligence. Auch wenn es durchaus Berührungspunkte dieses Umfeldes mit serviceorientierten Architekturen gibt, könnte sowohl das technische als auch das inhaltliche Umfeld kaum unterschiedlicher sein:

• Hatte ich bislang hauptsächlich mit Batch-Prozessen zu tun, geht es hier im Kern um interaktive Prozesse
• Der durchgängige Einsatz von XML-Strukturen war für mich weitgehend Neuland (na ja: auch vorher bin ich um diese Technologie nicht wirklich herumgekommen, aber die Berührungspunkte waren vereinzelt)
• Einzeltransaktionen statt Massendaten (OLTP statt OLAP)
• Ein völlig anderes zugrundeliegendes Prozessdenken

In den folgenden Monaten arbeitete ich mich mit Hilfe des Buches “Getting Started with Oracle SOA Suite 11gR1 – A Hands On Tutorial” grundlegend in die Materie ein. Die ersten Übungen führte ich auf einem Notebook mit einem 32-Bit Windows Betriebssystem durch. 3 GB Arbeitsspeicher, ein eher langsamer Dual-Core Prozessor. Mit der installierten Oracle XE Datenbank, dem Weblogic-Adminserver, dem SOA-Managed-Server, dem BAM-Server, dem Oracle Service Bus und J-Developer war die Maschine am Anschlag: der Speicher prallvoll, die CPU zumindest gelegentlich überfordert. Entsprechend langsam der Seitenaufbau des Webservers. So geht es nicht…

Abhilfe brachte neue Hardware: ein Server mit zwei Quad-Core Xeon Prozessoren machte ab April mächtig Dampf in der Bude. Neben viel Rechenleistung produziert die Maschine aber auch Lärm und Wärme. Ein paar Umbauten in der Wohnung sind erforderlich… Außerdem: die ganze Installation muss erneut durchgeführt werden. Zunächst unter Oracle Linux. Die Oracle 11gR2-Datenbank verlangt eine ganze Reihe von zusätzlichen Softwarepaketen vor der Installation. Bin ich ein professioneller Linux-Administrator? Nein! Aber der Mensch wächst bekanntlich mit seinen Herausforderungen. Meine Frau reagiert allerdings zeitweilig genervt darauf, dass ich abends häufig noch lange Zeit am Rechner verbringe…

Ende Juni endet mein Projekt in Hamburg. Nach dem Sommerurlaub mehrere Wochen Zeit, mich Vollzeit nur mit SOA zu beschäftigen. Jetzt nimmt die Sache richtig Fahrt auf. Dazwischen ein Betriebssystemwechsel auf dem Server: Ein Windows 2008 Server wird installiert. Wieder diverse Herausforderungen mit dem Betriebssystem. Allerdings installiert sich auf dieser Plattform die ganze SOA-Suite einschließlich Datenbank ohne einen Muckser.

Auf dieser Umgebung ließ es sich nun wirklich trefflich arbeiten. Zudem verbringe ich Stunden und Tage mit den Oracle Online-Kursen zur SOA-Suite um mich auf die Prüfung vorzubereiten.

Im September beginnt ein neues Projekt. Diesmal in Berlin. Ich bin von Montag bis Donnerstag außer Haus. Mein Notebook bekommt ein neues 64-Bit Windows 7 und 6 GB RAM. Die Maschine ist nicht so schnell wie der Server, aber man kann sie mit nach Berlin nehmen. Im Hotel habe ich keinen sicheren Internetzugang und keine ausreichende Bandbreite für einen Remote-Zugriff. Also wieder eine Neuinstallation der SOA-Suite. Inzwischen ist auch die neue BPM-Suite in den Fokus gerückt. Ein Hands-On Workshop in Berlin gibt erste Eindrücke von BPMN 2.0 und der Implementierung des neuen Standards durch Oracle. Mit der Neuinstallation kommt auch die BPM-Suite auf den Rechner.

Ende November beschließe ich endlich, mich zur Zertifizierungsprüfung anzumelden. Ich habe in den letzten Monaten in Berlin die Theorie etwas schleifen lassen. Bin ich optimal vorbereitet? Egal, ich will diese Sache jetzt hinter mich bringen. Tatsächlich erreiche ich auf Anhieb die erforderliche Punktzahl und darf mich seither als Oracle SOA Infrastructure Implementation Certified Expert bezeichnen. Nach der langen Vorbereitung bin ich nun darauf gespannt, die neu erworbenen Kenntnisse auch in “echten” Projekten umzusetzen.

Fortsetzung folgt…

Virtuelle Spalten

Auch Oracle kann sich dem Trend zur Virtualisierung nicht entziehen und bietet neben einer eigenen VM auch virtuelle Spalten an. Bzgl. des Sinns dieser Möglichkeit hatte ich bereits etliche Diskussionen ohne wirkliches Ergebnis – beschränken wir uns also auf die Fakten.

Die Idee zur Nutzung virtueller Spalten kam in einem Projekt, in welchem es u.a. um Lageinformationen von Objekten geht. Hierbei handelt es sich um Kilometrierungs-Informationen, für die es 3 verschiedene Schreibweisen gibt:

• km_v: 124,5 + 32
• km_e: 112450032
• km_r: 124,532

Diese 3 Darstellung meinen exakt das Gleiche und werden von den Entwicklern in allen Tabellen mit Kilometrierungs-Informationen erwartet. Nun gäbe es die Möglichkeit, alle 3 Schreibweisen vorzuhalten. Dies bedeutet aber, Informationen redundant vorliegen zu haben. Alternativ könnte man sich für eine Schreibweise entscheiden und dem Entwickler die Umrechnung überlassen. Natürlich sind auch mehr oder weniger komplexe View-Konstrukte denkbar. Oder aber man entscheidet sich für virtuelle Spalten.

Angenommen es existiert eine Tabelle OBJEKTE mit dem Objektnamen und der Spalte KM_V:

create table objekte
( objektname varchar2 ( 100 )
, km_v varchar2 ( 20 ) );

Dieser Tabelle können nun die beiden anderen Schreibweisen als virtuelle Spalten hinzugefügt werden:

alter table objekte
add ( km_e as ( util.get_e_km ( km_v ) )
, km_r as ( util.get_r_km ( km_v ) ) );

Dies setzt natürlich die Existenz der Umrechnungsfunktion im Package UTIL voraus.

Damit stehen den Entwicklern alle erwarteten Spalten zur Verfügung, die Daten werden aber nicht redundant vorgehalten.

Zu beachten sind folgende Restriktionen:

• virtuelle Spalten können nicht als Basis weiterer virtueller Spalten genutzt werden
  (ORA-54012: virtual column is referenced in a column expression)
• virtuelle Spalten können nicht über DML-Statements verändert werden
  (ORA-54017: UPDATE operation disallowed on virtual columns)
  (ORA-54013: INSERT operation disallowed on virtual columns)

Davon abgesehen verhalten sich virtuelle Spalten wie “normale” Spalten.

Über den Data-Dictionary-View USER_TAB_COLUMNS kann herausgefunden werden, ob es sich um virtuelle Spalten handelt:

select table_name, column_name, data_type
from user_tab_cols
where virtual_column = ‘YES’;

TABLE_NAME  COLUMN_NAME  DATA_TYPE
=========== ============ ==========
OBJEKTE     KM_E         NUMBER
OBJEKTE     KM_R         NUMBER

Virtuelle Spalten können indiziert werden, um Zugriffe zu beschleunigen. Allerdings sollte beachtet werden, dass virtuelle Spalten erst beim Zugriff berechnet werden – bei performancekritischen Abfragen sollte die Nutzung also ausgiebig getestet werden.

“buffer busy waits” und “Next” Extent Größe

Das Warten auf “busy buffers” kann die unterschiedlichsten Ursachen haben. So werden 18 Block-Kassen unterschieden die sich nicht nur auf Daten-, Undo-, Sort- oder Index-Block (bzw. auf deren Header) beziehen, sondern auch bis hinunter auf Extent- und Datenfile-Ebene gehen.

Mein Problem

Nach dem Umzug meiner Datenbank von Solaris auf Linux mittels Datapump, zeigten die Statspack Reports  immer das Event “buffer busy waits” unter den Top-5. Die Anwendung auf der Instanz war unverändert; vielleicht ein verändertes Userverhalten (wo wir ja jetzt wesentlich flotter unterwegs waren). Mein erster Gedanke: “Hot Blocks”.

Die Analyse

Im Nachhinein gibt die v$session_wait leider nicht mehr viel her, also versuche ich es mit der v$session_wait_history und suche nach dem Event.

EVENT               SID       sum       sum  P1TEXT  P1  P2TEXT      P2  P3TEXT  P3
                        wait_time wait_cout
buffer busy waits   433         1         1  file#    7  block#  601840  class#   1
buffer busy waits   228   588         6  file#    7  block#       2  class#  13
buffer busy waits   513   588         6  file#    6  block#       2  class#  13

Ok. File# 7 ist das Daten Tablespace und 6 das Tablespace für die Indizes, aber was bitte schön ist die class# 13?

Recherchen ergeben: Class#13 steht für “file header block”

Demnach handelt es sich hier nicht um die sonst üblichen konkurrierenden Zugriffe auf gleiche Datenblöcke, sondern spielt sich weiter unten auf der Datenfile Ebene ab.

Zudem wurden, dass kam dann so nebenbei heraus, zu den betroffenen Zeiten verstärkt Daten über die Anwendung in die Datenbank geladen.

Die Analyse der Datenfiles ergabt, dass beim initialen Anlegen der beiden Tablespases keine Größe für Next Extents angegeben wurde. Somit wurde ein Defaultwert von 8KB (was genau einem Block entspricht) angenommen.

Dementsprechend war das System,während der Ladevorgänge, ständig damit beschäftigt die Datenfiles um 8KB zu erweitern.

Die Lösung

Ich habe dann die Größe der Next Extents auf 100MB gesetzt und das Event “buffer busy waits”, zumindest was die Block-Klasse 13 betrifft, gehört der Vergangenheit an.

Abfrage der System Events

Mit folgendem SQL habe ich mir geholfen.

SELECT  nvl(s.schemaname, 'disconnected') as "Schema Name"
,       w.sid
,       w.event
,       e.wait_class
,       w.wait_time
,       w.wait_count
,       upper(w.p1text) as "P1 Type"
,       w.p1 as "P1 Value"
,       upper(w.p2text) as "P2 Type"
,       w.p2 as "P2 Value"
,       upper(w.p3text) as "P3 Type"
,       w.p3 as "P3 Value"
,       decode  (nvl(upper(w.p3text),'|')
                , 'CLASS#'
                , decode (w.p3
                         ,1 ,'data block'
                         ,2 ,'sort block'
                         ,3 ,'save undo block'
                         ,4 ,'segment header'
                         ,5 ,'save undo header'
                         ,6 ,'free list'
                         ,7 ,'extent map'
                         ,8 ,'1st level bmb'
                         ,9 ,'2nd level bmb'
                         ,10,'3rd level bmb'
                         ,11,'bitmap block'
                         ,12,'bitmap index block'
                         ,13,'file header block'
                         ,14,'unused'
                         ,15,'system undo header'
                         ,16,'system undo block'
                         ,17,'undo header'
                         ,18,'undo block'
                         )
                ,''                
                ) as "Block Type"
,       case when nvl(upper(w.p3text),'|') in ('CLASS#','BLOCKS','SET-ID#','BUF_PTR')
             then ( select  x.segment_name
                    from    dba_extents x
                    where   x.file_id = w.p1
                    and    w.p2 between x.block_id and x.block_id + x.blocks -1
                  )
        end as "Segment Type"
,       s.sql_hash_value                
FROM    (
         SELECT x.event
         ,      x.event#
         ,      x.sid
         ,      sum(x.wait_time) as wait_time
         ,      sum(x.wait_count) as wait_count
         ,      x.p1
         ,      x.p2
         ,      x.p3
         ,      x.p1text
         ,      x.p2text
         ,      x.p3text
         FROM   v$session_wait_history x
         WHERE  x.wait_time     > 0
         GROUP BY x.event, x.event#, x.sid, x.p1text, x.p2text, x.p3text, x.p1, x.p2, x.p3
        ) w
        ,       v$event_name            e
        ,       v$session               s
WHERE   e.event#        = w.event#
 AND    s.sid(+)        = w.sid
 AND    e.wait_class    not in ('Idle')
 AND    w.event         = 'buffer busy waits'
 AND    s.schemaname(+) like '%'||'&schema'||'%'
ORDER BY s.schemaname,w.sid;

PLS-00553 beim Restore mit RMAN

Es geht hier um ein Problem des fehlenden bzw. falschen default charaterset des RMAN-Cataloges.
Dies ist in Metalink durch hinlänglich viele Bug’s beschrieben für die Versionen 8.1 über 9 bis hin zu 10.2.0.5.

Mein Problem:

Beim Erstellen eines Klones (ich verwende das Backup einer anderen DB für das Recovery) der Produktion in eine Abnahmeumgebung tritt folgender Fehler auf und RMAN beendet sich:

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
RMAN-06003: ORACLE error from target database:
ORA-06550: line 1, column 152:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

und

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/01/2010 15:09:59
ORA-06550: line 1, column 43:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

Was bis dahin erfolgreich durchgeführt wurde, war der Restore der Controlfiles und die Ausführung verschiedener SQL-Statements (z.b. Löschen der Standby Logs und Deaktivieren von Flashback). Der Fehler tritt auf bei dem eigentlichen RESTORE-Kommando.

Der Workaround

Ein schneller Workaround für dieses Problem sieht folgendermassen aus:

1. Im RMAN-Template wird alles auskommentiert was bis dato erfolgreich ausgeführt wurde
2. Neustart des RMAN und alles funktioniert.

Das RMAN-Template

Das Template für den 2.ten Versuch:

run {
   allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/var/opt/oracle/conf/tdpo.opt),BLKSIZE=524288';
   #
#   restore controlfile;
#   alter database mount;
   #
#   SQL "alter database flashback off";
#   SQL "alter database set standby database to maximize  performance";
#   SQL "alter database disable block change tracking";
#   SQL "alter database drop standby logfile group     11";
   #
   restore database FORCE CHECK READONLY;
   #
   recover database delete archivelog;
}

SQL Zeilengenerator

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