Discussion :

[in]

Bonjour,

voilà, je ne sais pas trop si c'est possible mais je souhaite faire une requête récursive qui contient elle-même une requête récursive et remonter des valeurs de la sous-requête dans la "sur"-requête.

Je vais essayer d'être clair :

J'ai une table Projwbs (wbs_id, parent_wbs_id, phase_id)

Je veux récupérer, sous un élément donné, la liste des éléments dont phase_id est non nul et leurs enfants. Je fais donc :

Code :

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

1
2
3
4
5
6
7
8
SELECT pw.wbs_id, phase_id
FROM projwbs pw 
START WITH pw.wbs_id IN (SELECT wbs_id
                                     FROM admuser.projwbs
                                     WHERE phase_id IS NOT NULL 
                                     START WITH wbs_id = 244647
                                     CONNECT BY PRIOR wbs_id = parent_wbs_id )
CONNECT BY PRIOR pw.wbs_id = parent_wbs_id

j'obtiens donc par exemple :

WBS_ID PHASE_ID

244653 1169
244654 1169
244656 1169
244657 ----
244658 1169
244659 ----


Le hic c'est que je voudrais également obtenir la valeur phase_id pour les enfants. En gros, je voudrais que la valeur phase_id du parent soit propagée aux enfants (à la place des ----).

Voyez vous une manière de faire ?

J'ai tenté avec un bloc WITH contenant les parents de créer la requête pour les enfants, mais ça explose le temps d'exécution ....

Merci en tous cas d'avoir lu jusqu'ici ...

[Loyd1974]

Si j'ai bien tout compris, il faut que tu utilises SYS_CONNECT_BY_PATH

plus d'info ici : http://download.oracle.com/docs/cd/B...htm#sthref2194

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT pw.wbs_id, SYS_CONNECT_BY_PATH(phase_id, '|')
FROM projwbs pw 
START WITH pw.wbs_id IN (SELECT wbs_id
                                     FROM admuser.projwbs
                                     WHERE phase_id IS NOT NULL 
                                     START WITH wbs_id = 244647
                                     CONNECT BY PRIOR wbs_id = parent_wbs_id )
CONNECT BY PRIOR pw.wbs_id = parent_wbs_id

[Loyd1974]

Pour garder le même format de sortie (à peut être adapté un petit peu au niveau du substr ou du instr)

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT pw.wbs_id, substr(SYS_CONNECT_BY_PATH(phase_id, '|'),instr(SYS_CONNECT_BY_PATH(phase_id, '|'),'|',1,LEVEL-1))
FROM projwbs pw 
START WITH pw.wbs_id IN (SELECT wbs_id
                                     FROM admuser.projwbs
                                     WHERE phase_id IS NOT NULL 
                                     START WITH wbs_id = 244647
                                     CONNECT BY PRIOR wbs_id = parent_wbs_id )
CONNECT BY PRIOR pw.wbs_id = parent_wbs_id

[in]

oui c'est pas mal du tout. En fait j'avais essayé d'éviter cette solution car je ne sais pas si tout mes utilisateurs sont en 9i (en même temps j'aurais pas dû le mettre dans le titre )

Du coup question subsidiaire :

est ce que le
alter session set “_new_connect_by_enabled”=TRUE;

sur une Oracle 8 est dangereux ? Enfin je veux dire est ce que les dba qui vont installer le produit vont me crier dessus ?

[laurentschneider]

_new_connect_by_enabled ne fonctionnera pas en 8.1.7.3

de quels utilisateurs parles-tu? s'il s'agit de clients oracle qui accèdent une base 9i, alors ça ne devrait poser aucun problème, à mon avis...

[in]

_new_connect_by_enabled ne fonctionnera pas en 8.1.7.3

de quels utilisateurs parles-tu? s'il s'agit de clients oracle qui accèdent une base 9i, alors ça ne devrait poser aucun problème, à mon avis...

En fait au final la requête que je suis en train d'écrire sera intégrée à une macro Excel. La base de donnée cible risque (a priori non mais je ne suis pas sûr à 100 %) d'être une base 8 (genre 5% de nos DB sont des 8 et pas sûr que ce soit en prod).

Je préfèrerais donc savoir à quoi m'attendre si c'est le cas. est ce que proposer la solution précédente va faire criser les dba ? C'est seulement en 8.1.7.3 que ça n'existe pas ? Les versions suivantes c'est ok ?

PS : pour l'histoire, il s'agit d'une requête existante très lourde qui fait déjà criser les dba. Là je suis ne train de la refaire avec du WITH et des fonctions analytiques, et y'a pas photo niveau performances. Par contre j'ai ce point bloquant (mon bloc with est basé dessus, et c'est le noyau de ma requête).

[laurentschneider]

_new_connect_by_enabled ça ne fonctionne qu'en 8.1.7.4. Dans les versions antérieures, ça n'existe pas. Dans 9i, c'est le défaut. Il vaut mieux éviter les paramètres _underscore. A moins que ce soit recommandé par metalink, ce n'est pas supporté.

[in]

du coup,

quelqu'un a t-il une idée de remplacement au sys_connect_by_path ?

Ca va être dur de trouver un truc simple ?

[laurentschneider]

peut-être que je manque quelque chose, mais ne peux-tu pas faire simplement

Code :

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

1
2
3
4
5
6
 
SELECT a.wbs_id, a.phase_id, b.phase_id
FROM projwbs a, 
  (select * from projwbs WHERE b.phase_id IS NOT NULL and b.wbs_id=44647) b
START WITH a.wbs_id = b.wbs_id
CONNECT BY PRIOR a.wbs_id = a.parent_wbs_id

[in]

peut-être que je manque quelque chose, mais ne peux-tu pas faire simplement

non, pas vraiment.

Avec une image ça sera plus parlant (en PJ)

Voici une structure d'élements WBS (les éléments de la table projwbs)

Pour un WBS donné (ici à la racine), je voudrais récupérer tous les noeuds ayant une catégorie (phase_id) et leurs enfants (en propageant le phase_id).

je veux donc obtenir :
WBS_ID PHASE_ID
023 WP
023.1 WP
023.1.1 WP
023.2 WP
023.3 WP
024 WP
024.1 WP

je sais pas si c'est plus clair ?

J'ai essayé ça aussi :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
WITH parents AS 
     (
     SELECT wbs_id, phase_id 
     FROM projwbs 
     WHERE phase_id IS NOT NULL 
     START WITH wbs_id=244647
     CONNECT BY PRIOR wbs_id = parent_wbs_id)
SELECT pw.wbs_id, parents.phase_id
FROM   projwbs pw, parents
START WITH pw.wbs_id = parents.wbs_id
CONNECT BY PRIOR pw.wbs_id = pw.parent_wbs_id

mais bon, au bout de 3 min j'abandonne. (alors qu'avec le sys_connect c'est moins de 1 sec)

[laurentschneider]

peut-on avoir un exemple de données et un exemple de résultat désiré ?

[in]

un mini jeu de test :

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
create table test(wbs_id integer, parent_wbs_id integer, phase_id integer);
 
insert into test values (1,0,null);
insert into test values (2,0,1);
insert into test values (11,1,2);
insert into test values (12,1,null);
insert into test values (21,2,null);
insert into test values (22,2,null);
insert into test values (111,11,null);
insert into test values (121,12,null);
insert into test values (211,21,null);
insert into test values (2111,211,null);
insert into test values (221,22,null);
 
commit;

ce que je voudrais obtenir (en partant du noeud 1):

WBS_ID PHASE_ID

11 2
111 2
12 2
121 2

PS : juste pour info, à la base je fais ça pour gagner en perf par rapport à un existant (basé sur plusieurs requêtes ...)
ma table projwbs fait un peu plus de 300 000 lignes et possède un index sur chacune de ces colonnes ...

[laurentschneider]

essaye avec cette requête et trouve un contre-exemple.

A+
laurent

Code :

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

1
2
3
4
5
6
7
 
select a.wbs_id, b.phase_id
from test a, (
  select * from test 
  where parent_wbs_id=1 and phase_id is not null) b
start with a.parent_wbs_id=1
CONNECT BY PRIOR a.wbs_id = a.parent_wbs_id

[in]

essaye avec cette requête et trouve un contre-exemple.

dans ce cas ça marche ... mais si je fais

insert into test values (11,1,null); -- à la place de (11,1,2)
insert into test values (110,11,2);
inserr into test values (1101,110,null);
insert into test values (11011,1101,null);

là à partir du noeud 1, je ne trouve plus rien. Je crois que j'ai vraiment besoin d'une requête hiérachique pour trouver le premier fils de "1" qui est un phase_id

par contre avec ceci :

Code :

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

1
2
3
4
5
6
7
SELECT a.wbs_id, b.phase_id
FROM test a, (SELECT * FROM test --par contre j'imagine qu'il faut que je limite au premier résultat ...
              WHERE phase_id IS NOT NULL
              START WITH wbs_id = 1
              CONNECT BY prior wbs_id = parent_wbs_id) b
start WITH a.wbs_id=b.wbs_id
CONNECT BY PRIOR a.wbs_id = a.parent_wbs_id

c'est ok ... sauf au niveau perf. Au bout de 5 min toujours aucun résultat ...

bon ben je crois que c'est mort ... Merci en tous cas !

[laurentschneider]

effectivement en 8i ça devient complexe, il vaut peut-être mieux employer une procédure plsql.
evtl

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT WBS_ID, max(PHASE_ID) keep (DENSE_RANK FIRST ORDER BY nvl2(phase_id,1,2),L) PHASE_ID
from (
SELECT a.wbs_id, b.phase_id, b.l
FROM test a, (
  SELECT wbs_id, phase_id, level l
  FROM test a
  CONNECT BY PRIOR wbs_id = a.parent_wbs_id
  START WITH WBS_ID=1) b
START WITH a.wbs_id =b.wbs_id 
CONNECT BY PRIOR a.wbs_id = a.parent_wbs_id
)
GROUP BY WBS_ID

[in]

c'est très gentil de ta part de passer du temps comme ça ...

Je ne peux pas tester tout de suite car la base est très sollicitée ... Je te tiens au courant, en tous cas sur la table test, c'est quasi bon mais j'ai peur que les perfs soient insuffisantes (ce n'est qu'un chti bout de ma requete finale ).

C'est tout de même une solution qui fonctionne !

Merci à toi Ô Dieu du SQL !

[EDIT] je ne retire pas mes compliments mais en fait là requête que j'ai donnée en dernier correspond plus à ce que je veux. Hélas les perfs sont nulles ...