Discussion :

[GAEREL]

Bonjour à tous.

J'ai la hiérarchie de données suivante :
"Phases" contenant des "Activités" contenant elles même des "Taches".
Le tout est représenté dans une table de la manière suivante :

CREATE TABLE TACHES (
id_unique NUMBER(10,0) NULL,
nom VARCHAR2(192) NULL,
niveau NUMBER(5,0) NULL,
sequence NUMBER(5,0) NULL
)

Soit par exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
ID_UNIQUE   NOM       NIVEAU   SEQUENCE
1           Phase1        1      1
2           Activite1     2      2
3           Tache1        3      3
4           Tache2        3      4
5           Activite2     2      5
6           Tache3        3      6
7           Phase2        1      7
8           Activite3     2      8
9           Tache4        3      9

Je souhaite représenter la "liste des taches" sous la forme :

Code :

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

1
2
3
4
5
6
 
ID_UNIQUE PHASE  ACTIVITE  TACHE
3         Phase1 Activite1 Tache1
4         Phase1 Activite1 Tache2
6         Phase1 Activite2 Tache3
9         Phase2 Activite3 Tache4

Ma solution est la suivante :

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
 
SELECT T.id_unique,
       P.nom,
       A.nom,
       T.nom
FROM   TACHES T, TACHES P,TACHES A
WHERE  T.niveau = 3
AND    A.sequence = ( SELECT MAX (sequence) 
			FROM TACHES
			WHERE niveau = 2 
			AND sequence < T.sequence )
AND    P.sequence = ( SELECT MAX (sequence) 
			FROM TACHES
			WHERE niveau = 1 
			AND sequence < T.sequence )

Ce code me donne le bon résultat mais ce n'est pas vraiment rapide !!!! (j'ai en réalité plusieurs centaines de milliers de lignes Taches...)

Comment optimiser une telle requête ?
Merci de votre aide
Fred.

[nuke_y]

Drôle de modélisation...

Question : la séquence démarre à 1 pour une nouvelle tâche et s'incrémente pour toute la branche ? Parce que là dans votre exemple on dirait que Id_unique = séquence.

Sinon en terme de solution, une requête hiérarchique et/ou des fonctions analytiques peut-être...

[Waldar]

C'est mal modélisé tout simplement.
Les hierarchies se font avec des id parents / enfants.

[GAEREL]

Drôle de modélisation...

Je confirme mais je n'y puis rien... ce sont les tables d'un progiciel sur lesquelles je n'ai aucun pouvoir

Question : la séquence démarre à 1 pour une nouvelle tâche et s'incrémente pour toute la branche ? Parce que là dans votre exemple on dirait que Id_unique = séquence.

