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

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:

---

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:

---

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:

---

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:

---

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 :aie:
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:

---

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 :D

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:

---

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

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

Code:

---

1 2 3 4 5

select name , salaire , min(salaire) over() as MINIMUM , salaire / min(salaire) over() from EMP

---

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:

---

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:

---

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 | ---------------------------------------------------------------------------

---

Bonsoir,

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

Citation:

---

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:

---

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

---

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

Je suis d'accord mnitu, mais je voulais utiliser la clause WITH pour voir sur quoi je gagne.
Et puis la syntaxe que j'ai utilisée est plus facile à faire comprendre à des débutants que celle des fonctions analytiques, même si c'est une partie du SQL très intéressant.