[tuning] like avec variable

**batin** · 13/03/2006, 22h13

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 :

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

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

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

Code :

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

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

**Jaouad** · 14/03/2006, 00h37

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 :

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

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é

**batin** · 14/03/2006, 00h48

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

**McM** · 14/03/2006, 00h49

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 :

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

1
2
SELECT c.* FROM client c
WHERE c.codeclient LIKE (SELECT p.param FROM parametre p)

va mieux ?

**batin** · 14/03/2006, 01h02

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 :

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

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

**Jaouad** · 14/03/2006, 01h36

batin

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

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

**batin** · 20/03/2006, 21h48

J'ai essayé le code :

Code :

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

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 ?

**McM** · 20/03/2006, 22h07

Essayes de mettre un HINT

Code :

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

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

**HarryCoco** · 20/03/2006, 22h07

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.

**orafrance** · 21/03/2006, 11h33

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 :

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

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

où p_mon_parametre est la valeur de début, ici DUP

**McM** · 21/03/2006, 11h36

parce qu'il peut y avoir plusieurs p_mon_parametre .

**laurentschneider** · 21/03/2006, 11h56

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

**laurentschneider** · 21/03/2006, 12h02

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

**laurentschneider** · 21/03/2006, 12h04

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

**laurentschneider** · 21/03/2006, 12h15

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

**orafrance** · 21/03/2006, 12h53

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 :

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

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

Eventuellement :

Code :

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

1
2
3
 
select c.* from client c, (select replace(param,'%','') from parametre) p
where c.codeclient like p.param||'%'

**McM** · 21/03/2006, 14h47

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

**orafrance** · 21/03/2006, 15h18

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

**laurentschneider** · 21/03/2006, 16h24

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 :

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

**orafrance** · 21/03/2006, 16h41

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 %

En revanche si tu essayes :

Code :

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

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

Tu devrais faire un FULL SCAN

[tuning] like avec variable

Oracle

Discussions similaires

Partager

Partager