Simple Recherche LIKE très lente

**omar_elid** · 07/09/2011, 17h10

Bonjour,

Mon problème est relatif au temps d'exécution des requêtes de recherche.

En effet, par exemple lorsque j'exécute la requête suivante :

Code :

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

Select count(*) from REFCLPPP00 where CLPNOMCLIP LIKE 'touir%'

La c'est OK ! Le serveur répond en moins d’1ms.

Tandis que, si j'ajoute un "%" à gauche du mot à chercher, la requête prend une décennie pour s'exécuter ( une moyenne d'1min30s).

Code :

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

Select count(*) from REFCLPPP00 where CLPNOMCLIP LIKE '%touir%'

Je comprends que cette requête est plus compliquée que la première, mais un tel temps de réponse est loin d’être tolérable sachant que je suis contraint d'un timeOut de 30 s.

Je n’arrive pas à déceler l'origine du problème, est-ce que je dois modifier une propriété d'oracle ?

Merci, d'avance.

À noter que :
le champ CLPNOMCLIP est indexé.
La table comporte 1894277 entrées.

**StringBuilder** · 07/09/2011, 19h19

le fait d'avoir un % en début et un % en fin empêche d'utiliser les index.

**iberserk** · 07/09/2011, 19h24

La collation influence largement le travail du processeur lors de ce type de recherche.

Quelle est la COLLATION de votre colonne CLPNOMCLIP?
Tester une comparaison avec COLLATION BINAIRE?

**pachot** · 07/09/2011, 22h12

Bonjour,

Envoyé par omar_elid

Bonjour,
Je n’arrive pas à déceler l'origine du problème

C'est juste normal pour un index.
Imagines que tu doives chercher dans un dictionnaire tous les mots qui commencent par 'touir' : ça ira très vite.
Imagines que tu doives chercher tous les mots qui contiennent 'touir' ... il faudra lire toutes les pages...

Oracle Text a des possibilités pour indexer des mots.

Cordialement,
Franck.

**omar_elid** · 07/09/2011, 23h57

Merci pour vos réponses.

iberserk si vous parlez de la Collation NLS_SORT, elle est en binaire.

StringBuilder je confirme que dans le cas d'une recherche avec l'indexation simple n’optimise en aucun cas le temps d’exécution des requêtes.

pachot, je vais suivre cette piste, d'après une première recherche je pense que c'est le bout de file de la solution, j' approfondirai mes connaissances sur Oracle Text indexation.

je vais vous tenir au courant demain mon avancement

**iberserk** · 08/09/2011, 09h38

Oracle Text a des possibilités pour indexer des mots.

>pachot
Je ne connais pas ORACLE parfaitement mais le FULLTEXT (puisque c'est de ca qu'il s'agit) ne permet pas de reproduire tout ce que peut faire un LIKE %%.

Par exemple une recherche sur an ne retournera pas intervenant...

Mais si vos recherches portent sur des mots, c'est votre solution en effet.

**laurentschneider** · 08/09/2011, 10h58

Code :

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

SELECT count(*) FROM REFCLPPP00 WHERE CLPNOMCLIP LIKE '%touir%'

Plusieurs approches :

1) tu sais (mais Oracle ne le sait pas) que seulement peu de mots contiennent touir, donc tu utilises un hint

--> http://laurentschneider.com/wordpres...me-likebc.html

2) tu cherches toujours sur '%touir%', tu crées un fbi

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select count(*) from t where txt LIKE '%toui%';
  COUNT(*)
----------
        24
Elapsed: 00:00:00.04
SQL> create index fbi on t(case when txt LIKE '%toui%' then 1 end);
 
Index created.
SQL> select count(*) from t where case when txt LIKE '%toui%' then 1 end=1;
  COUNT(*)
----------
        24
Elapsed: 00:00:00.01

**Yanika_bzh** · 08/09/2011, 14h48

L'idée est interessante, mais le resultat du temps de requetage de la solution prend aussi en compte le temps de creation de l'index ?

**Mohamed.Houri** · 08/09/2011, 15h31

Envoyé par Yanika_bzh

L'idée est interessante, mais le resultat du temps de requetage de la solution prend aussi en compte le temps de creation de l'index ?

Je ne comprends pas votre réponse.

Voulez vous dire que le temps de réponse de la requête dépend du temps de création de l'index!!!!!

**Yanika_bzh** · 08/09/2011, 15h45

Non je n'affirme rien, je voulais juste savoir quel etait le temps total Creation de l'index + requete select, car il faut bien créer l'index non ?

Pour résumer

Cas A
Requete (Non indexée) X secondes

Cas B
Creation d'un index : Y Secondes
Requete (indexée) : Z Secondes.

Y+Z = ?

**Mohamed.Houri** · 08/09/2011, 15h54

Envoyé par Yanika_bzh

Non je n'affirme rien, je voulais juste savoir quel etait le temps total Creation de l'index + requete select, car il faut bien créer l'index non ?

Pour résumer

Cas A
Requete (Non indexée) X secondes

Cas B
Creation d'un index : Y Secondes
Requete (indexée) : Z Secondes.

Y+Z = ?

On créé l'index une seule fois et on fait des selects plusieurs fois.

Il faut plutôt parler de la pénalité lors de l'insert/update/delete que nous ajoutons par la création d'un index et non sur la pénalité de celui-ci lors d'un select.

**Yanika_bzh** · 08/09/2011, 16h00

Envoyé par Mohamed.Houri

On créé l'index une seule fois et on fait des selects plusieurs fois.

Il faut plutôt parler de la pénalité lors de l'insert/update/delete que nous ajoutons par la création d'un index et non sur la pénalité de celui-ci lors d'un select.

Ca limite quand meme les fonctions de recherche :

Code :

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

CREATE INDEX fbi ON t(case when txt LIKE '%toui%' then 1 end);

Ok pour rechercher toui dans la chaine,
Mais si apres je veux chercher uoit dans la chaine ? il faut que je refasse un index ?

**laurentschneider** · 08/09/2011, 16h05

si l'on cherche toujours la même chaine (touir), alors ça va. Sinon ce n'est pas la bonne approche

dans le cas où on checher tata, titi ou toto, à tours de role, on pourrait faire un

Code :

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

1
2
3
4
5
case 
  when t like '%tata%' then 1 
  when t like '%titi%' then 2
  when t like '%toto%' then 3
end

et tester si le case indexer vaut 1 ou 2 ou 3.

Bien sûr la meilleures façon d'augmenter la performance d'une application qui fait toujours des like '%xxx%' c'est de ne pas faire le like

**Yanika_bzh** · 08/09/2011, 16h10

je suis d'accord

**omar_elid** · 09/09/2011, 11h29

Bonjour,

en essayant d'optimiser le temps de réponse des requêtes LIKE "%%". Je me suis intéressé à l'idée proposée par "pachot" celle de Oracle Text.

Conclusion, Oracle Text crée des indexes sur les mots qui constituent le champ en question.

Par exemple : si on a un prénom Jean MARC, Oracle Text va créer un index sur Jean et un autre sur Marc.

Par concéquent, cette méthode s'avère fructueuse pour notre cas.

Ayant grand espoir, j'ai trouvé sur la doc officielle d'oracle qu’on peut configurer la création de l index en lui spécifiant des paramètres précisément le "SUBSTRING" = TRUE. Dans ce cas, et pour le même exemple Oracle va créer des indexes sur toutes les combinaisons possibles de la chaine de caractère, ainsi on aura :

j,e,a,n,m,a,r,c,Je,ea,an,ma,ar,rc,jea,ean,anm,.... enfin ne croyez pas que je vais tout écrire

mathématiquement on aura n(n+1)/2 = 36 indexes créé.

Faisant le calcul pour 1894277 données : 2000000*36 = 72 000 000 indexes

Curieux, je l ai exécuté et le serveur à planter aère 13 minutes d'indexation

..

Enfin Merci à vous, je pense qu'il faut revoir la contrainte de 30s que d'essayer d'optimiser le temps d'exécution des requêtes LIKE "%%".

Tout de même si vous avez d'autres propositions je suis preneur.

**ORA-007** · 09/09/2011, 17h47

Bonjour,

Dans le cas d'un

Code :

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

LIKE '%%'

tu ne pourras pas eviter un fullscan, mais un axe d'optim est de limiter le nombre de lignes sur lesquelles Oracle va scanner.

Par exemple creer un FBI sur la longueur de ta chaine :

Code :

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

CREATE INDEX INDX_REFCLPPP00_01 ON REFCLPPP00(length(CLPNOMCLIP ));

Ensuite

Code :

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

1
2
3
4
5
6
 
SELECT count(*) 
FROM REFCLPPP00 
WHERE 1=1
AND length(CLPNOMCLIP ) >= length('touir')
AND CLPNOMCLIP LIKE '%touir%'

Evidemment si le champ CLPNOMCLIP est de la meme longueur pour chaque record alors ça ne sert à rien

Autre exemple, indexer sur la présence des lettres de l'alphabet A->Z.

Certain me diront que ce genre d'indexation n'a pas grand interet vu le faible taux de selectivité de l'indexe et que oracle prefera ne pas l'utiliser , et ils n'auront pas tord . Par contre la proba que ton champ contienne à la fois les lettres t,o,u,i et r devient interessante.

**omar_elid** · 09/09/2011, 21h45

Oui utiliser un index sur la longueur de la chaîne ca peut optimiser quelques recherches.

Simple Recherche LIKE très lente

SQL Oracle

Discussions similaires

Partager

Partager