Simple Recherche LIKE très lente

[omar_elid]

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 :

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 :

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]

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

[iberserk]

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]

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]

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]

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]

Code :

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 :

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]

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]

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]

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]

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]

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 :

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]

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 :

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]

je suis d'accord

[omar_elid]

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]

Bonjour,

Dans le cas d'un

Code :

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 :

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

Ensuite

Code :

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]

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