[Oracle 9i] Bind variables et sql dynamique

Bonjour,
nous utilisons du sql dynamique pour la construction de nos requetes dans des procédures et des packages oracles.
Suite à la lecture de plusieurs articles, dont celui ci
http://www.rittman.net/archives/000832.html
nous désirons utiliser les variables "bindées" afin d'amélirorer les performances globales.
Ci dessous un exemple simpliste de ce que nous faisons et de ce que nous voudrions faire :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

CREATE OR REPLACE PROCEDURE exemple( pi_id_user IN NUMBER, cr_cursor OUT Cr_Rpt_Cursors.cr_rpt_cursor -- type REF CURSOR ) AS l_qry VARCHAR2(2000); BEGIN   /* ancienne méthode sans utilisation de bind variables l_qry :='SELECT LOGIN FROM T_USERS where ID_USER=' || pi_id_user;   OPEN cr_cursor FOR l_qry ; */     --nouvelle méthode l_qry :='SELECT LOGIN FROM T_USERS where ID_USER=:bind_id_user'; OPEN cr_cursor FOR l_qry USING pi_id_user ;   END;

---

ceci marche très bien,
Seul GROS problème, c'est exemple est trop simple et en fait la génération de notre chaine sql dynamique est plutot du style :

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 28 29 30

CREATE OR REPLACE PROCEDURE exemple( pi_id_user IN NUMBER, pi_param1 IN NUMBER, pi_param2 IN NUMBER, cr_cursor OUT Cr_Rpt_Cursors.cr_rpt_cursor -- type REF CURSOR ) AS l_qry VARCHAR2(2000); BEGIN   l_qry :='SELECT LOGIN FROM T_USERS where 1=1';   IF pi_id_user != -1 THEN l_qry:=l_qry || ' AND ID_USER=:bind_pi_id_user'; END IF;   IF pi_param1 != -1 THEN l_qry:=l_qry || ' AND ID_PAYS = :bind_pi_param1'; END IF;   IF pi_param2 != -1 THEN l_qry:=l_qry || ' AND ID_VILLE = :bind_pi_param2'; END IF;   --[...]   -- impossible de faire le bind ci dessous car je ne suis pas sûr d'avoir une 1,2 ou 3 variable à binder OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ,pi_param2 ;   END;

---

J'ai un probleme dans la clause USING ci dessus car je ne suis pas sûr d'avoir une 1,2 ou 3 variable à passer (puisque la chaine générée dépend des valeurs des parametres)

Il faudrait en fait que je puisse renseigner les variables :bind_pi_param1, :bind_pi_param2 au moment de la création de la chaine (dans le if)

j'ai essayé

Code:

---

:bind_pi_param2:=pi_param2

---

mais le compilateur n'en veut pas

Si je met directement dans la chaine générée :pi_param2 le message est au moment de l'execution: ORA-01008: not all variables bound

J'ai bien vu la possibilité d'utiliser le package dbms_sql , mais la syntaxe est beaucoup plus lourde et serait plus lente dixit :
http://sheikyerbouti.developpez.com/execute_immediate/

Impossible de trouver sur le net d'exemples correspondant à mon pb.
Soit je suis passé completement a coté et c'est tellement simple que personne n'a eu l'idée de poser la question, soit j'ai mal cherché (dans ces 2 cas désolé de la question :oops:), soit il n'y a pas de solution...mais ça m'étonne quand même :roll: soit la vérité est ailleur, mais dans ce cas.. OU..? 8O


Merci pour toute aide ou conseil pour résoudre ce problème (en espérant ne pas avoir été trop long dans la description )

Pour info, outils utilisés
[Oracle9i Enterprise Edition Release 9.2.0.2.0]
[TOAD 8.5.3.2]

je ne comprends pas ce que tu veux au bout du compte... tu veux pas donner la requête "en clair" pour chacun des cas ?

Hum, un petit copié collé malhencontreux a obscurci l'exemple (je viens de rééditer le précédent post :oops:

Sinon l'idée est la suivante:
Si on a passé un identifiant user différent de -1 alors on filtre sur l'id_user
et/ou si on a passé un identifiant de ville différent de -1 on filtre sur la ville
et/ou si on a passé un identifiant de pays différent de -1 on filtre sur le pays
...

