[tuning] like avec variable

Version imprimable

13/03/2006, 21h13
batin

[tuning] like avec variable

Bonjour,
j'ai un problème de perf sur une de mes requêtes, en particulier sur une whereclause like !

J'ai 2 tables :
- table client (codeclient varchar2(20), autrecols varchar2(10)) avec un index sur la colonne codeclient. Cette table contient environ 800k lignes
- table parametre (param varchar2(512))

Le fonctionnement de mon outil est le suivant :
- un ensemble de requête sont prédéfinies dans le code
- l'utilisateur saisie des paramètres d'entrée (ca peut etre des chaines caractères, des nombres, ... d'où le varchar2 à 512)
- ces paramètres sont chargés dans la table parametre
- la requête prédéfinie va faire ensuite des select dans les tables qui vont bien

Le pb, c'est que l'utilisateur a besoin de mettre des caractères joker (%). Ainsi, il peut rentrer un paramètre DUP% ... ca ira dans ma table client, et retournera DUPONT, DUPOND, DUPOUET, DUPMACHIN ...

Alors, autant la requête :

Code:

select c.* from client c where c.codeclient like 'DUP%';

est instantanée (car elle utilise l'index).
Autre la requête :

Code:

select c.* from client c,parametre p where c.codeclient like p.param;

est super long; car n'utilise pas l'index, ... et la cardinalité explose.

J'ai vu que sur interbase, il y a une commande "starting by" qui est un truc qui ressemble au like avec un % à la fin (donc permettant l'utilisation de l'index). Mais en oracle 9i (là ou je suis), il n'y a pas d'équivalent.

Existe-t-il un moyen de forcer l'index par hint ?!?

Merci !!!
13/03/2006, 23h37
Jaouad

Bonsoir ,
Si on essaye de décoder ton probléme, voila ce que j'en comprends.

Tu as une reqûete quasi instantané avec un like . Comme le pourcent n'est pas au début il prends l'index existant. Jusque la tout est logique.

Par contre dans la seconde requête j'ai du mal a comprendre la requête. SI c'est une jointure entre les deux tables pourquoi est ce que tu ne l'écrit tout simplement ainsi :

Code:

select c.* from client c,parametre p where c.codeclient = p.param;

Si c'est deux colonnes sont indéxes et sous réserve de volumétrie et de cardinalité l'index devrait être utilisé
13/03/2006, 23h48
batin

l'égalité ne fonctionne pas si il y a un caractère joker dans mon champ param.
En gros, je fais :
insert into parametre (param) values ('DUP%');

Si je fais:
select c.* from client c,parametre p where c.codeclient = p.param;
==> il me retourne tous les clients qui ont un nom=DUP%
Or je veux tous les clients qui commencent par DUP.
C'est bien un like qu'il me faut !

Ou au pire, je fais :
select c.* from client c,parametre p where substr(c.codeclient,1,length(p.param)) = p.param;
mais bon, là encore je casse l'index
13/03/2006, 23h49
McM
Parce que que dans p.param il y aura le critère de recherche : valeur 'DUP%'

Ce que je ne comprend pas par contre, c'est combien de ligne contient parametre ? Est ce que l'utilisateur peut renseigner plusieurs critères (mais dans ce cas les critères sont des OR et non des AND).

Bref si une seule ligne, est ce que
Code:

1 2 SELECT c.* FROM client c WHERE c.codeclient LIKE (SELECT p.param FROM parametre p)
va mieux ?
14/03/2006, 00h02
batin
j'essaie ca demain au boulot pour voir.
La table parametre ne contiendra que peu d'enregistrement (<20)

et c'est bien un comportement de 'OR' pour la requête. C'est en emplacement d'une requête qui était construite en dynamique :
Code:

1 2 3 4 5 select c.* from client c where c.codeclient like 'DUP%' or c.codeclient like 'DUR%' or c.codeclient like 'MARTIN%' or c.codeclient like 'MACHIN%' ;
qui était bcp moins flexible pour certains besoins.

Au niveau du plan d'exécution, vaut-il mieux un nested loop ou un hash join ? J'ai du mal à trouver des infos en francais sur ce sujet
14/03/2006, 00h36
Jaouad

batin :fleche: merci d'utilise les balises Quote et Code qui améliorent la lisibilité de tes posts :wink:

:merci:

Par contre concernant la jointure cela dépend totalement de ta volumtrie et du nombre de lignes ramenés.

Pourquoi ne pas laisser Oracle choisir en s'assurant juste qu'il posséde tous les paramétres pour prendre la meilleure jointure
20/03/2006, 20h48
batin

marche toujours pas
J'ai essayé le code :
Code:

1 2 SELECT c.* FROM client c WHERE c.codeclient LIKE (SELECT p.param FROM parametre p)
mais ca ne marche pas ...
ORA-1427 : single-row subquery returns more than one row !

Y a une autre solution ?

Essayes de mettre un HINT

Code:

1
2
3
SELECT /*+ INDEX(c NOM_INDEX)*/ c.* 
FROM client c,parametre p 
WHERE c.codeclient = p.param;

20/03/2006, 21h07
HarryCoco

Bonsoir,

etant donné la conception de ton application, je ne vois pas d'autre solution que de faire un cuseur sur ta table parametre pour construire dynamiquement ta requete et l'envoyer par un EXECUTE IMMEDIATE.
21/03/2006, 10h33
orafrance

Sinon, il est possible de forcer le plan d'exécution grâce aux outlines

Sinon, j'arrive pas bien à saisir pourquoi tu ne fais pas un truc du genre :

Code:

select c.* from client c where c.codeclient like p_mon_parametre || '%';

où p_mon_parametre est la valeur de début, ici DUP :)
21/03/2006, 10h36
McM

parce qu'il peut y avoir plusieurs p_mon_parametre .

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
 
SQL> select /*+ RULE */ * from emp where ename like :p||'%';                                                                                                                    
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2775231443
 
--------------------------------------------
| Id  | Operation                   | Name |
--------------------------------------------
|   0 | SELECT STATEMENT            |      |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP  |
|*  2 |   INDEX RANGE SCAN          | I    |
--------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ENAME" LIKE :P||'%')
       filter("ENAME" LIKE :P||'%')
 
Note
-----
   - rule based optimizer used (consider using cbo)

où est le problème?

21/03/2006, 11h02
laurentschneider

Citation:

Envoyé par McM

parce qu'il peut y avoir plusieurs p_mon_parametre .

ouais, dans ce cas il peut y avoir des plans où il vaudrait mieux employer l'index et d'autres où non. Si tu employes des bind, tu gagnes en parsing, mais tu perds en flexibilité...

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> exec :p := '%T%'
PL/SQL procedure successfully completed.
 
P
--------------------------------------------------------------------------------------------------------------------------------
%T%
 
SQL> select * from emp where ename like :p;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28.09.1981 00:00:00       1250       1400         30
      7788 SCOTT      ANALYST         7566 19.04.1987 00:00:00       3000                    20
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
      7844 TURNER     SALESMAN        7698 08.09.1981 00:00:00       1500          0         30
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2775231443
 
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     2 |    74 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP  |     2 |    74 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I    |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ENAME" LIKE :P)
       filter("ENAME" LIKE :P)

ça me parait dangereux comme approche...

21/03/2006, 11h15
laurentschneider

:oops: je n'avais pas bien compris le problème. p n'est pas un bind mais une colonne. dans ce cas, je ne pense pas qu'il soit judicieux de forcer l'usage d'un index. mais si vraiment les paramètres sont toujours de la forme 'XXX%', un hint peut être "évalué"
21/03/2006, 11h53
orafrance
Citation:

Envoyé par batin

Si je fais:
select c.* from client c,parametre p where c.codeclient = p.param;
==> il me retourne tous les clients qui ont un nom=DUP%
Or je veux tous les clients qui commencent par DUP.
C'est bien un like qu'il me faut !

pourquoi pas :

Code:

select c.* from client c,parametre p where c.codeclient like p.param||'%'

Eventuellement :
Code:

1 2 3 select c.* from client c, (select replace(param,'%','') from parametre) p where c.codeclient like p.param||'%'
21/03/2006, 13h47
McM

Mais si les paramètres possibles sont '%DUP%' ou 'DU%ON%' ces requetes ne marcheront pas.

Dans quoi cette requete va s'effectuer ? (report, pl/sql, ecran...)
21/03/2006, 14h18
orafrance

de toutes façons si le début de la recherche n'est pas connu on passera pas par l'index. Lorsqu'il s'agit de faire une recherche dans des gros volumes, il est essentiel d'interdire les recherches tout azimut; le début de la chaîne de caractères doit être indiqué.

Citation:

Envoyé par Fred_D

de toutes façons si le début de la recherche n'est pas connu on passera pas par l'index.

ou plutôt, on "ne devrait pas passer" par l'index...

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
 
SQL> create table t as select 'ABCDEF' c, 1 x from dual;
 
Table created.
 
SQL> create table p as select '%C%' p  from dual;
 
Table created.
 
SQL> create index i on t(c);
 
Index created.
 
SQL> set autot on exp
SQL> select t.* from t,p where c like p||'%';
C               X
------ ----------
ABCDEF          1
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'P'
   4    2       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)

ci dessus, l'usage d'un index n'est pas bon...

21/03/2006, 15h41
orafrance

C'est normal ça... ta chaîne de caractère contient '%' en début de chaîne et l'index est construit sur ce début de chaîne. On a heureusement le droit d'indexer les colonnes qui commence par % :mrgreen:

En revanche si tu essayes :

Code:

select t.* from t,p where c like '%'||p||'%';

Tu devrais faire un FULL SCAN ;)

Citation:

Envoyé par Fred_D

C'est normal ça... ta chaîne de caractère contient '%' en début de chaîne et l'index est construit sur ce début de chaîne. On a heureusement le droit d'indexer les colonnes qui commence par % :mrgreen:

En revanche si tu essayes :

Code:

select t.* from t,p where c like '%'||p||'%';

Tu devrais faire un FULL SCAN ;)

ben j'ai essayé :-(
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SQL> select t.* from t,p where c like '%'||p||'%'; C X ------ ---------- ABCDEF 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'P' 4 2 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)

21/03/2006, 16h12
orafrance

Tiens... marrant... C'est probablement dû à l'index créé sur '%quelquechose'.

Mais dans ce cas, je mets mon billet que les perfs sont exécrables si le résultat attendu est la liste des lignes qui ne commencent pas par % ;)