Discussion :

[jld_33]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

: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 ), soit il n'y a pas de solution...mais ça m'étonne quand même soit la vérité est ailleur, mais dans ce cas.. OU..?


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]

[orafrance]

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 ?

[jld_33]

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

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 ...
et ce que c'est plus clair ?

[PpPool]

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 !

@

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

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[jld_33]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SheikYerbouti]

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

[pifor]

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

[SheikYerbouti]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SheikYerbouti]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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>

[jld_33]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[sygale]

et avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

?

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

[jld_33]

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

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

[SheikYerbouti]

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 !

[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à:

colonne IS NULL

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.

[SheikYerbouti]

On peut utiliser un curseur dynamique sans EXECUTE IMMEDIATE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ;

[jld_33]

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

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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