Clause WITH pour limiter le nombre de SELECT mais ECHEC TOTAL!

**Ikebukuro** · 16/12/2016, 14h18

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

**skuatamad** · 16/12/2016, 15h10

Le WITH a matérialisé la requête, étape : LOAD AS SELECT | SYS_TEMP_0FD9D6620_AC1496

Puis c'est cette table temporaire qui est accédée, comme vous codez plusieurs accès, vous avez plusieurs accès à cette table temporaire, mais vous pouvez coder comme ça :

Code :

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

1
2
3
4
5
6
7
WITH SELECT_MINIMUM AS (
select MIN(SALAIRE) AS MINIMUM 
  FROM EMP
) 
select name, salaire, sm.MINIMUM, salaire/sm.MINIMUM
  from EMP
 cross join SELECT_MINIMUM sm

A noter que le WITH ne materialise pas forcément la requête dans une table temporaire.

Par ailleurs la comparaison du coût de 2 requêtes syntaxiquement différentes n'est pas très pertinente.
Le coût permet plutôt de comprendre pourquoi l'optimiseur a privilégié un accès plutôt qu'un autre.

https://asktom.oracle.com/pls/asktom...D:313416745628

**al1_24** · 16/12/2016, 15h19

Par curiosité, as-tu essayer avec les fonctions de regroupement fenêtrées ?

Code :

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

1
2
3
4
5
select  name
    ,   salaire
    ,   min(salaire) over() as MINIMUM
    ,   salaire / min(salaire) over()
from    EMP

**Ikebukuro** · 16/12/2016, 15h19

Merci pour les réponses très rapide :-)
Je ferai mes tests ce week-end.

[EDIT]
Merci pour le conseil al1 24 mais je voulais surtout voir, avec la clause WITH, comment on peut diminuer le coût d'une opération appelée N fois.

Bon, voici le résultat de mes tests et on voit bien qu'utiliser une vue en ligne réduit, dans mon cas, le nombre de lignes utilisées et évite des SORT.
Avec la clause WITH, Oracle crée une vue et ensuite, en y accédant, lit une seule donnée à chaque fois et fait un seul SORT.

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
SQL> WITH SELECT_MINIMUM AS (select MIN(SALAIRE) AS MINIMUM FROM EMP) select name, salaire, 	(SELECT MINIMUM FROM SELECT_MINIMUM) AS MINI01, salaire/(SELECT MINIMUM FROM 	SELECT_MINIMUM) AS MINI02,  salaire*(SELECT MINIMUM FROM SELECT_MINIMUM) AS MINI03 from 	EMP;

NAME                                                  SALAIRE     MINI01     MINI02     MINI03
-------------------------------------------------- ---------- ---------- ---------- ----------
DUBOIS                                                  40000      40000          1 1600000000
DURAND                                                  45000      40000      1,125 1800000000
DUPONT                                                  48000      40000        1,2 1920000000

	Execution Plan
	----------------------------------------------------------
	Plan hash value: 3276894477
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |     3 |    33 |     6   (0)| 00:00:01 |
|   1 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6627_AC1496 |     1 |     4 |     2   (0)| 00:00:01 |
|   3 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6627_AC1496 |     1 |     4 |     2   (0)| 00:00:01 |
|   5 |  VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL        | SYS_TEMP_0FD9D6627_AC1496 |     1 |     4 |     2   (0)| 00:00:01 |
|   7 |  TEMP TABLE TRANSFORMATION |                           |       |       |            |          |
|   8 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6627_AC1496 |       |       |            |          |
|   9 |    SORT AGGREGATE          |                           |     1 |     4 |            |          |
|  10 |     TABLE ACCESS FULL      | EMP                       |     3 |    12 |     3   (0)| 00:00:01 |
|  11 |   TABLE ACCESS FULL        | EMP                       |     3 |    33 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

En revanche, sans clause WITH, les N lignes sont lues à chaque fois et, à chaque fois, un SORT est fait, d'où des problèmes de performances.

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
SQL> select name, salaire,      (select MIN(SALAIRE) FROM EMP) AS MINI01, salaire/(select MIN(SALAIRE) FROM EMP) AS MINI02,  salaire*(select MIN(SALAIRE) FROM EMP) AS MINI03 from      EMP;
NAME                                                  SALAIRE     MINI01     MINI02     MINI03
-------------------------------------------------- ---------- ---------- ---------- ----------
DUBOIS                                                  40000      40000          1 1600000000
DURAND                                                  45000      40000      1,125 1800000000
DUPONT                                                  48000      40000        1,2 1920000000

Execution Plan
----------------------------------------------------------
Plan hash value: 3137437906
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    33 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |     3 |    12 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   4 |   TABLE ACCESS FULL| EMP  |     3 |    12 |     3   (0)| 00:00:01 |
|   5 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   6 |   TABLE ACCESS FULL| EMP  |     3 |    12 |     3   (0)| 00:00:01 |
|   7 |  TABLE ACCESS FULL | EMP  |     3 |    33 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

**escartefigue** · 23/12/2016, 18h22

Bonsoir,

Question subsidiaire, pourquoi utiliser une table dérivée dans votre première requête plutôt que directement min(salaire) ?

**Ikebukuro** · 23/12/2016, 19h41

Envoyé par escartefigue

Bonsoir,

Question subsidiaire, pourquoi utiliser une table dérivée dans votre première requête plutôt que directement min(salaire) ?

Je voulais afficher les enregistrements détails de la table des employés donc impossible de faire un MIN(SALAIRE) car c'est une fonction de regroupement.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SELECT NAME, SALAIRE, (SELECT MIN(SALAIRE) FROM EMP) AS MINIMUM FROM EMP;
 
NAME                                                  SALAIRE    MINIMUM
-------------------------------------------------- ---------- ----------
                                                        40000      40000
DURAND                                                  45000      40000
DUPONT                                                  48000      40000
 
SQL> SELECT NAME, SALAIRE, MIN(SALAIRE)  FROM EMP;
SELECT NAME, SALAIRE, MIN(SALAIRE)  FROM EMP
       *
ERROR at line 1:
ORA-00937: not a single-group group function

**mnitu** · 24/12/2016, 12h23

Vous voulez afficher le détail et le regroupement => fonction analytique. Rien ne peut battre ça.

Clause WITH pour limiter le nombre de SELECT mais ECHEC TOTAL!

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager