Discussion :

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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

Il faut utiliser le mot clé "nocycle" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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)