Clause WITH dans le Select : quel intérêt?

Bonjour,

J'ai lu qu'il était plus performant de faire un Select avec une clause With dans le cas où une même sous-requête est exécutée N fois plutôt que de laisser l'optimiseur d'Oracle se débrouiller seul.
MAIS j'avoue que cela me laisse perplexe...

Code:

---

1 2 3 4 5

WITH sub_req1 AS (SELECT max(salary) FROM stagiaires) SELECT first_name, last_name FROM employees WHERE commission_pct>sub_req1 AND salary<sub_req1;

---

Ce que j'ai compris c'est que Oracle va créer une vue avec le résultat de la sous-requête du WITH et il utilisera cette vue à chaque appel de la sous-requête, donc il lira les données en mémoire.
Ce que je ne comprends pas c'est pourquoi c'est plus rapide que l'action faite sans WITH. Je pensais que Oracle exécutait la requête

Code:

---

SELECT max(salary) FROM stagiaires

---

une première fois, un process serveur charge les données du disque dur vers la SGA et au deuxième appel de la même requête Oracle ne va pas relire les données sur le disque dur car il sait qu'elles sont déjà en SGA, il va donc relire celles déjà stockées et, de plus, il ne va pas ré-analyser la requête (parsing) car il l'a déjà fait une fois donc il a mémorisé cette étape d'où un gain de temps.

En réfléchissant je me dis que la vue est mise à jour en temps réel alors que les données en SGA peuvent ne pas refléter la dernière version de données entre le premier appel et le suivant mais cela me semblerait être un manque de chance car une requête pas trop lourde s'exécute "en général" très rapidement. Dans ce cas, il faudra effectivement que Oracle teste si les données en SGA sont en décalage avec celles du disque dur ou bien si une autre transaction dans un autre bloc de SGA a modifié les données sur lesquelles on travaille MAIS, sauf erreur de ma part, Oracle pratique la lecture cohérente donc même si les données sont modifiées par un autre user entre les différents appels, Oracle utilisera toujours dans ma transaction les données récupérées dans le premier appel, qu'on utilise la clause WITH ou non.

Comme vous le voyez ce n'est pas très clair pour moi, j'aimerai donc avoir vos lumières sur ce sujet.

Vous mélangez plusieurs sujets dans une seule question et cela montre que vous est dans la confusion.

D’abord une vue (sauf les vues matérialisées qu’on va laisser à coté) ne stocke pas des données c’est juste la requête sql qui est stockée. A un moment donné dans la version 8 il me semble Oracle a introduit la possibilité de faire un Select … from (Select …) et cela a été appelé une vue en ligne « inline view » parce qu’elle corresponde bien au Select … from « une vue préalablement créée »

Ensuite en version 9 Oracle a ajouté la factorisation des sous-requêtes via la clause WITH elle-même étendue à partir de la version 11 via la récursivité pour interroger des donnés hiérarchiques.

La factorisation d’un sous requête permet d’une part de récrire les vues en ligne en transformant le

Code:

---

Select … From (Select ….)

---

en

Code:

---

With Data As (Select … ) Select … From Data.

---

D’autre part dans certaines circonstances Oracle peut matérialiser la vue en ligne c’est à dire créer une table interne, stocker le contenu dans cette table et optimiser la requête en se basant sur cette table. Quand cela se passe le plan d’exécution met e évidence l’opération de la façon suivante

Code:

---

1 2 3

| 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D66EF_6A654247 | | | | |

---

Comme toute autre requête parfois cette factorisation est bénéfique parfois non et normalement c’est Oracle qui décide de matérialiser ou pas quoi que vous pouvez imposer votre choix via une astuce SQL non-documenté materialize ou tout simplement en ajoutant le rowid parmi les colonnes sélectionnées.

Un autre critère d’utilisation est lié à la lisibilité des requêtes complexes qui se basent sur des vues en lignes : il est plus facile de lire et par conséquence de maintenir une requête de ce type qui emploie With que celle qui utilise Select from Select à plusieurs niveaux.

Maintenant l’autre problème. Oracle en général crée un processus serveur pour exécuter une requête à la demande d’un processus client : sqlplus, Toad, ou autre application. La requête est analysé c’est le parsing qui peut être « hard » ou « soft » en fonction du fait que la requête a été déjà analysé par le serveur ou pas et son texte est stocké dans la SGA. Une clé hash est calculée à partir du texte de la requête (sqlid) et lors d’une demande de réexécution de la requête c’est cette clé hash qui permet de retrouver la requête et éventuellement de réutiliser son plan d’exécution si possible. Suite à l’exécution de cette requête il est possible qu’Oracle demande la lecture des blocks des données nécessaires au système d’exploitation quand il ne les trouve pas dans la mémoire, ce sont les lectures physiques. A cette occasion les blocks des données sont stockés en mémoire dans le buffer caché pour utilisation ultérieure et ils resteront tant qu’Oracle n’a besoin de plus de mémoire pour stocker des autres blocks nécessaires à des autres requêtes. Dans ce cas Oracle doit trouver de la place en remplaçant les blocks qui ont été utilisés le moins récemment. Ces blocks sont organisés dans des chaines des blocks et une clé de hachage calculé à partir de l’identifiant du block permet de vérifier leur présence en mémoire (bref les choses sont en fait un poil plus compliqués que présentés ici). En plus Oracle gèrent plusieurs version d’un même block des données en mémoire. Quand vous exécutez une requête de type

Code:

---

Update emp set sal = sal + 10 where empno = 7369

---

Oracle va lire le bloc qui contient l’enregistrement à modifier et il va cloner et modifier ce nouveau bloc en gardant en mémoire les deux versions. Si à ce moment une autre session exécute la requête Select … from emp Oracle va encore crée une nouvelle version de ce bloc pour la lecture consistante.
Pour des explications bien plus détaillées sur ce sujet ainsi que des requêtes pour investiguer ce phénomène vous pouvez lire Cache Buffer Chains Demystified

Houlala, il va me falloir du temps pour lire, relire et bien comprendre ta réponse mnitu car là c'est du très haut niveau 8O

En tout cas un gros merci pour ta réponse car là au moins j'ai de quoi satisfaire ma curiosité.

Bon alors je vais ajouter juste un nouveau lien concernant WITH Clause : Subquery Factoring

J'ai pris le temps de lire ton post mnitu et j'avoue que ce n'est pas clair pour moi.
De ce que je comprends, avec WITH Oracle crée un objet (inline view ou table temporaire) dans lequel il met les données à l'exécution la première fois de la requête du WITH alors que sans WITH les données sont stockées en SGA dans des blocs chaînés. Je comprends que c'est cette différence d'organisation qui serait la clé de la vitesse d'exécution : Oracle retrouve plus rapidement des données qui sont dans une inline view (surtout qu'elle ne contient QUE les données de la requête du WITH) alors que le parcours des blocs va prendre du temps (même dans le cas où le database buffer cache n'a pas été vidé) du fait que les données sont en vrac (je suppose) et que les bonnes données sont au milieu de données parasites (si la requête ramène que 2ko de données et que le db_block_size est de 8Ko alors il y a en mémoire 6Ko de données parasite).

Voilà, je ne sais pas si c'est exactement çà qui se passe mais j'essaye vraiment de comprendre pourquoi le WITH augmente les performances, même dans le cas où le db cache contient toutes les données récupérées lors de la première exécution de la sous-requête.

Vous avez décidé de factoriser une sous-requête via la clause WITH. Deux possibilités s’offre à Oracle à ce moment :

• Il décide de matérialiser les données de la sous-requête c’est à dire il va créer par-dessous la main une table temporaire et il va la alimenter avec les données de la sous-requête. Ensuite il va optimiser la requête finale (celle qui utilise la sous-requête) en utilisant cette table temporaire à la place de la sous-requête.
• Il décide de ne pas matérialiser les données de la sous-requête et après avoir appliqué des transformations de la requête finale lors du parsing il trouve un plan d’exécution. Dans certains cas ce plan pourrait impliquer plusieurs exécutions de la sous-requête factorisé et cela pourrait alourdir le temps de traitement.

Et pour mieux comprendre le premier cas, imaginez-vous que vous avez créé une table temporaire et que vous l’avez chargée avec les données de la sous-requête factorisée et que maintenant vous avez réécrit votre requête finale pour qu’il se base sur cette table temporaire.

Parfois A c’est la stratégie gagnante parfois c’est B.

Normalement vous devez laisser Oracle faire et intervenir seulement là ou l’optimiseur se trompe parce que vous avez une meilleure connaissance des données et de leurs répartitions et que vous savez qu’un meilleur plan est possible.

Comme vous pouvez constater il n’y a aucun référence à quelconque problématique de mémoire, de caché des buffers des données ou autre structure interne d’Oracle.

A nouveau je te remercie mnitu pour toute ton aide :D