[10g] Bug connect by nocycle - Requete hierarchique

Version imprimable

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.

Citation:

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

Soit la table suivante :

Code:

1
2
3
4
5
6
7
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

Code:

1
2
3
4
A-->B
^   |
|   v
D<--C

Code:

1
2
3
4
5
6
7
8
9
10
11
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 :

Citation:

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

Il faut utiliser le mot clé "nocycle" :

Code:

1
2
3
4
select connect_by_root OBJECT, level, OBJECT, TYPE, LINKED_OBJECT
from "OBJECT_LINK"
connect by nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

Code:

1
2
3
4
5
6
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

Code:

1
2
3
4
A-->B-->E
^   |   |
|   v   v
D<--C<--F

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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';

Code:

1
2
3
4
5
6
7
8
9
10
11
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 :

Code:

1
2
3
4
select  lpad(' ', level-1) || level ||' '|| OBJECT ||' '|| TYPE ||' '|| LINKED_OBJECT
from "OBJECT_LINK"
connect by nocycle OBJECT = prior LINKED_OBJECT
START WITH OBJECT = 'A';

Code:

1
2
3
4
5
6
7
8
9
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

Code:

1
2
3
4
5
6
7
A-->C<--D-->F-->L
|   |   ^   |   ^
v   v   |   v   |
B   H-->I   J-->K
|           |   |
v           v   v
E           G   M

cf ici

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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';

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

13/05/2009, 10h11
GoLDoZ

Sujet très intéressant. ;)
13/05/2009, 12h57
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 :aie:). J'ai ouvert une SR à ce sujet il y a fort longtemps ;-)
14/05/2009, 10h22
nuke_y

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

Merci
14/05/2009, 10h47
Waldar
C'est l'astuce pour générer n lignes à la volée :
Code:

1 2 select level from dual connect by level <= 10
Code:

1 2 3 4 5 6 7 8 9 10 11 LEVEL 1 2 3 4 5 6 7 8 9 10

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 :
Code:

1 2 3 4 5 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)

14/05/2009, 11h44
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
14/05/2009, 12h32
laurentschneider

Citation:

Envoyé par nuke_y

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

Et bien tant mieux :king:

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...

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

Code:

1
2
3
4
5
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';

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

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

Code:

1
2
3
4
5
6
7
8
9
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;

Code:

26/05/2009, 14h27
laurentschneider
peut-être que
Code:

1 2 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 :aie:
19/05/2010, 22h19
laurentschneider

j'ai écrit un article sur mon blog quant à l'implémentation en 11gR2 des requêtes hiérarchiques

http://laurentschneider.com/wordpres...rsive-cte.html