Weiß jemand, warum Oracle weiterhin einem Pfad folgt, der über eine zyklische Schleife hinausgeht, wenn der Zyklus am obersten Knoten (Stammknoten, der direkt mit dem Stammknoten verbunden ist) stattfindet? Noch wichtiger, wie kann man das verhindern?
Ich habe Oracle 11g Release 2 (11.2) und ich habe hierarchische Abfragen untersucht. Ich werde meine Frage um die Baumstruktur in Abbildung 9-1 der Oracle-Datenbank SQL-Sprachreferenz Seite 9-4
erstellenIch habe eine Tabelle Struktur für diesen Baum mit dem Konzept der Anbieter und Kunden erstellt:
%Vor%Die folgende Auswahlabfrage durchläuft den Baum ohne Probleme:
%Vor%Geben Sie die Ergebnisse:
%Vor%Ich habe dann Dinge kompliziert, indem ich der Struktur Zyklen hinzufüge. Zuerst ein Rekord für einen Verkäufer, der sich selbst verkauft ...
%Vor%und eins für einen Kreditor, dessen Kunde der Lieferant seines Lieferanten ist ...
%Vor%Das erneute Ausführen der Auswahlabfrage führt zu der gleichen Ausgabe wie oben, außer dass Iscycle für Zeile 3 und Zeile 5 (Pfade 1 ~ 2 ~ 4 und 1 ~ 2 ~ 4 ~ 6) 1 ist. Beachten Sie, dass die CONNECT BY-Nomenklatur den übergeordneten Datensatz eines Zyklus kennzeichnet und nicht den untergeordneten Datensatz, der den Zyklus tatsächlich beendet. (Also weiß ich, dass 4 und 6 beide zu einem Vorfahren zurückgehen, aber ich weiß nicht, welcher Ahnherr.)
Wenn Sie zwei weitere Datensätze hinzufügen, wird ein größerer Zyklus über die Zweige des ursprünglichen Baums erstellt:
%Vor%Wenn Sie die SELECT-Abfrage erneut ausführen, erhalten Sie folgende Ausgabe:
%Vor%Die Ausgabe ist weiterhin wie erwartet. Alle Zyklen sind fehlerhaft und das Mapping stoppt, wenn ein Zyklus auftritt.
Nun das Problem Kind ... Fügen wir dem Wurzelknoten einen Selbstzyklus hinzu, der genau dem ersten Zyklus entspricht, der oben mit Knoten 4 erstellt wurde; nur für Knoten 1.
%Vor%Diesmal erkennt Oracle erwartungsgemäß den Zyklus bei Knoten 1 (die erste Zeile ist mit Iscycle auf 1 gesetzt). JEDOCH geht es über diesen Zyklus hinaus und baut die gesamte Baumstruktur zweimal auf. Die Zeilen 2 bis 21 sind eine Duplizierung der Zeilen 22 bis 41, wobei der Zyklus des Knotens 1 der Vorderseite des Pfades vorangestellt ist.
%Vor%Warum wird der 1-1-Zyklus nicht genauso behandelt wie der 4-4-Zyklus? Was vermisse ich?
Um dies zu vermeiden, habe ich eine zusätzliche Bedingung in der CONNECT BY-Klausel hinzugefügt, die besagt, dass der Kunde nicht "1" sein muss.
%Vor%Ironischerweise war das alles das Entfernen der Zyklusflagge aus der ersten Reihe.
Jede Hilfe wäre willkommen.
Oracle wählt die root row(s) der Hierarchie (die Zeilen, die die Bedingung START WITH erfüllen).
Oracle wählt die untergeordneten Zeilen jeder Stammzeile aus.
Jede untergeordnete Zeile muss die Bedingung der Bedingung CONNECT BY in Bezug auf eine der Stammzeilen erfüllen.
Um die untergeordneten Elemente einer übergeordneten Zeile zu finden, wertet Oracle den PRIOR-Ausdruck der CONNECT BY-Bedingung für die übergeordnete Zeile und den anderen Ausdruck für jede Zeile in der Tabelle aus.
Zeilen, für die die Bedingung wahr ist, sind die Kinder des Elternteils.
Die Bedingung CONNECT BY kann weitere Bedingungen enthalten, um die von der Abfrage ausgewählten Zeilen weiter zu filtern.
Wenn Sie versuchen, mit demselben Elternteil wie Kind (22 oder 33 oder 44), wird es funktionieren, da sie keine Stammzeilen und nur Eltern sind Da 1 die Wurzel und auch ein Kind mit 1 ist, wird LEVEL aufgrund der CONNECT_BY_ROOT-Klausel
als Zyklus festgelegt Die Duplizierung in der Ausgabe erfolgt seit dem connect by works on root which is duplicated ebenfalls.
Machen Sie Ihren Datensatz entweder eindeutig oder codieren Sie ihn so, dass Oracle in der Hierarchie nach Präferenzen arbeiten kann
FOLGE OBEN: LÖSUNG FÜR OPs Problem
%Vor%Ergebnisse:
%Vor%Ich würde dem ersten Teil von @ realspirituals zustimmen, wie Oracle mit hierarchischen Daten umgeht. In meiner Vision besteht der erste Schritt darin, Wurzelelemente der Bäume zu finden, die in der START WITH-Klausel angegeben sind. Dies könnte zu folgender Abfrage umformuliert werden:
%Vor%Eigentlich haben wir 4 Wurzelknoten und 4 separate Bäume. In den nächsten Schritten wird die CONNECT BY-Klausel iterativ ausgewertet. Stellen Sie sich vor, wir nehmen die obige Liste der KUNDEN-Werte und suchen nach ihren Nachkommen:
%Vor%Sobald wir NOCYCLE angegeben haben, werden erkannte Schleifen verworfen und die vorherige Zeile, die uns zum Schleifensatz geführt hat, wird als CONNECT_BY_ISCYCLE = 1 markiert.
Der dritte Schritt:
%Vor%So geht es, bis mindestens ein Datensatz ausgegeben wird. Es braucht etwas Zeit und Geduld, aber die von Ihrer Anfrage zurückgegebenen Ergebnisse sind vollständig reproduzierbar und erscheinen mir absolut legitim. Das ist die Art und Weise, wie der Algorithmus von Oracle funktioniert, so dass jeder dies beim Schreiben von Abfragen berücksichtigen muss.
Wie können wir den Zyklus auf dem obersten Knoten vermeiden? Ich würde vorschlagen, einen virtuellen Datensatz hinzuzufügen, der unseren Top-Level-Knoten nicht zum Top-Level macht. Bedenken Sie Folgendes:
%Vor%Natürlich ist es nicht angebracht, der Produktionsdatenbank neue Datensätze hinzuzufügen. Kombinieren Sie stattdessen die Abfrage mit einer realen Tabelle mit einer Abfrage, die Knoten der obersten Ebene dynamisch bestimmt. So etwas (mit der gleichen Ausgabe wie oben):
%Vor% Von einem Knoten aus zu starten und einen Knoten mit einem anderen zu verbinden, ist nicht dasselbe. ISCYCLE sucht nach Verbindungen zwischen Kunden und Herstellern und verbindet sie nur einmal pro Pfad. Wenn Sie oracle zu
START WITH vendor = '1'
es beginnt tatsächlich bei 4 Punkten simultanousely :
%Vor% Diese Pfadsuchen werden parallel ausgeführt und jeder Pfad versucht, nicht mit seinem eigenen Pfad zu fahren. Jeder Pfad weiß nichts über die anderen. Der Pfad, der mit 1 ~ 1 beginnt, weiß also nicht, warum er nicht weiter auf 2, 7 und 9 setzen sollte, weil er vorher nicht da war. Das NOCYCLE verbietet es einfach, noch einmal in 1 zu schauen. Sie können also entweder
START WITH (vendor='1' AND customer !='1')
um zu viele Startpunkte zu vermeiden und / oder Verbindungen zu ignorieren, bei denen Anbieter und Kunde identisch sind:
%Vor% Das nocycle erlaubt Ihrer Abfrage tatsächlich, Zyklen zu haben, ohne dieses Schlüsselwort würde Oracle stoppen, sobald ein Zyklus erkannt wird ( ORA-01436: CONNECT BY loop in user data ). Es ermöglicht Ihnen auch, "CONNECT_BY_ISCYCLE" zu verwenden, um Orte zu erkennen, an denen Kinder den Zyklus durchführen, aber das Filtern der Abfrage nach diesem Ergebnis würde gültige Zeilen entfernen.
Also, vielleicht könnten Sie Ihre connect by nocycle vendor=prior customer AND connect_by_iscycle = 0 in der Schleifenbedingung verwenden, um alle Schleifen zu vermeiden, nachdem Eltern von Zyklen erkannt wurden? (Ich habe keine Dinge, um es zu testen). Dies würde die Rekursivität auf dem ersten 1 ~ 1 Pfad stoppen.
Tags und Links sql hierarchy oracle11g common-table-expression cycle