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.