[Oracle 9] [SQL] Optimisation d'une requete

Version imprimable

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 :

Citation:

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:

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:

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:

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.

15/02/2010, 15h24
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...
15/02/2010, 15h36
Waldar

C'est mal modélisé tout simplement.
Les hierarchies se font avec des id parents / enfants.
15/02/2010, 15h40
GAEREL

Citation:

Envoyé par nuke_y

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

Citation:

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

Citation:

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

Citation:

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

Certes... mais encore ?
Merci d'avance

Essayez cette requête-ci :

Code:

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

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:

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:

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.

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

Code:

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

16/02/2010, 11h58
mnitu

Citation:

Envoyé par Waldar

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 :ccool:. Malheureusement, je ne pense pas que IGNORE NULLS passe en 9i. Mais, encore une fois j'ai bien aimé ta solution.

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:

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 !

16/02/2010, 13h37
GAEREL

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

A suivre...

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

Code:

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. :bravo: :hola: :applo:
:lahola:

Je n'aurai qu'un mot : MERCI.

Fred

16/02/2010, 15h43
Waldar

La dernière pourrait être encore plus rapide !