Utilisation fonctions analytiques

Bonjour,
je travaille sous oracle10g et j'ai dû écrire une requête assez longue et couteuse basé sur plusieurs union pour traiter un cas, un collègue m'a dis que tout ce que j'ai écris pouvais surement être simplifier en une seule requête en utilisant les fonctions analytiques, j'ai essayé mais là je peine à y arriver et me demande sérieusement si cela est possible. Voici la description de mon problème :
je gère une sorte de gestion de version de fichiers, ceux-ci sont classés par famille (type de fichier) , chaque fichier est versionné (à un instant T on génère une nouvelle version pour chaque type).
Certains type de fichiers utilisent des variantes : pour un même version je dispose de plusieurs déclinaisons ou variantes, donc à un instant T après une génération, voici la liste des fichier à jour :
Code:

1 2 3 4 5 6 7 Fichiers_UptoDate de la vue V_LAST_VERSION_FILES : TypeFic NomFic Version Variante UseVariante -------------------------------------------------------------------------------- A Fic1 5 N B Fic2 6 1 O B Fic3 7 2 O
Dans une application, je créée une configuration de fichier (groupe) qui doit regrouper 2 fichiers de type A et 3 de type B; donc à l'instant T voici le contenu d'une de ces configurations (certains fichiers sont déjà assigné et on peut donc voir leur nom et d'autre pas encore ont un nom de fichier null):
Code:

1 2 3 4 5 6 7 8 9 Fichiers_config1 de la vue V_CONFIG1 : TypeFic NomFic Version Variante UseVariante --------------------------------------------------------------------- A N (>fichier pas encore assigné) B O (>fichier pas encore assigné) A Fic0 0 N B Ficx 1 1 O B Ficy 1 2 O
Dans mon application, je désire maintenant écrire une requête qui puisse me donner :

le numéro de version de la vue V_LAST_VERSION_FILES pour chaque fichier du même type de la vue V_CONFIG1 ne possédant pas de fichier assigné et n'utilisant pas de variante.
null pour le numéro de version de chaque ligne de la vue V_CONFIG1 ne possédant pas de fichier assigné et utilisant une variante.
le numéro de version de la vue V_LAST_VERSION_FILES pour chaque fichier du même type de la vue V_CONFIG1 possédant un fichier assigné et n'utilisant pas de variante.
le numéro de version de la vue V_LAST_VERSION_FILES pour chaque fichier du même type et avec le même numéro de variante de la vue V_CONFIG1 possédant un fichier assigné et utilisant une variante.

Ma requête devrait donc me renvoyer le résultat suivant :
Code:

1 2 3 4 5 6 7 8 9 Résultat attendu : TypeFic NomFic Version Variante UseVariante VersionUpToDate ---------------------------------------------------------------------------------- A N 5 B O null A Fic0 0 N 5 B Ficx 1 1 O 6 B Ficy 1 2 O 7
J'ai essayé de simplifié pour l'exemple mais les deux vues contiennent plusieurs milliers de lignes. J'ai réussis à écrire ma requête en faisant plusieurs union pour chacun des cas à traiter, mais la requête est extrêmement lente et j'aimerais savoir si le résultat peut être obtenu en une seule requête en utilisant les fonctions analityques (j'essaye mais je n'arrête pas de rencontrer des difficulutés notamment sur les jointures :? )...
N'hésitez pas si vous avez des questions!
Merci d'avance, Lek.

La demande peut se simplifier en
* NULL pour le numéro de version de chaque ligne de la vue V_CONFIG1 ne possédant pas de fichier assigné et utilisant une variante.

* le numéro de version de la vue V_LAST_VERSION_FILES pour chaque fichier du même type et avec le même numéro de variante (ou les 2 variantes NULL)

Soit on peut tester la valeur NULL avec une valeur de variante impossible à avoir (-1 par exemple, ou 99 pour un number(1)), soit on teste le NULL avec un OR

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with V_LAST_VERSION_FILES as
(					select 'A' TypeFic, 'Fic1' Nomfic, 5 Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL select 'B' TypeFic, 'Fic2' Nomfic, 6 Version, 1 variante, 'O' usevariante FROM DUAL
UNION ALL select 'B' TypeFic, 'Fic3' Nomfic, 7 Version, 2 variante, 'O' usevariante FROM DUAL
),
V_CONFIG1 as
(					SELECT 'A' TypeFic, '' Nomfic, NULL Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL select 'B' TypeFic, '' Nomfic, NULL Version, NULL variante, 'O' usevariante FROM DUAL
UNION ALL select 'A' TypeFic, 'Fic0' Nomfic, 0 Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL select 'B' TypeFic, 'Ficx' Nomfic, 1 Version, 1 variante, 'O' usevariante FROM DUAL
UNION ALL select 'B' TypeFic, 'Ficy' Nomfic, 1 Version, 2 variante, 'O' usevariante FROM DUAL
)
select c.TypeFic, c.Nomfic, c.version, c.variante, c.usevariante,
		(CASE
			when c.nomfic IS NULL and c.useVariante = 'O' Then NULL
			ELSE
					(SELECT version
				 FROM V_LAST_VERSION_FILES v
				 WHERE v.typefic = c.typefic
				 AND NVL(v.variante,-1) = nvl(c.variante, -1)
				 ) 
		 end) versionUptoDate
FROM v_config1 c

Code:

1
2
3
4
5
6
TYPEFIC	NOMFIC	VERSION	VARIANTE	USEVARIANTE	VERSIONUPTODATE
A	- 	- 	- 	N	5
B	- 	- 	- 	O	-
A	Fic0	0	- 	N	5
B	Ficx	1	1	O	6
B	Ficy	1	2	O	7

Voici 2 tests possibles

Code:

1
2
3
4
5
6
7
8
9
10
SELECT version
FROM V_LAST_VERSION_FILES v
WHERE v.typefic = c.typefic
AND NVL(v.variante,-1) = nvl(c.variante, -1)
 
SELECT version
FROM V_LAST_VERSION_FILES v
WHERE v.typefic = c.typefic
AND ( v.variante = c.variante 
  OR (v.variante IS NULL AND c.variante IS NULL))

Merci mille fois, c'est exactement le résultat que j'attendais. Je me suis tout de même aperçu que je pouvais enlever le premier case when en conservant le même résultat :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
WITH V_LAST_VERSION_FILES AS
(					SELECT 'A' TypeFic, 'Fic1' Nomfic, 5 Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Fic2' Nomfic, 6 Version, 1 variante, 'O' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Fic3' Nomfic, 7 Version, 2 variante, 'O' usevariante FROM DUAL
),
V_CONFIG1 AS
(					SELECT 'A' TypeFic, '' Nomfic, NULL Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, '' Nomfic, NULL Version, NULL variante, 'O' usevariante FROM DUAL
UNION ALL SELECT 'A' TypeFic, 'Fic0' Nomfic, 0 Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Ficx' Nomfic, 1 Version, 1 variante, 'O' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Ficy' Nomfic, 1 Version, 2 variante, 'O' usevariante FROM DUAL
)
SELECT c.TypeFic, c.Nomfic, c.version, c.variante, c.usevariante,
 
					(SELECT version
				 FROM V_LAST_VERSION_FILES v
				 WHERE v.typefic = c.typefic
				 AND NVL(v.variante,-1) = nvl(c.variante, -1)
				 ) 
		 versionUptoDate
FROM v_config1 c

Partant de là je peux aussi récrire la requête sans un select imbriqué en faisant la jointure que vous avez effectué (mais en la laissant ouverte pour garder l'ensemble des résultats) ;-)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
WITH V_LAST_VERSION_FILES AS
(SELECT 'A' TypeFic, 'Fic1' Nomfic, 5 Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Fic2' Nomfic, 6 Version, 1 variante, 'O' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Fic3' Nomfic, 7 Version, 2 variante, 'O' usevariante FROM DUAL
),
V_CONFIG1 AS
(SELECT 'A' TypeFic, '' Nomfic, NULL Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, '' Nomfic, NULL Version, NULL variante, 'O' usevariante FROM DUAL
UNION ALL SELECT 'A' TypeFic, 'Fic0' Nomfic, 0 Version, NULL variante, 'N' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Ficx' Nomfic, 1 Version, 1 variante, 'O' usevariante FROM DUAL
UNION ALL SELECT 'B' TypeFic, 'Ficy' Nomfic, 1 Version, 2 variante, 'O' usevariante FROM DUAL
)
SELECT c.TypeFic, c.Nomfic, c.version, c.variante, c.usevariante,v.version versionUptoDate
FROM v_config1 c,V_LAST_VERSION_FILES v
WHERE 
v.typefic (+)= c.typefic
AND NVL(v.variante(+),-1) = nvl(c.variante, -1)

J'ai tout de même un question à propos des deux écritures : "nvl" et celle utilisant le "or", y en a t il une de moins performante que l'autre ou sont-elles équivalentes ?
D'autre part l'écriture n'a pas du tout necessité l'utilisation des requêtes analytiques : est-ce qu'elles sont indaptatées à cet exercice ?
En tous cas encore merci pour votre aide. Si, si...
Cordialement,
Lek.

Entre le NVL et le OR, l'EXPLAIN PLAN t'en dira d'avantage.
Le nvl empêche l'utilisation d'index sur la colonne (sauf si tu crées un index de fonction), et il y a plein d'autres cas possibles. Donc faut tester les 2 :)

Les fonctions analytiques sont en effet inadaptées.
Une fonction analytique te permet de faire des opérations au sein d'un regroupement des lignes affichées (exemple le rownum mais par type de fichier)

En l'occurrence vous n'avez pas besoin du test complet sur le NVL.
Préférez aussi la syntaxe avec les jointures ANSI, c'est plus lisible :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
    c.TypeFic,
    c.Nomfic,
    c.version,
    c.variante,
    c.usevariante,
    v.version as versionUptoDate
from
    V_CONFIG1 C
    LEFT OUTER JOIN V_LAST_VERSION_FILES V
      ON V.typefic = C.typefic
     AND (v.variante = c.variante OR v.variante is null)
ORDER BY
    C.nomfic ASC nulls first,
    C.typefic ASC;

Merci pour votre aide, messieurs.
@ bientôt,
Lek.