Pourquoi Oracle connecter avec nocycle suit racine cycle

Personne ne sait pourquoi Oracle continue à suivre un chemin au-delà du cycle de la boucle lorsque le cycle se produit en haut de nœud (nœud racine connecté droit de retour au nœud racine)? Plus important encore, comment l'empêcher?

J'ai Oracle 11g Release 2 (11.2) et j'ai été d'explorer des requêtes hiérarchiques. Je vais construire ma question autour de la structure de l'arbre dans la figure 9-1 de la Base de données Oracle SQL Langage de Référence page 9-4

J'ai créé une table structe pour cet arbre à l'aide de la notion de vendeurs et de cusomers:

    create table t
     ( vendor       varchar2(3)
    , customer   varchar2(3)
    );
    insert into t values ( '1'  , '2'  ); 
    insert into t values ( '2'  , '3'  ); 
    insert into t values ( '2'  , '4'  ); 
    insert into t values ( '4'  , '5'  ); 
    insert into t values ( '4'  , '6'  ); 
    insert into t values ( '1'  , '7'  ); 
    insert into t values ( '7'  , '8'  ); 
    insert into t values ( '1'  , '9'  ); 
    insert into t values ( '9'  , '10' ); 
    insert into t values ( '10' , '11' ); 
    insert into t values ( '9'  , '12' ); 
    commit;

La requête de sélection suivante parcourt l'arbre, pas de problèmes:

    select vendor, 
           customer, 
           level, 
           connect_by_isleaf as isleaf, 
           connect_by_iscycle as iscycle, 
           connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
    from t
    connect by nocycle
          vendor=prior customer
    start with vendor='1';

Donner les résultats:

Vendor Cust     Level   Isleaf Iscycle   Path
1        2        1        0        0   1 ~ 2
2        3        2        1        0   1 ~ 2 ~ 3
2        4        2        0        0   1 ~ 2 ~ 4
4        5        3        1        0   1 ~ 2 ~ 4 ~ 5
4        6        3        1        0   1 ~ 2 ~ 4 ~ 6
1        7        1        0        0   1 ~ 7
7        8        2        1        0   1 ~ 7 ~ 8
1        9        1        0        0   1 ~ 9
9        10       2        0        0   1 ~ 9 ~ 10
10       11       3        1        0   1 ~ 9 ~ 10 ~ 11
9        12       2        1        0   1 ~ 9 ~ 12

J'ai ensuite compliquer les choses en ajoutant des cycles de la structure. D'abord un record pour un vendeur qui vend à eux-mêmes...

    --self cycle
    insert into t values ( '4'  , '4'  );

et un pour un vendeur dont le client est le fournisseur de leur fournisseur...

    --ancestor cycle
    insert into t values ( '6'  , '2'  );

Reexecuting la requête select résultats ci-dessus dans le même résultat que ci-dessus sauf Iscycle est 1 pour la ligne 3 et la ligne 5 (Voies 1 ~ 2 ~ 4 et 1 ~ 2 ~ 4 ~ 6). Notez que la connexion PAR la nomenclature des drapeaux de l'enregistrement parent d'un cycle de ne pas l'enregistrement enfant réellement terminer le cycle. (Donc je sais de 4 et 6 fois le cycle de retour d'un ancêtre, mais je ne sais pas QUI ancêtre.)

Ajout de deux dossiers crée un plus grand vélo dans les branches de l'arbre d'origine:

 --cycle crossing branches of tree
  insert into t values ( '6'  , '9'  ); 
  insert into t values ( '11' , '2'  );

Reexecuting la requête select de nouveau donne le résultat suivant:

Vendor Customer Level   Isleaf   Iscycle       Path
1        2        1        0        0    1 ~ 2
2        3        2        1        0    1 ~ 2 ~ 3
2        4        2        0        1    1 ~ 2 ~ 4
4        5        3        1        0    1 ~ 2 ~ 4 ~ 5
4        6        3        0        1    1 ~ 2 ~ 4 ~ 6
6        9        4        0        0    1 ~ 2 ~ 4 ~ 6 ~ 9
9       10        5        0        0    1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11        6        1        1    1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12        5        1        0    1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1        7        1        0        0    1 ~ 7
7        8        2        1        0    1 ~ 7 ~ 8
1        9        1        0        0    1 ~ 9
9       10        2        0        0    1 ~ 9 ~ 10
10      11        3        0        0    1 ~ 9 ~ 10 ~ 11
11       2        4        0        0    1 ~ 9 ~ 10 ~ 11 ~ 2
2        3        5        1        0    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2        4        5        0        1    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4        5        6        1        0    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4        6        6        1        1    1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12        2        1        0    1 ~ 9 ~ 12

La sortie continue d'être comme prévu. Tous les cycles sont flaged et la cartographie s'arrête lorsqu'un cycle est détecté.

Maintenant, le problème de l'enfant... ajoutons une auto cycle pour le nœud racine qui est exactement le même que le premier cycle créé ci-dessus avec le nœud 4; juste pour le nœud 1.

    insert into t values ( '1'  , '1'  );

Cette fois, Oracle détecte le cycle au niveau du nœud 1, comme prévu (la première ligne est marqué avec Iscycle mis à 1); TOUTEFOIS, il se prolonge au-delà de ce cycle et construit l'ensemble de la structure de l'arbre deux fois. Les lignes 2 à 21 sont une duplication de lignes 22 à 41 avec le cycle de nœud 1 ajouté sur le devant de la voie.

Vendor Customer  Level Isleaf Iscycle    Path
1        1        1        0    1      1 ~ 1
1        2        2        0    0      1 ~ 1 ~ 2
2        3        3        1    0      1 ~ 1 ~ 2 ~ 3
2        4        3        0    1      1 ~ 1 ~ 2 ~ 4
4        5        4        1    0      1 ~ 1 ~ 2 ~ 4 ~ 5
4        6        4        0    1      1 ~ 1 ~ 2 ~ 4 ~ 6
6        9        5        0    0      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9
9       10        6        0    0      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11        7        1    1      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12        6        1    0      1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1        7        2        0    0      1 ~ 1 ~ 7
7        8        3        1    0      1 ~ 1 ~ 7 ~ 8
1        9        2        0    0      1 ~ 1 ~ 9
9       10        3        0    0      1 ~ 1 ~ 9 ~ 10
10      11        4        0    0      1 ~ 1 ~ 9 ~ 10 ~ 11
11       2        5        0    0      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2
2        3        6        1    0      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2        4        6        0    1      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4        5        7        1    0      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4        6        7        1    1      1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12        3        1    0      1 ~ 1 ~ 9 ~ 12
1        2        1        0    0      1 ~ 2
2        3        2        1    0      1 ~ 2 ~ 3
2        4        2        0    1      1 ~ 2 ~ 4
4        5        3        1    0      1 ~ 2 ~ 4 ~ 5
4        6        3        0    1      1 ~ 2 ~ 4 ~ 6
6        9        4        0    0      1 ~ 2 ~ 4 ~ 6 ~ 9
9       10        5        0    0      1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11        6        1    1      1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12        5        1    0      1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1        7        1        0    0      1 ~ 7
7        8        2        1    0      1 ~ 7 ~ 8
1        9        1        0    0      1 ~ 9
9       10        2        0    0      1 ~ 9 ~ 10
10      11        3        0    0      1 ~ 9 ~ 10 ~ 11
11       2        4        0    0      1 ~ 9 ~ 10 ~ 11 ~ 2
2        3        5        1    0      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2        4        5        0    1      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4        5        6        1    0      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4        6        6        1    1      1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12        2        1    0      1 ~ 9 ~ 12

Pourquoi n'est-ce pas le 1-1 cycle traités de la même façon que le 4-4 cycle? Ce qui me manque?

D'atténuer cette j'ai ajouté une condition supplémentaire sur la connexion PAR la clause exigeant que le client de ne pas être à ‘1’.

    select vendor, 
customer, 
level, 
connect_by_isleaf as isleaf, 
connect_by_iscycle as iscycle, 
connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
from t
connect by nocycle
vendor=prior customer
and customer<>'1' 
start with vendor='1';

Ironiquement, tout cela n'a été de SUPPRIMER le cycle de drapeau à partir de la ligne.

Toute aide serait appréciée.

C'est peut-être parce que 1 est le niveau 1 et le 4 n'est pas. Aussi pourquoi ne pas simplement ajouter des WHERE VENDOR <> CUSTOMER
Je veux être averti lorsque leurs est un cycle, donc je ne veux pas utiliser lorsque le vendeur <> le client. Certains de mes réels arbre structures comportent de 10 à 20 niveaux et des milliers de nœuds. avoir un niveau répliqué provoque des doublons. Selon tous les documents que je peux trouver, il ne devrait pas question que le cycle se produit au niveau un. ...mais il le fait... 🙁
Pourriez-vous vérifier?

OriginalL'auteur Larry May | 2013-09-20

Oracle sélectionne le root row(s) de la hiérarchie (les lignes répondant à la DÉMARRER AVEC une condition.)
Oracle sélectionne l'enfant rangées de chaque racine de ligne.
Chaque ligne enfant doit satisfaire à la condition de la CONNECT BY condition à l'égard de la racine lignes.

Pour trouver les enfants d'un parent ligne, Oracle évalue au PRÉALABLE l'expression de la CONNECTER EN condition pour la ligne parent et l'autre expression pour chaque ligne de la table.
Les lignes pour lesquelles la condition est vraie, ce sont les enfants de la mère.
Le CONNECT BY condition peut contenir d'autres conditions pour filtrer les lignes sélectionnées par la requête.

A root row is the highest row within an inverted tree.

Si vous essayez avec le même parent que l'enfant (22 ou 33 ou 44), cela fonctionnera, car ils ne sont pas la racine de lignes et seulement les parents
Depuis le 1er est la racine et aussi un enfant avec 1, le NIVEAU est réglé pour le cycle en raison de CONNECT_BY_ROOT clause

Duplication de sortie se produit depuis le connect by works on root which is duplicated.

Oracle is not able to restrict the uniqueness since Oracle can't give preference to one of the other

Soit faire votre jeu de données unique ou code d'eux, tels que oracle peuvent travailler de préférence dans la hiérarchie

SUIVI:
SOLUTION POUR des OP problème

SELECT
VENDOR,
CUSTOMER,
LEVEL,
CONNECT_BY_ISLEAF AS ISLEAF,
CONNECT_BY_ISCYCLE AS ISCYCLE,
CONNECT_BY_ROOT VENDOR
|| SYS_CONNECT_BY_PATH ( CUSTOMER,
' ~ ' )
AS PATH
FROM
(SELECT
VENDOR,
CUSTOMER
FROM
T
WHERE
CUSTOMER <> '1')
CONNECT BY
NOCYCLE VENDOR = PRIOR CUSTOMER
START WITH
VENDOR = '1';

Résultats:

VENDOR CUSTOMER      LEVEL     ISLEAF    ISCYCLE PATH                                                                            
------ -------- ---------- ---------- ------------------------------------------------------------------------------------------
1      2                 1          0          0 1 ~ 2                                                                           
2      3                 2          1          0 1 ~ 2 ~ 3                                                                       
2      4                 2          0          1 1 ~ 2 ~ 4                                                                       
4      5                 3          1          0 1 ~ 2 ~ 4 ~ 5                                                                   
4      6                 3          0          1 1 ~ 2 ~ 4 ~ 6                                                                   
6      9                 4          0          0 1 ~ 2 ~ 4 ~ 6 ~ 9                                                               
9      10                5          0          0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10                                                          
10     11                6          1          1 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11                                                     
9      12                5          1          0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12                                                          
1      7                 1          0          0 1 ~ 7                                                                           
7      8                 2          1          0 1 ~ 7 ~ 8                                                                       
1      9                 1          0          0 1 ~ 9                                                                           
9      10                2          0          0 1 ~ 9 ~ 10                                                                      
10     11                3          0          0 1 ~ 9 ~ 10 ~ 11                                                                 
11     2                 4          0          0 1 ~ 9 ~ 10 ~ 11 ~ 2                                                             
2      3                 5          1          0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3                                                         
2      4                 5          0          1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4                                                         
4      5                 6          1          0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5                                                     
4      6                 6          1          1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6                                                     
9      12                2          1          0 1 ~ 9 ~ 12                                                                      
20 rows selected

Merci pour la réponse. Je ne suis pas tout à fait sûr j'ai suivi ce que vous voulez dire quand vous dites Since 1 is the root and also a child with 1, the LEVEL is set to be cycle due to CONNECT_BY_ROOT clause dites-vous que si je sors de l'CONNECT_BY_ROOT fonction de mon problème serait résolu? Votre code proposé ne parvient pas à informer mon du cycle qui existe au niveau du nœud 4 vers le nœud 4. :- (: J'ai par la suite découvert que TOUTE référence au nœud racine ne parvient pas à déclencher le cycle de drapeau sur la première passe. Si j'élimine tout childs égal à la racine, j'ai des problèmes de détruire legitamate liens.
(continuer) lorsque plus d'une racine existe; par exemple START WITH VENDOR IN (1, 2) {avec une clause where dans la sous-requête vous proposons de WHERE CUSTOMER NOT IN (1, 2) } détruire toute une branche de l'arborescence de la structure du Fournisseur 1.
Oui, vous avez raison. mon point était "Oracle n'est pas en mesure de restreindre l'unicité depuis Oracle ne peut pas donner la préférence à l'un des autres" en raison de l'auto cycle nœud racine. Ce que j'ai donné est une démo pour le raisonnement de votre problème et non une solution. Encore besoin de travailler sur ce
Mis à jour le SQL pour votre problème

OriginalL'auteur SriniV

Je suis d'accord avec @realspirituals de la première partie de l'explication sur la façon dont Oracle, offres spéciales avec des données hiérarchiques. Dans ma vision, la première étape est de trouver des éléments racines des arbres spécifié par DÉBUT À la clause. Cela pourrait être reformulé à la requête suivante:

select * from t where vendor = '1';
VENDOR  CUSTOMER
------------------
1   2
1   7
1   9
1   1

Donc en fait nous avons 4 nœuds racine et 4 arbres distincts. Les prochaines étapes sont de manière itérative évaluer CONNECTER PAR la clause. Imaginez que nous prenons au-dessus de la liste de valeurs de CLIENT et la recherche de leurs descendants:

select * from t where vendor in ('2', '7', '9', '1');
VENDOR  CUSTOMER
------------------
1   2
2   3
2   4
1   7
7   8
1   9
9   10
9   12
1   1 --This one is loop and is not taken to final resultset

Dès que nous l'avons spécifié NOCYCLE, détecté les boucles sont jetés et la ligne précédente qui nous a conduit à la boucle d'enregistrement est marqué comme CONNECT_BY_ISCYCLE = 1.

La troisième étape:

select * from t where vendor in ('2', '3', '4', '7', '8', '9', '10', '12');
VENDOR  CUSTOMER
------------------
2   3
2   4
4   5
4   6
7   8
9   10
10  11
9   12
4   4 --This one is loop

Donc, il va jusqu'à ce qu'il y a au moins un enregistrement de sortie. Cela prend du temps et de la patience, mais les résultats retournés par la requête sont entièrement reproductible et semble tout à fait légitime pour moi. C'est la façon dont Oracle de l'algorithme fonctionne si tout le monde suffit de garder à l'esprit lors de l'écriture des requêtes.

Comment pouvons-nous éviter le cycle sur le nœud de haut niveau? Je suggère d'ajouter d'enregistrement virtuel qui fera de notre nœud de haut niveau non par le haut. Considérez ceci:

insert into t values(null, '1');
select vendor, 
customer, 
level, 
connect_by_isleaf as isleaf, 
connect_by_iscycle as iscycle, 
connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
from t
connect by nocycle
vendor=prior customer
start with vendor is null; --Note the changed condition
Vendor Customer Level   Isleaf  Iscycle  Path
------------------------------------------------------------
1       1       0       1        ~ 1
1       2       2       0       0        ~ 1 ~ 2
2       3       3       1       0        ~ 1 ~ 2 ~ 3
2       4       3       0       1        ~ 1 ~ 2 ~ 4
4       5       4       1       0        ~ 1 ~ 2 ~ 4 ~ 5
4       6       4       0       1        ~ 1 ~ 2 ~ 4 ~ 6
6       9       5       0       0        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9
9       10      6       0       0        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10
10      11      7       1       1        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11
9       12      6       1       0        ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12
1       7       2       0       0        ~ 1 ~ 7
7       8       3       1       0        ~ 1 ~ 7 ~ 8
1       9       2       0       0        ~ 1 ~ 9
9       10      3       0       0        ~ 1 ~ 9 ~ 10
10      11      4       0       0        ~ 1 ~ 9 ~ 10 ~ 11
11      2       5       0       0        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2
2       3       6       1       0        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3
2       4       6       0       1        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4
4       5       7       1       0        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5
4       6       7       1       1        ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6
9       12      3       1       0        ~ 1 ~ 9 ~ 12

Bien sûr, il mignt être pas nécessaire d'ajouter de nouveaux enregistrements à la base de données de production. Plutôt combiner requête véritable table avec des requêtes qui détermine dynamiquement nœuds de niveau supérieur. Quelque chose comme ça (ce qui donne le même résultat que ci-dessus):

delete from t where vendor is null; --Removing previosly inserted record
select vendor, 
customer, 
level, 
connect_by_isleaf as isleaf, 
connect_by_iscycle as iscycle, 
connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path 
from (select vendor, customer from t
union all
select distinct null, vendor from t
where vendor = 1) --Here is your START WITH condition
connect by nocycle
vendor=prior customer
start with vendor is null;

OriginalL'auteur Yaroslav Shabalin

-1

À partir d'un nœud et de connexion d'un nœud à un autre n'est pas la même chose. ISCYCLE ressemble à la clientèle ~ fournisseur de connexions et seulement se connecte une fois par chemin qu'il prend. Si vous dites à oracle pour

START WITH vendor = '1'

il commence réellement à 4 points simultanément:
```
1 ~ 1
1 ~ 2
1 ~ 7
1 ~ 9
```
Ceux-chemin-des recherches sont exécutées en parallèle, et chaque chemin essaie pas de cycle avec son propre chemin. Chaque chemin d'accès ne sait rien sur les autres. Ainsi, le chemin de départ avec 1 ~ 1 ne sais pas pourquoi il devrait s'arrêter en continuant à 2, 7 et 9, parce qu'il n'a pas été là avant. Le NOCYCLE juste interdit de regarder dans 1 une fois de plus. Ainsi, vous pouvez

START WITH (vendor='1' AND customer !='1')

pour éviter de trop nombreux points de départ et/ou d'ignorer toutes les connexions lorsque le vendeur et le client sont les mêmes:
```
CONNECT BY NOCYCLE ( vendor = PRIOR customer AND vendor != customer )
```
Le client !=1 retirez le cycle de drapeau sur 1, rien de plus. Se CONNECTER PAR NOCYCLE ( vendeur = AVANT le client ET le fournisseur != client ) va également produire aucun résultat souhaité.
Vermeul Cela a permis de donner un aperçu en mettant en évidence que la connexion PAR clause et le DÉBUT AVEC clause de fonctionner de façon indépendante. Se CONNECTER EN limite les enregistrements de connexion INDEPENTANTLY de la initilal enregistrements sélectionnés par le DÉPART À la clause. Le " client !='1" dans le DÉBUT AVEC la clause empêche l'ensemble de l'arbre de duplication; cependant, 1 ~ 1 n'est pas signalé comme un cycle (puisqu'il n'existe pas) et je voudrais savoir sur le cycle. le vendor != customer dans la connexion PAR la clause des causes de la 4 ~ 4 cycle pour ne pas être repérés.
J'aime vendeur != client du DÉBUT À la clause et le client!= 1 dans la connexion PAR la clause -- préfèrerais le client != CONNECT_BY_ROOT vendeur, mais qui n'est pas pris en charge -- ce qui empêche tous les cycles qui passent par la racine, MAIS il échoue également à signaler ces cycles. Je veux savoir que ces cycles existent.

OriginalL'auteur Swen Vermeul
-1

La nocycle permet en fait de votre requête pour avoir des cycles, sans que le mot-clé Oracle s'arrêter dès que le cycle est détecté (ORA-01436: CONNECT BY loop in user data). Il vous permet également d'utiliser "CONNECT_BY_ISCYCLE" afin de détecter les endroits où les enfants font le cycle, mais en filtrant les requêtes sur le résultat de cette permettrait de supprimer valide lignes.
Alors, peut-être, vous pouvez utiliser votre connect by nocycle vendor=prior customer AND connect_by_iscycle = 0 dans la condition de la boucle pour éviter toutes les boucles après que les parents de cycles sont-ils détectés? (Je n'ai pas de choses à tester). Ce qui n'empêchera pas la récursivité sur le premier 1~1 chemin.

CONNECT_BY_ISCYCLE=0 ne produira pas le résultat souhaité, plutôt partielles.
cela ne mérite pas un -1. Je pense que ce serait supprimer la totalité de l'arbre de duplication.
Désolé d'être grossier sur ce point.. je cherchais une réponse crédible que non testé l'extrait de code. Donc un -1 sur cette. Lorsque vous testez, vous pouvez voir les résultats. (Je n'ai lu votre commentaire qu'il n'est pas testé)

OriginalL'auteur regilero

Vous devez vous connecter pour publier un commentaire.