Résultat la requete générée dans la chaine l_qry peut être:
SELECT LOGIN FROM T_USERS where 1=1
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_USER=:bind_pi_id_user
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_USER=:bind_pi_id_user
AND ID_PAYS = :bind_pi_param1
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_USER=:bind_pi_id_user
AND ID_PAYS = :bind_pi_param1
AND ID_VILLE = :bind_pi_param2
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_PAYS = :bind_pi_param1
ou
SELECT LOGIN FROM T_USERS where 1=1
AND ID_PAYS = :bind_pi_param1
AND ID_VILLE = :bind_pi_param2
etc....

Le probleme est qu'il y a donc un nombre variable de variable à binder et
je ne peut pas faire
OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ,pi_param2 ;
car selon les cas, il faudrait:
OPEN cr_cursor FOR l_qry ;
ou
OPEN cr_cursor FOR l_qry USING pi_id_user
ou
OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ;
ou
OPEN cr_cursor FOR l_qry USING pi_id_user, pi_param1 ,pi_param2 ;
ou
OPEN cr_cursor FOR l_qry USING pi_param1 ;
ou
OPEN cr_cursor FOR l_qry USING pi_param1 ,pi_param2 ;

etc...


Le nombre de combinaisosn possible augmentant vite avec le nombre de parametre, je ne peux pas vraiment mettre tous ces "OPEN cr_cursor FOR" dans des if imbriqués ...:triste:
et ce que c'est plus clair ? :oops:

Bonjour

il faut que tu ajoutes tes blocs if EGALEMENT au niveau de l'ouverture de l'OPEN CURSOR

dans la préparation de ta chaine SQL, il te faut simplement avoir recours à :1, :2,... etc..

j'ai déjà eut à gérer ce genre de pb, et cela est passé ainsi !

@

Vous pouvez vous en sortir avec une seule syntaxe d'ordre select prévoyant tous les cas.
Voici un exmple simple avec la table EMP:

Code:

---

1 2 3 4 5 6 7 8 9 10

DECLARE l VARCHAR2(200) := 'select count(*) from emp where (deptno = :1 or :1 is null) and (ename = :2 or :2 is null )' ; vide VARCHAR2(1) := '' ; ret PLS_INTEGER ; BEGIN EXECUTE IMMEDIATE l INTO ret USING 10,10,vide,vide ; dbms_output.put_line('ret= ' || ret); EXECUTE IMMEDIATE l INTO ret USING 10,10,'CLARK','CLARK' ; dbms_output.put_line('ret= ' || ret); END;

---

Citation:

---

Envoyé par SheikYerbouti

Vous pouvez vous en sortir avec une seule syntaxe d'ordre select prévoyant tous les cas.
Voici un exmple simple avec la table EMP:

Code:

---

1 2 3 4 5 6 7 8 9 10

DECLARE l VARCHAR2(200) := 'select count(*) from emp where (deptno = :1 or :1 is null) and (ename = :2 or :2 is null )' ; vide VARCHAR2(1) := '' ; ret PLS_INTEGER ; BEGIN EXECUTE IMMEDIATE l INTO ret USING 10,10,vide,vide ; dbms_output.put_line('ret= ' || ret); EXECUTE IMMEDIATE l INTO ret USING 10,10,'CLARK','CLARK' ; dbms_output.put_line('ret= ' || ret); END;

---

---

hélas on y a pensé mais sauf erreur de ma part, si on fait ça, plus d'utilisation des index.:mur:
De plus on profitait de la construction dynamique pour n'ajouter certaines tables que quand c'etait vraiment nécessaire, donc j'ai peur que si on fasse ça, a l'arrivée on perde plus de temps et de ressources qu'on en aura gagné...:pleure:

Non, car :1 représente la bind variable, pas le nom de la colonne. donc les index seront naturellement utilisés.

Dans l'exemple donné, si on fait le test is null, comme les valeurs nulles ne sont pas stockées dans l'index, l'index ne peut pas être utilisé.

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 28 29 30 31 32 33 34

SQL> set autotrace on SQL> select count(*) from emp where (deptno = 10 or 10 is null) and (ename = 'CLARK' or 'CLARK' is null ) 2 /   COUNT(*) ---------- 1     Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 3 2 INDEX (RANGE SCAN) OF 'ENAME_IDX' (NON-UNIQUE)         Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 199 bytes sent via SQL*Net to client 274 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed   SQL>

---

mais effectivement, si les bind variables sont null, les index ne seront pas utilisés...

Quoi que ! (ID et LABEL sont indexées)

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 28 29 30 31 32 33 34

SQL> set autotrace on SQL> select count(*) from bigtable where (id = 10001 or 10001 is null) and (label = null or null is null ) 2 /   COUNT(*) ---------- 1     Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIGTABLE' 3 2 INDEX (UNIQUE SCAN) OF 'ID_IDX' (UNIQUE)         Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 198 bytes sent via SQL*Net to client 275 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed   SQL>

---

Citation:

---

Envoyé par SheikYerbouti

Non, car :1 représente la bind variable, pas le nom de la colonne. donc les index seront naturellement utilisés.

---

justement il y a un moment j'ai eu a faire cette manipulation et il me semblait que ça ne marchait pas. :?
Je viens de re-essayer avec une table :

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

CREATE TABLE M2DBA.Z_USERS ( ID_USER NUMBER, NOM VARCHAR2(50), PRENOM VARCHAR2(50) )   LOGGING NOCACHE NOPARALLEL NOMONITORING;     ALTER TABLE M2DBA.Z_USERS ADD ( CONSTRAINT Z_USERS_PK PRIMARY KEY (ID_USER));     INSERT INTO Z_USERS values (1,'DUPOND', 'PIERRE');   INSERT INTO Z_USERS values (2,'MARTIN', 'JEAN');   INSERT INTO Z_USERS values (3,'ROGER', 'YVES')

---

