Les bind variables pour les requêtes courantes

Une application destinée à un grand nombre d'utilisateurs est généralement composée de requêtes identiques à un critère prêt.

Soit la requête de l'application permettant de générer la facture pour une commande donnée (CMD1) :

select cm.ref, cl.nom, cl.adresse, a.description, a.prix, l.quantite, sum(l.prix*l.quantite)
from commande cmd
inner join client cl on cl.idclient = cm.idclient
inner join ligne_commande l on l.idcmd = cm.idcmd
inner join article a on a.idart = l.idart
where cm.ref = 'CMD1';


Dans ce cas, Oracle analysera la requête et construira le meilleur plan d'exécution.
Cependant, si un autre utilisateur demande ensuite la même chose mais pour la commande CMD2, Oracle réévaluera la requête suivante :

select cm.ref, cl.nom, cl.adresse, a.description, a.prix, l.quantite, sum(l.prix*l.quantite)
from commande cmd
inner join client cl on cl.idclient = cm.idclient
inner join ligne_commande l on l.idcmd = cm.idcmd
inner join article a on a.idart = l.idart
where cm.ref = 'CMD2';


Or, comme cette demande est très courante, il est utile qu'Oracle ne recherche pas systématiquement le plan à utiliser. C'est pour cela que sont utiliser les bind variables :

select cm.ref, cl.nom, cl.adresse, a.description, a.prix, l.quantite, sum(l.prix*l.quantite)
from commande cmd
inner join client cl on cl.idclient = cm.idclient
inner join ligne_commande l on l.idcmd = cm.idcmd
inner join article a on a.idart = l.idart
where cm.ref = :ref_cmd;


Il faudra ensuite indiquer la valeur de cette variable.

Dans TOAD ou SQLdeveloper, il suffit d'écrire la requête comme citée précedemment, le logiciel propose ensuite d'assigner une valeur à la bind variable.

Dans SQL*Plus, il faut au préalable déclarer la variable :

SQL> variable ref_cmd char
Puis l'affecter dans un bloc PL (en la préfixant par les :) :

SQL> begin
2 :ref_cmd := 'CMD1';
3 END;
4 /
PL/SQL procedure successfully executed

ou bien :
SQL> execute :ref_cmd := 'CMD1'
PL/SQL procedure successfully executed


On peut alors vérifier la liste des bind variables par la commande var (ou variable) :

SQL> var
variable ref_cmd
datatype char


Et enfin exécuter la requête avec la bind variable (toujours précédée des :).

Dans les applications codées et utilisant les API courantes (JDBC, ADO, ODBC, OCI, dblib) il existe des mécanismes permettant de tirer profit des bind variables : OleDbCommand, PreparedStatement, OCIStmtPrepare.

Cependant, il existe le paramètre cursor_sharing qui permet, lorsqu'il est positionné à SIMILAR, de retrouver les requêtes semblables afin de se comporter comme s'il s'agissait de bind variable.

Il faut bien noter que l'utilisation de bind variables est recommandée pour les traitements OLTP, cependant, elle est à éviter pour les traitements batch car Oracle ne distinguera pas la distribution hétérogènes des valeurs dans une table :

select * from commande
inner join client cl on cl.idclient = cm.idclient
where date_cmd < '2008-12-25'

et
select * from commande
inner join client cl on cl.idclient = cm.idclient
where date_cmd < :date_veille


Peuvent retourner deux plans d'exécution différents.

Cependant, en oracle 11g, ce fonctionnement évolue un peu plus et lorsque la valeur du paramètre est à SIMILAR, Oracle va utiliser les histogrammes afin de définir comment sont équilibrer la valeur dans la colonne.
De la même manière, si les conditions de la requête sont sur une clé primaire ou qu'il existe une contrainte d'unicité dessus, Oracle sait par avance qu'une seule valeur de peut sortir de la table en question, il préfèrera très probablement utiliser l'index.


Voir également :
- La jointure : sélection sur plusieurs tables
- La table DUAL pour développer
- Comparer l'exécution d'une requête avec 2 index différents
- Utilisation de rownum et rowid
- Pourquoi mon index n'est pas utilisé ?




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

Catégories