Discussion :

[StringBuilder]

Bonjour,

J'ai une table dédiée à la recherche littérale.

Sa structure est : clé primaire (constituée de plusieurs colonnes), et une colonne "TEXTE" varchar2(4000)
Il y a un index CTXCAT sur la colonne "TEXTE".

La table fait quelques centaines de milliers de lignes.

Si on effectue ces requêtes :

Code sql :

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

select * from matable where catsearch(texte, 'cha', '')

=> On a moins d'une dizaine de lignes retournées au bout de 2 secondes.

Code sql :

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

select * from matable where catsearch(texte, 'cha*', '')

=> On a une vingtaine de résultats au bout de 2 minutes

Code sql :

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

select * from matable where texte like '%cha%'

=> On a une cinquantaine de lignes au bout de moins d'une seconde

Comment est-ce possible que le LIKE soit à se point plus rapide ?
Mise à part le résultat légèrement différent, j'ai toujours cru que l'avantage principal d'un index littéral était d'aller plus vite qu'un LIKE qui ne peut pas utiliser les index...

On a essayé un rebuild index : ça a effectivement gagné un peu, mais rien d'extraordinaire...

Comment optimiser cet index ? Si le like dure moins d'une seconde, le ctxcat devrait aussi durer moins d'une seconde non ?

[a.presles]

dans ton cas le like '%CHAT%' va faire un full sur ta table et il est possible que le full soit la solution la plus rapide. Cela si la table est en mémoire, la vélocité de tes disques. pour comparer l'efficacité des requête il vaut mieux regarder le nombre de bloc lu. tu auras cette information en faisant un autotrace de tes requêtes.

[skuatamad]

Un index CTXCAT ne semble pas le plus approprié pour ce genre de recherche :
https://docs.oracle.com/cd/B28359_01...d.htm#BEIIEAFD

Use this index type for better mixed query performance. Typically, with this index type, you index small documents or text fragments. Other columns in the base table, such as item names, prices, and descriptions can be included in the index to improve mixed query performance.

[StringBuilder]

J'ai grandement simplifié la requête pour l'exemple, en réalité elle est bien différente.
Si le "mixed" indique "filtrer sur des éléments de l'index ainsi que sur les autres colonnes inclues dans l'index" c'est bien le cas.

Notamment les colonnes de la clé primaire sont dans l'index et on filtre dessus.

Ma question concerne plutôt la maintenance de cet index.

En effet, je ne sais pas du tout comment Oracle le rempli ni le maintien ensuite.

Sous SQL Server par exemple, l’alimentation d'un index littéral peut être différée. Dans ce cas, les résultats sont faux si on l'interroge avant qu'il ne soit alimenté complètement. Voir même l'index peut être ignoré s'il n'est pas assez fiable.

Qu'en est-il d'Oracle ? Comment forcer un rebuild de l'index ? J'ai essayé la syntaxe habituelle, mais je me demande si ça a bien rebuildé l'index entier, ou seulement les colonnes inclues...

Ensuite, y a-t-il des paramètres mémoire, CPU à prendre en compte pour ce type d'index ?

Car vu les performances qu'on se paie comparé à un LIKE, il y a forcément quelque chose qui cloche grave avec notre index...

[skuatamad]

Il existe plusieurs type d'index textuel, dans le lien fourni ci-dessus il est précisé :

CONTEXT : This index type requires CTX_DDL.SYNC_INDEX after DML on base table.
CTXCAT : This index type is transactional, automatically updating itself after DML to base table. No CTX_DDL.SYNC_INDEX is necessary.

Peut être qu'un index de type CONTEXT sera plus approprié aux types de requêtes utilisées.

La colonne TEXTE est définie en VARCHAR2(4000), contient elle vraiment beaucoup de texte ?
Par contre je n'ai pas particulièrement d'expérience sur ce genre de sujet.

[StringBuilder]

Cette colonne contient la concaténation du texte contenu dans plusieurs colonnes de plusieurs tables.

Pour un client, on a par exemple le nom du client, la raison sociale, son adresse ainsi que quelques caractéristiques qui sont concaténées.

Le but étant par exemple de pouvoir rechercher toutes les "pharmacie herboriste lyon" où chacun des termes provient d'une colonne distincte.

Je suis le premier choqué par la structure et l'existence de cette table : il doit exister dans Oracle un type d'index textuel capable de porter sur plusieurs colonnes.
Seulement ici les colonnes indexées change d'un type de tiers à un autre (entre un client et un fournisseur on ne va pas indexer les mêmes caractéristiques par exemple) et ça doit être dynamique. Du coup des triggers alimentent cette table en fonction du paramétrage, et il n'y a plus qu'un indexer une unique colonne dans une unique table. C'est pas franchement terrible, mais en soit ça devrait marcher pas trop mal au vue de la volumétrie (quelques centaines de milliers de ligne avec généralement moins de 1000 caractères - je dirais même la plupart du temps, moins de 200 caractères -)

La structure de la table est la suivante :

codsoc, typtie, sigtie, texte

codsoc, typtie, sigtie sont uniques et une clé externe vers la table des tiers.

codsoc est l'entité juridique, et typtie est le type de tiers.

Ainsi, quand on recherche les "pharmacie herboriste lyon" on a tout intérêt à pouvoir filtrer sur codsoc (l'entité juridique courante) et typtie (client).
Ceci afin de réduire drastiquement le volume de la recherche lexicale.

Visiblement, CTXCAT permet de faire une recherche sur ces colonnes complémentaires.

Seulement, j'ai l'impression que le souci vient de là...

le "index set" est vide. Donc contre toute attente, "codsoc" et "typtie" ne font pas partie de l'index.
Idem, la requête de recherche ne filtre pas correctement ces colonnes...

On a :

Code sql :

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

1
2
 
WHERE CATSEARCH (texte, 'pharmacie herboriste lyon', null) > 0 and codsoc = 102 and typtie = 'CLI'

Au lieu de :

Code sql :

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

1
2
 
WHERE CATSEARCH (texte, 'pharmacie herboriste lyon', 'codsoc = 102 and typtie = ''CLI''') > 0

Notre problème de performances ne viendrait-il pas de là ?

Ceci dit, je reste quand même perplexe par la lenteur... Car même si on scanne tout l'index pour rien, un index textuel est censé être très véloce (surtout pour une recherche aussi simple) et là ce n'est pas le cas du tout.
Dans notre situation, "codsoc = 102 and typtie = 'CLI' " ne filtre pas énormément (on divise environ par 2 le nombre de lignes recherchées). Pas vraiment de quoi expliquer comment un LIKE combiné à un range scan sur la clé primaire peut être autant plus véloce.

Sinon, question...

A la base le filtre sur codsoc et typtie se fait sur une autre table, et ensuite par jointure. C'est à dire que lorsque je lance la requête, je ne connais pas les valeurs littérales de codsoc et typtie... Sâchant qu'à la base je pourrais avoir plusieurs valeurs de typtie et codsoc...

Code sql :

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

1
2
3
4
5
6
 
select tie.sigtie
from tie
inner join tie_rec on tie_rec.codsoc = tie.codsoc and tie_rec.typtie = tie.typtie and tie_rec.sigtie = tie.sigtie
WHERE << filtre sur tie >>
and CATSEARCH (tie_rec.texte, 'pharmacie herboriste lyon', null) > 0

Comment convertir cette requête pour pouvoir utiliser le troisième argument de la fonction CATSEARCH ???

Code sql :

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

1
2
3
4
5
6
 
select tie.sigtie
from tie
inner join tie_rec on tie_rec.codsoc = tie.codsoc and tie_rec.typtie = tie.typtie and tie_rec.sigtie = tie.sigtie
WHERE << filtre sur tie >>
and CATSEARCH (tie_rec.texte, 'pharmacie herboriste lyon', 'codsoc = tie.codsoc and typtie = tie.typtie') > 0

????