(je ne sais pas si ça peut avoir une incidence mais j'utilise TOAD...)

Code:

---

select * from z_users u where (u.ID_USER=:1 or :1 is null)

---

si je met :1 = 2 (dans le fenetre de toad, je ne sais pas faire autrement.... désolé ...)
-->
SELECT STATEMENT Optimizer Mode=CHOOSE
TABLE ACCESS FULL M2DBA.Z_USERS

Code:

---

select * from z_users u where u.ID_USER=:1

---

-->
SELECT STATEMENT Optimizer Mode=CHOOSE
TABLE ACCESS BY INDEX ROWID M2DBA.Z_USERS
INDEX UNIQUE SCAN M2DBA.Z_USERS_PK

ça semblerais dire que si on fait la methode avec le "or", il n'utilise pas d'index (j'ai essayé avec des tables plus grandes et a chaque fois il fait un full scan si j'ai un "or", même si il ne porte pas sur un champ de la table.

Mais si je me suis trompé dans ma démarche ou si il y a un moyen de contourner ce pb, je suis preneur.:P

et avec

Code:

---

1 2	select * from z_users u where u.id_user = nvl(:1,u.id_user)

---

ou

Code:

---

1 2	select * from z_users u where u.id_user(+) = :1

---

?

Voir l'exemple que je donne quelques post auparavant.
Je me suis basé sur une table 100 000 enregistrements.
Il faut en tenir compte car, sur les petites tables, Oracle choisi souvent un FULL SCAN tout aussi rapide.

Citation:

---

Envoyé par SheikYerbouti

Voir l'exemple que je donne quelques post auparavant.
Je me suis basé sur une table 100 000 enregistrements.
Il faut en tenir compte car, sur les petites tables, Oracle choisi souvent un FULL SCAN tout aussi rapide.

---

Ok,
Je vais essayer sur mes cas réels (avec des jointures sur plusieurs tables pour voir si c'est "viable".

Le probleme va être de pouvoir savoir quel sera le meilleur cas entre:

- 1 la solution actuelle du sql dynamique sans bind de variables mais où l'on ne met que les tables nécessaires (si certaines variables sont a null (ou -1) des tables ne sont pas nécessaires et on ne les ajoute pas à la requete dynamique d'où des jointures en moins)

- 2 solution a base de bind de variable et la syntaxe préconisée ci dessus (u.ID_USER=:1 or :1 is null....) (mais qui va m'obliger à mettre tout le temps toutes les tables , et vérifier cette histoire d'indexes)

- 3 solution à base de dbms_sql qui il me semble peut me permettre de ne binder que les variables nécessaire et qui me permettrait de n'utiliser que les tables nécessaires aussi . Ce qui me fait hésiter par cette méthode est la lourdeur du code a produire et la jolie phrase

Citation:

---

Plus grande rapidité d’exécution

En moyenne, le SQL dynamique natif s’exécute de 1.5 à 3 fois plus rapidement. [ que le package DBMS_SQL]

---

cf : http://sheikyerbouti.developpez.com/execute_immediate/

Merci en tout cas pour ces conseils !!!

Il faut avouer que votre besoin n'est pas vraiment commun et ne peut se traiter sans un minimum d'effort.
Mais ce n'est pas le nombre d'instruction ni le temps passé qui compte. Seulement le temps d'exécution !

SheikYerbouti a raison au sujet de l'utilisation ou non de l'index. L'index ne sera pas utilisé seulement si la clause WHERE ne contient que cette condition là:

Citation:

---

colonne IS NULL

---

Citation:

---

En moyenne, le SQL dynamique natif s’exécute de 1.5 à 3 fois plus rapidement. [ que le package DBMS_SQL]

---

Là, je pense que ça dépend si le code SQL est executé seulement 1 fois ou plusieurs fois. Si le code n'est executé qu'une seule fois, EXECUTE IMMEDIATE est sans doute plus rapide. Mais ce n'est sans doute plus vrai si le code doit être exécuté plusieurs fois car EXECUTE IMMEDIATE implique compilation systématique du SQL alors que DBMS_SQL bien utilisé permet de l'éviter.

Il faut aussi noter qu'on ne peut pas utiliser EXECUTE IMMEDIATE avec un SELECT * dès que le SELECT * va retourner plus d'une ligne. Dans ce cas là, DBMS_SQL ou l'utilisation d'un curseur est obligatoire.

On peut utiliser un curseur dynamique sans EXECUTE IMMEDIATE

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Declare l varchar2(100) := 'Select * from EMP where DEPTNO = 20'; c sys_refcursor ; e emp%rowtype ; Begin Open c for l ; Loop Fetch c into e ; exit when c%notfound; dbms_output.put_line( e.ename ) ; End loop; Close c ; End ;

---

Citation:

---

Envoyé par pifor

SheikYerbouti a raison au sujet de l'utilisation ou non de l'index. L'index ne sera pas utilisé seulement si la clause WHERE ne contient que cette condition là:
[...]

---

Je suis completement perdu :help:
Visiblement tout le monde est d'accord pour dire que l'index sera utilisé (sauf si colonne IS NULL ).
J'ai encore fait des tests ce matin sur plusieures tables (65000 enregistrements et 580000 enregistrement) en me basant uniquement sur une recherche sur un champ indexé (sur l'exemple ce n'etait pas une clé primaire mais j'ai fait la même chose sur une table et sa clé primaire et les résultats sont du même style).

Les résultats sont les suivants :
(en donnant une valeur non nulle et <>-1 à :pi_id_art)
Avec une syntaxe :

Code:

---

where id_art=:pi_id_art

---

Temps d'execution de la recherche : 31ms
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 10 1
INDEX RANGE SCAN M2DBA.T_580000_ENREG_IDX 10 40 1

Avec une syntaxe :

Code:

---

where (:pi_id_art = -1 or id_art=:pi_id_art )

---

Temps d'execution de la recherche : 2s
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 27 K 3
INDEX FAST FULL SCAN M2DBA.T_580000_ENREG_IDX 27 K 107 K 3

avec la même syntaxe mais sur une autre table plus petite (65000 enreg), il faisait même un TABLE FULL SCAN

Avec une syntaxe :

Code:

---

where id_art=decode(:pi_id_art, -1, id_art,:pi_id_art)

---

Temps d'execution de la recherche : 46ms
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 552 K 4
CONCATENATION
FILTER
INDEX FAST FULL SCAN M2DBA.T_580000_ENREG_IDX 10 40 1
FILTER
INDEX RANGE SCAN M2DBA.T_580000_ENREG_IDX 10 40 1


Il semblerait donc que la solution la moins performante soit celle avec le "or" (utilisation de l'index mais en FULL SCAN sur l'index)

Je suis vraiment le seul a avoir ces résultats et il faut que je regarde ce qui se passe du coté de l'administration de la base ou il y a autre chose ?!?!?:arf: