Zyklus-Klausel - zyklische Daten bei rekursiven Anfragen

Die optionale Zyklus-Klausel kann für rekursive, temporäre Hilfssichten in der WITH-Klausel einer SELECT-Anfrage angegeben werden und beschreibt Zyklen in der Rekursion. Zyklen sind nicht endende Ableitungen bereits vorhandener Daten. Erkennt das DBMS? einen Zyklus und ist die Zyklus-Klausel spezifiziert, dass werden die Zyklusdaten entsprechend markiert und die Suche bei "nichtzyklischen" Datensätzen fortgesetzt. Fehlt diese Klausel, dann reagiert das DBMS? auf einen Zyklus mit dem Abbruch der Auswertung der gesamten Anfrage. Zyklische Daten sind etwas ganz normales, wenn man z.B. an Routenplanung denkt, Wegenetze wie unser Straßennetz läßt zyklische Wege zu, ebenso das Schienennetz der Bahn. Bei anderen Anwendungen, wie z.B. die Vorgesetztenbeziehungen oder die Stücklisten sind Zyklen in den Daten eher Hinweise auf nicht konsistente Daten. Zyklen können direkt in benachbarten Ebenen auftreten, wie in dem Beispiel unten, oder sich auch erst über mehrere Ebenen entwickeln.

Syntax Zyklus-Klausel

  <Zyklus Klausel>   ::=    CYCLE  <Spaltenname>  [, <Spaltenname> ]...  
                              SET  <Zyklusspaltenname>   TO  <Zykluswert>   DEFAULT <Nichtzykluswert>
  • Die hier gelisteten Spaltennamen werden vom DBMS? benutzt, um Zyklen in der Rekursion zu entdecken. Sie müssen in der SELECT-Klausel der Hilfssicht gelistet sein.
  • Der Zyklusspaltenname wird automatisch der SELECT-Klausel der Hilfssicht zugefügt. In der Spalte wird gekennzeichnet, ob ein Zyklus aufgetreten ist oder nicht.
  • Die Zykluswert und der Nichtzykluswert sind beide Zeichenketten der Länge 1.
  • Wird ein Zyklus gefunden, so wird für den zyklusverursachenden Datensatz die Zyklusspalte auf den Zykluswert gesetzt und die weitere Auswertung für diesen Datensatz abgebrochen. Die Verarbeitung wird dann mit anderen Datensätzen fortgefahren.
  • Die Zyklenerkennung kann als recht "empfindsam" bezeichnet werden. Sobald ein Datensatz abgeleitet wird, der bereits vorher abgeleitet wurde, wird auf Zyklus erkannt und entsprechend reagiert. Es gibt sicherlich Anwendungen, bei der Punkte (Datensätze) wiederholt durchlaufen werden, ohne dass dies gleich in einem endlosen Zyklus gipfelt. Andererseits ist das Terminierungsproblem rekursiver Anfragen auch in der Wissenschaft ein heikles Thema.
  • Im Vergleich zum SQL-Standard ergibt sich bei dieser Oracle-Syntax nur eine Ergänzung:
    • Die USING <path column>-Klausel, wobei <path column> ein Spaltenname ist.

Beispiele

-- Mitarbeiter werden von verschieden vielen Vorgesetzten über beliebige Ebenen hinweg geführt.
-- Genauso wie die Vorgesetzten auch beliebig viele Mitarbeier über unterschiedliche Ebenen hinweg betreuen.
-- Der Zyklus ist in den ersten beiden Datensätzen zu finden: Schulz -> Meier -> Schulz -> Meier -> Schulz -> u.s.w.

  CREATE TABLE Hierarchie ( Angestellter VARCHAR(20) , Vorgesetzter VARCHAR(20));

  INSERT INTO Hierarchie  VALUES ( 'Schulz',  'Meier'); 
  INSERT INTO Hierarchie  VALUES ( 'Meier',   'Schulz'); 
  INSERT INTO Hierarchie  VALUES ( 'Müller',  'Meier'); 
  INSERT INTO Hierarchie  VALUES ( 'Schmidt', 'Schulz'); 
  INSERT INTO Hierarchie  VALUES ( 'Koch',    'Müller'); 
  INSERT INTO Hierarchie  VALUES ( 'Bäcker',  'Koch'); 
  INSERT INTO Hierarchie  VALUES ( 'Bauer',   'Müller'); 
  COMMIT;

  ALTER TABLE  Hierarchie ADD constraint Hierarchie_Pk PRIMARY KEY (Angestellter);
  ALTER TABLE  Hierarchie ADD constraint Hierarchie_Fk FOREIGN KEY (Vorgesetzter) REFERENCES Hierarchie(Angestellter);

-- Zeigen Sie für alle Angestellten deren Vorgesetzte über alle Hierarchieebenen hinweg an.

-- Die Suchklausel gibt eine Tiefensuche vor, also erst alle Vorgesetzten.
-- Die Zyklusklausel kennzeichnet die zyklischen Datensätze ('Schulz', 'Meier' und 'Meier', 'Schulz') mit einem 'J', die anderen nichtzyklischen mit einem 'N'.
-- Ohne diese Zyklusklausel meldet das Oracle-DBS für die Anfrage auf diese zyklischen Daten die Fehelrmeldung: "ORA-32044: Cycle bei der Ausführung der rekursiven WITH-Abfrage ermittelt".

   WITH Alle_Vorgesetzten (Angestellter, Vorgesetzter,  Ebenenzaehler) 
     -- Basis-/Ankerdatenmenge: alle Angestellten
   AS (SELECT Angestellter, Vorgesetzter, 1
            FROM Hierarchie
       UNION  ALL
       -- davon rekursiv abgeleitete Vorgesetzte
       SELECT h.Angestellter, h.Vorgesetzter, av.Ebenenzaehler+1
         FROM Alle_Vorgesetzten av, hierarchie h
        WHERE  av.Vorgesetzter = h.Angestellter )         
       SEARCH   DEPTH  FIRST BY   Angestellter SET  Sortierspalte          
       CYCLE Angestellter SET Zyklus_Knz TO 'J' DEFAULT 'N' 
   SELECT lpad(' ',2*Ebenenzaehler)||Angestellter AS Angestellter, Vorgesetzter,  Ebenenzaehler, Zyklus_Knz 
     FROM Alle_Vorgesetzten 
    ORDER BY Sortierspalte;

  Ergebnismenge: 
  Angestellter       Vorgesetzter   Ebenenzähler   Zyklus_Knz
  Bäcker   	        Koch	         1	       N
    Koch	        Müller           2	       N
      Müller  	        Meier	         3	       N
        Meier	        Schulz	         4	       N
          Schulz	Meier	         5	       N
            Meier	Schulz	         6	       J
  Bauer	                Müller	         1	       N 
    Müller        	Meier	         2	       N
      Meier        	Schulz           3	       N
        Schulz        	Meier	         4	       N
          Meier        	Schulz	         5	       J
  Koch                	Müller	         1	       N
    Müller	        Meier	         2	       N
      Meier	        Schulz	         3	       N
        Schulz	        Meier	         4	       N
          Meier	        Schulz	         5	       J
  Meier	                Schulz	         1	       N
    Schulz	        Meier	         2	       N
      Meier	        Schulz	         3	       J
  Müller	        Meier	         1	       N
    Meier	        Schulz	         2	       N
      Schulz	        Meier	         3	       N
        Meier	        Schulz	         4	       J
  Schmidt	        Schulz	         1	       N
    Schulz	        Meier	         2	       N
      Meier	        Schulz	         3             N
        Schulz	        Meier	         4	       J
  Schulz	        Meier	         1	       N
    Meier	        Schulz	         2	       N
      Schulz	        Meier	         3	       J



-- Und nun die umgekehrte Aufgabenstellung:
-- Zeigen Sie für alle Vorgesetzten deren Angestellte, über alle Hierarchieebenen hinweg an.
-- Drei Datensätze fallen heraus, weil sie keine Vorgesetzten sind: Schmidt, Bauer, Bäcker.

   WITH Alle_Angestellten (Vorgesetzter, Angestellter, Ebenenzaehler) 
     -- Basis-/Ankerdatenmenge: alle Angestellten
   AS (SELECT Vorgesetzter, Angestellter, 1
            FROM Hierarchie
                -- nur die Vorgesetzten aus der Hierarchie-Tabelle
          WHERE Angestellter IN (SELECT Vorgesetzter
                                   FROM Hierarchie )
       UNION  ALL
       -- davon rekursiv abgeleitete Vorgesetzte
       SELECT h.Vorgesetzter , h.Angestellter , aa.Ebenenzaehler+1
         FROM Alle_Angestellten aa, hierarchie h
        WHERE  aa.Angestellter  = h.Vorgesetzter)         
       SEARCH   DEPTH  FIRST BY   Angestellter SET  Sortierspalte          
       CYCLE Angestellter SET Zyklus_Knz TO 'J' DEFAULT 'N'
   SELECT lpad(' ',2*Ebenenzaehler)||Vorgesetzter AS Vorgesetzter, Angestellter, Ebenenzaehler, Zyklus_Knz 
     FROM Alle_Angestellten 
    ORDER BY Sortierspalte;

  Ergebnismenge: 
  Angestellter       Vorgesetzter   Ebenenzähler   Zyklus_Knz
  Müller	        Koch	         1	       N
    Koch	        Bäcker	         2	       N
  Schulz	        Meier	         1	       N
    Meier	        Müller	         2	       N
      Müller	        Bauer	         3	       N
      Müller	        Koch	         3	       N
        Koch	        Bäcker	         4	       N
    Meier	        Schulz	         2	       N
      Schulz	        Meier	         3	       J
      Schulz	        Schmidt	         3	       N
  Meier	                Müller	         1	       N
    Müller	        Bauer	         2	       N
    Müller	        Koch	         2	       N
      Koch	        Bäcker	         3	       N
  Meier	                Schulz	         1	       N
    Schulz	        Meier	         2	       N
      Meier	        Müller	         3	       N
        Müller	        Bauer	         4	       N
        Müller	        Koch	         4	       N
          Koch	        Bäcker	         5	       N
      Meier	        Schulz	         3	       J
    Schulz	        Schmidt	         2	       N

Weitere Beispiele für rekursive Anfragen sind bei der WITH-Klausel und der Such-Klausel erklärt.

Quellen:

  • Quellen/Standards in http://www.wiscorp.com/SQLStandards.html und http://www.jcc.com/sql.htm
  • INCITS/ISO/IEC 9075-1-2008. Part 1 "SQL/Framework", ISO International Organization for Standardization / INCITS InterNational Committee for Information Technology Standards, 2008
  • INCITS/ISO/IEC 9075-1-2008. Part 2 "SQL/Foundation", ISO International Organization for Standardization / INCITS InterNational Committee for Information Technology Standards, 2008
  • Adams, Ralf: "SQL - Eine Einführung mit vertiefenden Exkursen", Hanser, München, 2012, ISBN 978-3-446-43200-0
  • Elmasri, Ramez/Navathe, Shamkant B.: "Grundlagen von Datenbanksystemen" , Pearson Studium, München, 2002, ISBN 3-8273-7021-3
  • Faeskorn-Woyke, Heide/Bertelsmeier, Birgit/Riemer, Petra/Bauer, Elena: "Datenbanksysteme - Theorie und Praxis mit SQL2003, Oracle und MySQL", Pearson Education, München, 2007, ISBN 978-3-8273-7266-6
  • Kemper, Alfons/Eickler, André: "Datenbanksysteme", Oldenbourg, München, 2009, 978-3-486-59018-0
  • Melton, Jim/Simon, Alan R.: "SQL: 1999 - Understanding Relational Language Components", Morgan Kaufmann, San Francisco, 2001, ISBN 1558604561
  • Oracle® Database SQL Language Reference 11g Release 2 (11.2), E17118-03, August 2010, http://download.oracle.com/docs/cd/E11882_01/server.112/e17118.pdf
  • Saake, Gunter/Sattler, Kai-Uwe/Heuer, Andreas: "Datenbanken - Konzepte und Sprachen", mitp-Verlag, Redline GmbH, Heidelberg, 2007, ISBN 3-8266-1664-2
  • Sieben, Jürgen: "Oracle® SQL - Das umfassende Handbuch", Galileo Press, 2012, ISBN 978-3-8362-1875-7
  • Vossen, Gottfried: "Datenmodelle, Datenbanksprachen und Datenbankmanagementsysteme", Oldenbourg, München, 2008, ISBN 978-3-486-27574-2

Kategorie; SQL, Z