En fait le modèle est plus compliqué que cela.
La colonne id_unique est bien la cle unique de la table (on s'en douterait)
La séquence démarre à 1 et s'incrémente pour toutes les phases/activites/taches d'un projet...
le vrai modèle est celui-ci

CREATE TABLE TACHES (
id_unique NUMBER(10,0) NULL,
id_projet NUMBER(10,0) NULL,
nom VARCHAR2(192) NULL,
niveau NUMBER(5,0) NULL,
sequence NUMBER(5,0) NULL
... and so on...
)

ID_UNIQUE - ID_PROJET - NOM ------ NIVEAU - SEQUENCE ....
1 ---------- 1 ---------- Phase1 ---- 1 ------- 1
2 ---------- 1 ---------- Activite1 -- 2 ------- 2
3 ---------- 1 ---------- Tache1 --- 3 ------- 3
4 ---------- 1 ---------- Tache2 --- 3 ------- 4
5 ---------- 1 ---------- Activite2 -- 2 ------- 5
6 ---------- 1 ---------- Tache3 --- 3 ------- 6
7 ---------- 1 ---------- Phase2 ---- 1 ------- 7
8 ---------- 1 ---------- Activite3 -- 2 ------- 8
9 ---------- 1 ---------- Tache4 --- 3 ------- 9
10 --------- 2 ---------- Phase1 ---- 1 ------- 1
11 --------- 2 ---------- Activite1 -- 2 ------- 2
12 --------- 2 ---------- Tache1 --- 3 ------- 3
13 --------- 2 ---------- Tache2 --- 3 ------- 4

Sinon en terme de solution, une requête hiérarchique et/ou des fonctions analytiques peut-être...

Certes... mais encore ?
Merci d'avance

[Waldar]

Essayez cette requête-ci :

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
22
23
24
  SELECT T.id_unique,
         max(P.nom) keep (dense_rank first order by P.sequence desc) as Phase,
         max(A.nom) keep (dense_rank first order by A.sequence desc) as Activite,
         T.nom as Tache
    FROM TACHES T
         inner join TACHES A
           on A.id_projet = T.id_projet
          and A.sequence <  T.sequence
         inner join TACHES P
           on P.id_projet = A.id_projet
          and P.sequence <  A.sequence
   WHERE T.niveau = 3
     AND A.niveau = 2
     AND P.niveau = 1
GROUP BY T.id_unique, T.nom
ORDER BY T.id_unique ASC;
 
ID_UNIQUE	PHASE	ACTIVITE	TACHE
3		Phase1	Activite1	Tache1
4		Phase1	Activite1	Tache2
6		Phase1	Activite2	Tache3
9		Phase2	Activite3	Tache4
12		Phase1	Activite1	Tache1
13		Phase1	Activite1	Tache2

[mnitu]

Comme votre hiérarchie semble assez rigide je vous propose une autre solution mais qui implique les functions pipelined donc via PL/SQL

Regardez d'abord la requête

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
22
23
24
25
26
27
28
29
30
31
 
SQL> With data As (
  2  Select 1 as id,'Phase1' As Nom, 1 As Niveau, 1 as Seq from dual union all
  3  Select 2,'Activite1',2,2 from dual union all
  4  Select 3,'Tache1',3,3 from dual union all
  5  Select 4,'Tache2',3,4 from dual union all
  6  Select 5,'Activite2',2,5 from dual union all
  7  Select 6,'Tache3',3,6 from dual union all
  8  Select 7,'Phase2',1,7 from dual union all
  9  Select 8,'Activite3',2,8 from dual union all
 10  Select 9,'Tache4',3,9 from dual
 11  )
 12  Select Niveau,
 13         Case When Niveau = 1 Then Nom End Phase,
 14         Case When Niveau = 2 Then Nom End Actvite,
 15         Case When Niveau = 3 Then Nom End Tache
 16    from data
 17  order by seq
 18  /
 
    NIVEAU PHASE     ACTVITE   TACHE
---------- --------- --------- ---------
         1 Phase1
         2           Activite1
         3                     Tache1
         3                     Tache2
         2           Activite2
         3                     Tache3
         1 Phase2
         2           Activite3
         3                     Tache4

Via une fonction pipelined vous pourriez produire vos données pour le niveau 3 en prenant en compte les ruptures niveau 1 (projet) et 2 (activité); quelque chose de type

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Loop
  Fetch Nom into l_nom;
  ...
  If niveau 1 Then
     l_project := l_nom
  ElsIf niveau 2 Then
    l_activite := l_nom
  Else
   pipe row(res(l_project, l_activite, l_nom))
  End If;
  ...
End Loop

De cette manière la table sera balayée une seule fois.

[Waldar]

En reprenant l'idée de mnitu, on peut arriver à la solution en SQL pur, toujours avec un seul table scan :

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
WITH SR AS
(
SELECT id_unique,
       last_value(Case Niveau When 1 Then Nom End ignore nulls) over(partition by id_projet order by sequence asc) Phase,
       last_value(Case Niveau When 2 Then Nom End ignore nulls) over(partition by id_projet order by sequence asc) Activite,
       Case Niveau When 3 Then Nom End Tache
  FROM TACHES
)
SELECT id_unique, Phase, Activite, Tache
  FROM SR
 WHERE Tache IS NOT NULL;
 
ID_UNIQUE	PHASE	ACTIVITE	TACHE
3		Phase1	Activite1	Tache1
4		Phase1	Activite1	Tache2
6		Phase1	Activite2	Tache3
9		Phase2	Activite3	Tache4
12		Phase1	Activite1	Tache1
13		Phase1	Activite1	Tache2

[mnitu]

En reprenant l'idée de mnitu, on peut arriver à la solution en SQL pur, toujours avec un seul table scan :
...
last_value(Case Niveau When 1 Then Nom End ignore nulls) over(partition by id_projet order by sequence asc) Phase,
...

C'est . Malheureusement, je ne pense pas que IGNORE NULLS passe en 9i. Mais, encore une fois j'ai bien aimé ta solution.

[Waldar]

Merci mnitu
Dommage pour le IGNORE NULLS, c'est vrai que cet attribut n'a l'air d'être arrivé qu'en 10g.

On doit pouvoir contourner en utilisant un MAX sur la séquence.
En y concaténant le nom, ça devrait le faire :

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
WITH SR AS
(
SELECT id_unique,
       substr(max(Case Niveau When 1 Then to_char(sequence, 'fm00000') || nom End) over(partition by id_projet order by sequence asc), 6) as Phase,
       substr(max(Case Niveau When 2 Then to_char(sequence, 'fm00000') || nom End) over(partition by id_projet order by sequence asc), 6) as Activite,
       Case Niveau When 3 Then Nom End as Tache
  FROM TACHES
)
SELECT id_unique, Phase, Activite, Tache
  FROM SR
 WHERE Tache IS NOT NULL;
 
ID_UNIQUE	PHASE	ACTIVITE	TACHE
3		Phase1	Activite1	Tache1
4		Phase1	Activite1	Tache2
6		Phase1	Activite2	Tache3
9		Phase2	Activite3	Tache4
12		Phase1	Activite1	Tache1
13		Phase1	Activite1	Tache2

Ici j'ai supposé que la séquence ne dépasse pas 99999.
C'est moins souple que la solution précédente, mais ça fonctionne en 9i !

[GAEREL]

Merci à tous, je suis en cours de test sur vos différentes solutions

A suivre...

[GAEREL]

Impressionnant...
En utilisant la première solution de Waldar

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
 SELECT T.id_unique,
         max(P.nom) keep (dense_rank first ORDER BY P.sequence DESC) AS Phase,
         max(A.nom) keep (dense_rank first ORDER BY A.sequence DESC) AS Activite,
         T.nom AS Tache
    FROM TACHES T
         INNER JOIN TACHES A
           ON A.id_projet = T.id_projet
          AND A.sequence <  T.sequence
         INNER JOIN TACHES P
           ON P.id_projet = A.id_projet
          AND P.sequence <  A.sequence
   WHERE T.niveau = 3
     AND A.niveau = 2
     AND P.niveau = 1
GROUP BY T.id_unique, T.nom
ORDER BY T.id_unique ASC;

Je passe de 52 à 3 minutes.


Je n'aurai qu'un mot : MERCI.

Fred

[Waldar]

La dernière pourrait être encore plus rapide !