Bonjour,

Je veux utiliser la clause WITH pour une requête présente plusieurs fois dans mon SELECT. De la sorte j'évite qu'elle ne s'exécute N fois mais une seule pour des raisons de performances.
Visiblement je n'ai pas tout compris sur comment fonctionne cette clause car j'ai un échec lamentable...

Voici mes tests.

Une sous-requête dans la clause SELECT d'un SELECT ne peux pas être réutilisée dans ce SELECT, même avec un ALIAS.
Création d'un alias OK.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
	SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM FROM EMP;
	NAME                                                  SALAIRE        MINIMUM
	-------------------------------------------------- ---------- ----------
	DUBOIS                                                  40000      40000
	DURAND                                                  45000      40000
	DUPONT                                                  48000      40000
Réutilisation KO d'un alias créé dans un SELECT dans le même SELECT .
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
	SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/MINIMUM 	FROM EMP;
	SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/MINIMUM FROM 	EMP
              								                                                                *
	ERROR at line 1:
	ORA-00904: "MINIMUM": invalid identifier
Il faut donc réutiliser l'ordre SQL associé à l'alias si je veux le bon résultat.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/(SELECT 	MIN(SALAIRE) FROM EMP) FROM EMP;
	NAME                                                  SALAIRE    MINIMUM SALAIRE/(SELECTMIN(SALAIRE)FROMEMP)
	-------------------------------------------------- ---------- ---------- -----------------------------------
	DUBOIS                                                  40000      40000                                1
	DURAND                                                  45000      40000                               1,125
	DUPONT                                                  48000      40000                              1,2
Le problème est que l'ordre SQL est exécuté N fois : la table EMP est lue trois 3!
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
	SQL> SET AUTOTRACE ON
	SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM, SALAIRE/(SELECT 	MIN(SALAIRE) FROM EMP) FROM EMP;
	NAME                                                  SALAIRE    MINIMUM SALAIRE/(SELECTMIN(SALAIRE)FROMEMP)
	-------------------------------------------------- ---------- ---------- -----------------------------------
	DUBOIS                                                  40000      40000                                1
	DURAND                                                  45000      40000                               1,125
	DUPONT                                                  48000      40000                              1,2
 
        Execution Plan
	----------------------------------------------------------
	Plan hash value: 1174980467
	---------------------------------------------------------------------------
	| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
	---------------------------------------------------------------------------
	|   0 | SELECT STATEMENT   |      |     3 |   345 |     3   (0)| 00:00:01 |
	|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
	|   2 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
	|   3 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
	|   4 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     3   (0)| 00:00:01 |
	|   5 |  TABLE ACCESS FULL | EMP  |     3 |   345 |     3   (0)| 00:00:01 |
	---------------------------------------------------------------------------
Le problème est que si j'utilise la clause WITH pour afficher le même résultat, le coût passe de 3 à 6
Je crée une vue en ligne SELECT_MINIMUM qui récupère le salaire le plus bas de tous les employés et après je réutilise cette vue dans le SELECT.
Et c'est là où ça coince ...
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
SQL> WITH SELECT_MINIMUM AS (select MIN(SALAIRE) AS MINIMUM FROM EMP) select name, salaire, (SELECT MINIMUM FROM SELECT_MINIMUM), salaire/(SELECT MINIMUM FROM SELECT_MINIMUM)  from EMP;
 
NAME                                                  SALAIRE (SELECTMINIMUMFROMSELECT_MINIMUM) SALAIRE/(SELECTMINIMUMFROMSELECT_MINIMUM)
-------------------------------------------------- ---------- --------------------------------- -----------------------------------------
DUBOIS                                                  40000                             40000                                 1
DURAND                                                  45000                             40000                             1,125
DUPONT                                                  48000                             40000                               1,2
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3834331678
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     3 |   345 |     6   (0)| 00:00:01 |
|   1 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6620_AC1496 |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6620_AC1496 |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   6 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6620_AC1496 |       |       |            |          |
|   7 |    SORT AGGREGATE          |                           |     1 |    13 |            |          |
|   8 |     TABLE ACCESS FULL      | EMP                       |     3 |    39 |     3   (0)| 00:00:01 |
|   9 |   TABLE ACCESS FULL        | EMP                       |     3 |   345 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Si vous pouvez m'aider à comprendre ce qui se passe, par avance merci