La clause WITH à la rescousse des sous requêtes

Lors d'un rendu général, il arrive parfois de construire des requêtes un peu complexes, nécessitant plusieurs sous requêtes au sein d'unions ou de jointures différentes.
La clause with permet d'optimiser ces requêtes en indiquant à oracle de n'évaluer la requête qu'une seule fois et de réutiliser ses résultats par la suite.

Requête originelle :
select * from client where idfacture in (
select souscripteur from facture where souscription_type='INTERNET')
union
select * from client inner join site on client.id_site = site.id_site where site.id_souscripteur in (
select souscripteur from facture where souscription_type='INTERNET')


devient :
with cur_souscripteur as (select souscripteur from facture where souscription_type='INTERNET')
select * from client where idfacture in (select souscripteur from cur_souscripteur)
union
select * from client inner join site on client.id_site = site.id_site where site.id_souscripteur in (select souscripteur from cur_souscripteur)


Les plans d'exécution correspondants permettent de mettre en évidence l'impact du tuning SQL mis en place.

Cet exemple est évidemment simpliste, mais l'utilisation de clause with peut s'avérer très utiles, surtout lorsque la (les) sous-requête(s) est (sont) coûteuse(s).

Exemple avec plusieurs clauses with (pas de répétition du mot with, mais utilisation des virgules) :

with wreq1 as (select .. from .. where ..),
wreq2 as (select .. from .. where ..),
wreq2 as (select .. from .. where ..)
select * from t1 where id11 in (select * from wreq1) and id12 in (select * from wreq2)
union
select * from t2 where id22 in (select * from wreq2) and id23 in (select * from wreq3)
union
select * from t3 where id31 in (select * from wreq1) and id33 in (select * from wreq3)


Outre un gain de performance possible, on gagne aussi souvent en clarté.
Attention, with ne remplace pas une vue ; mais peut très bien être une requête sur une vue.

A noter qu'il existe un hint non documenté : materialize, forçant Oracle à créer une tale temporaire et garder le résultat de cette sous requête pour toute la requête :

with total as (select /* + materialize */ sum(prix) tot from montant)
select sum(prix), client, (select tot from total) from montant
group by client;



Voir aussi :
- Les vues
- Migration de modèles avec vues et synonymes
- Les colonnes virtuelles en 11g
- La jointure : sélection sur plusieurs tables
- Améliorer les performances d'une vue




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories