[10g] Bug connect by nocycle - Requete hierarchique

[nuke_y]

Bonjour à tous.

Je viens vers vous concernant un problème de comportement (un bug ?) dans les requêtes hierarchiques sur des données contenant des boucles.

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Soit la table suivante :

DROP TABLE "OBJECT_LINK";
CREATE TABLE "OBJECT_LINK"
( 
  OBJECT VARCHAR2(10) NOT NULL -- ex: 'MYSCHEMA.MYTABLE.MYCOLUMN'
 ,LINKED_OBJECT VARCHAR2(10) NOT NULL -- ex: 'MYSCHEMA.MYTABLE.MYCOLUMN'
 ,TYPE VARCHAR2(10) NOT NULL -- ex: 'refers'
);

Cas 1

A-->B
^   |
|   v
D<--C

DELETE FROM OBJECT_LINK;
 
INSERT INTO OBJECT_LINK VALUES ('A', 'B', 'refers');
INSERT INTO OBJECT_LINK VALUES ('B', 'C', 'refers');
INSERT INTO OBJECT_LINK VALUES ('C', 'D', 'refers');
INSERT INTO OBJECT_LINK VALUES ('D', 'A', 'refers');
 
SELECT connect_by_root OBJECT, level, TYPE, OBJECT, LINKED_OBJECT
FROM "OBJECT_LINK"
connect BY OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

On obtient une erreur car il y a une boucle dans les données :

ERROR:
ORA-01436: boucle CONNECT BY dans les données utilisateur

Il faut utiliser le mot clé "nocycle" :

SELECT connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
FROM "OBJECT_LINK"
connect BY nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
---------- ---------- ---------- ---------- ----------
A                   1 A          refers     B
A                   2 B          refers     C
A                   3 C          refers     D
A                   4 D          refers     A

Les 4 références sont bien données. Parfait


Cas 2

A-->B-->E
^   |   |
|   v   v
D<--C<--F

DELETE FROM OBJECT_LINK;
 
INSERT INTO OBJECT_LINK VALUES ('A', 'B', 'refers');
INSERT INTO OBJECT_LINK VALUES ('B', 'C', 'refers');
INSERT INTO OBJECT_LINK VALUES ('C', 'D', 'refers');
INSERT INTO OBJECT_LINK VALUES ('D', 'A', 'refers');
INSERT INTO OBJECT_LINK VALUES ('B', 'E', 'refers');
INSERT INTO OBJECT_LINK VALUES ('E', 'F', 'refers');
INSERT INTO OBJECT_LINK VALUES ('F', 'C', 'refers');
 
SELECT connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
FROM "OBJECT_LINK"
connect BY nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
---------- ---------- ---------- ---------- ----------
A                   1 A          refers     B
A                   2 B          refers     C
A                   3 C          refers     D
A                   4 D          refers     A
A                   2 B          refers     E
A                   3 E          refers     F
A                   4 F          refers     C
A                   5 C          refers     D
A                   6 D          refers     A

Les 4 references sont bien données mais il y a 2 doublons ce qui prouve qu'Oracle ne s'arrête pas dès qu'il commence la boucle :
3 C refers D
5 C refers D
4 D refers A
6 D refers A

Ces "doublons" s'expliquent par le fait qu'ils n'ont pas lieu dans le même chemin. Le chemin a donc de l'importance. Si on présente la requête en arbre cela se comprend :

SELECT  lpad(' ', level-1) || level ||' '|| OBJECT ||' '|| TYPE ||' '|| LINKED_OBJECT
FROM "OBJECT_LINK"
connect BY nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

1 A refers B
 2 B refers C
  3 C refers D
   4 D refers A
 2 B refers E
  3 E refers F
   4 F refers C
    5 C refers D
     6 D refers A

Cas 3

A-->C<--D-->F-->L
|   |   ^   |   ^
v   v   |   v   |
B   H-->I   J-->K
|           |   |
v           v   v
E           G   M

cf ici

DELETE FROM OBJECT_LINK;
 
INSERT INTO OBJECT_LINK VALUES ('A', 'C', 'refers');
INSERT INTO OBJECT_LINK VALUES ('A', 'B', 'refers');
INSERT INTO OBJECT_LINK VALUES ('B', 'E', 'refers');
INSERT INTO OBJECT_LINK VALUES ('C', 'H', 'refers');
INSERT INTO OBJECT_LINK VALUES ('H', 'I', 'refers');
INSERT INTO OBJECT_LINK VALUES ('I', 'D', 'refers');
INSERT INTO OBJECT_LINK VALUES ('D', 'F', 'refers');
INSERT INTO OBJECT_LINK VALUES ('D', 'C', 'refers');
INSERT INTO OBJECT_LINK VALUES ('F', 'J', 'refers');
INSERT INTO OBJECT_LINK VALUES ('J', 'K', 'refers');
INSERT INTO OBJECT_LINK VALUES ('J', 'G', 'refers');
INSERT INTO OBJECT_LINK VALUES ('K', 'L', 'refers');
INSERT INTO OBJECT_LINK VALUES ('F', 'L', 'refers');
INSERT INTO OBJECT_LINK VALUES ('K', 'M', 'refers');
 
SELECT connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
FROM "OBJECT_LINK"
connect BY nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
---------- ---------- ---------- ---------- ----------
A                   1 A          refers     C
A                   2 C          refers     H
A                   3 H          refers     I
A                   4 I          refers     D
A                   5 D          refers     F
A                   6 F          refers     J
A                   7 J          refers     K
A                   8 K          refers     L
A                   8 K          refers     M
A                   7 J          refers     G
A                   6 F          refers     L
A                   1 A          refers     B
A                   2 B          refers     E

On a perdu D refers C

Ceci prouve que la gestion des boucles avec une requête hierarchique n'est pas du tout au point, même en 10g puisqu'on peut perdre des relations (alors que la plupart des cas sont correctement gérés).

En conclusion, si je ne trouve pas une méthode pour contourner ce comportement je vais être obligé d'abandonner les requêtes hierarchiques pour faire ma récursivité en code applicatif (toujours pratique quand on utilise un ETL ou un outil de requêtage).

Donc si quelqu'un s'est déjà frotté à ce problème, a du temps pour m'aider ou a ses entrées chez Oracle, merci du coup de main.

[GoLDoZ]

Sujet très intéressant.

[laurentschneider]

je suis assez d'accord que D refere C est manquant. A mon sens ça vaut le coup d'ouvrir un bug sur Metalink.

Quant à l'implémentation propre au chemin, c'est bien ça le bug qui fait que CONNECT BY LEVEL<10 fonctionne (à tort ). J'ai ouvert une SR à ce sujet il y a fort longtemps ;-)

[nuke_y]

Salut Laurent. C'est quoi cette histoire de LEVEL < 10 ? Tu peux développer stp ?

Merci

[Waldar]

C'est l'astuce pour générer n lignes à la volée :

SELECT level FROM dual
connect BY level <= 10

LEVEL
1
2
3
4
5
6
7
8
9
10

[Waldar]

Pour votre soucis, je ne sais pas si c'est viable dans votre cas pratique mais si vous pouvez vous passez des informations level et connect_by_root vous pouvez obtenir tous les résultats ainsi :

SELECT DISTINCT OBJECT, TYPE, LINKED_OBJECT
FROM OBJECT_LINK
connect BY nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT IN (SELECT object FROM OBJECT_LINK
                      GROUP BY object HAVING count(*) > 1)

[nuke_y]

Hmmm disons que cette méthode va me permettre d'avoir toutes les relations, mais pas toutes les relations en partant d'un point donné.

J'utilise déjà cette méthode pour "mettre à plat" toutes mes relations à partir de tous les objets possibles, mais je perds toujours des relations dans certains cas de boucles.

PS : ok je connaissais pas l'astuce du DUAL connect by

[laurentschneider]

Envoyé par nuke_y

PS : ok je connaissais pas l'astuce du DUAL connect by

Et bien tant mieux

Cette astuce est une perversion du système de hiérarchie et elle a l'avantage et l'inconvénient d'être performante. Mais ce n'est pas une syntaxe légale.

En fait, il y a boucle dès que la valeur courante existe dans la liste des "PRIOR" précédentes. Cependant si tu fais CONNECT BY 1=1, tu as une boucle infinie, mais comme tu n'as pas de PRIOR, la boucle n'est pas détectée. Différentes astuces plus ou moins réussies ont permis d'abuser cette technique pour créer des lignes. Il y a bien sûr plein de bugs et ce n'est pas supporté à mon humble avis.

Pour revenir à la relation manquante, je vais tenter de faire ouvrir un bug sur Metalink.

A+
Laurent

PS: bien sûr j'update dès que j'ai le numéro du bug...

[laurentschneider]

j'ai ajouté and prior sys_guid() is not null dans la clause connect by, suite à un commentaire sur mon blog.

SELECT connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
FROM "OBJECT_LINK"
connect BY nocycle OBJECT = prior LINKED_OBJECT
AND prior sys_guid() IS NOT NULL
START WITH OBJECT = 'A';

CONNECT_BY      LEVEL OBJECT     TYPE       LINKED_OBJ
---------- ---------- ---------- ---------- ----------
A                   1 A          refers     B         
A                   2 B          refers     E         
A                   1 A          refers     C         
A                   2 C          refers     H         
A                   3 H          refers     I         
A                   4 I          refers     D         
A                   5 D          refers     C         ====> BINGO
A                   5 D          refers     F         
A                   6 F          refers     J         
A                   7 J          refers     G         
A                   7 J          refers     K         
A                   8 K          refers     L         
A                   8 K          refers     M         
A                   6 F          refers     L

[laurentschneider]

mais c'est probablement une mauvaise idée, Oracle risque de boucler infiniment dans certains cas

WITH t AS
     (SELECT 1 x, 2 y
        FROM DUAL
      UNION ALL
      SELECT 2, 2
        FROM DUAL)
SELECT * FROM t
CONNECT BY NOCYCLE x = PRIOR y AND PRIOR SYS_GUID () IS NOT NULL
START WITH x = 1;

X Y
- -
1 2
2 2
2 2
2 2
2 2
2 2
...
 

[laurentschneider]

peut-être que

CONNECT BY NOCYCLE OBJECT = PRIOR linked_object
            AND OBJECT != PRIOR object

peut marcher, parfois. Bien sûr le mieux serait de harceller Oracle jusqu'à ce qu'ils fixent leur implémentations de NOCYCLE