!
I. Introduction▲
L'indexation plein texte, aussi appelée indexation de texte intégral, propose de fournir un service de recherche rapide d'informations déstructurées, basée sur des mots, parties de mots, expressions, formes fléchies, synonymes, etc. contenus dans une ligne d'une table.
Pour satisfaire cette demande SQL propose à l'origine différents prédicats comme LIKE ou SIMILAR et des fonctions particulières (SUBSTRING, CHARACTER_LENGTH…), mais leur utilisation s'avère vite compliquée et finalement extrêmement coûteuse sur un volume de données appréciable. En effet aucun index SQL classique ne permet d'accélérer de telles recherches.
II. Principe▲
Le principe d'un index textuel, commun aux différents SGBDR est simple :
- on découpe les mots de l'ensemble des phrases résultant de la concaténation des différentes colonnes à indexer ;
-
on les référence par rapport à :
- la table,
- la ligne dans la table,
- la colonne dans la ligne de table,
- la position ordinale du mot dans la colonne.
L'index textuel est donc composé de deux parties : la liste de tous les mots indexés, et la référence croisée entre les mots et leur position dans l'index textuel (table, ligne, position ordinale).
Bien entendu on ne tient pas compte des signes de ponctuation.
Enfin, il convient de gérer une liste de mots dits « noirs », (stop words, noise words), c'est-à-dire des mots vides de sens sur lesquels les recherches n'ont aucun intérêt, comme par exemple, les articles, les pronoms, etc.
III. Construction d'un index textuel▲
Chaque éditeur propose sa propre solution de création des index textuels, car la norme n'a rien prévu à ce sujet qui reste dans le domaine physique et non celui de la logique pure, SQL étant un langage purement logique.
III-A. Solution Oracle MySQL▲
Lors de la définition d'une table ou bien par modification de la table, on définit quelle(s) colonne(s) compose(nt) l'index textuel à l'aide de la fonction « FULLTEXT » dont la syntaxe est la suivante :
FULLTEXT [INDEX] [non_index] (colonne1 [, colonne2 […] ] ) [WITH PARSER nom_parser ]Exemple - création d'un index textuel MySQL :
CREATE TABLE T_LIVRE_LVR
(LVR_ID INT NOT NULL PRIMARY KEY,
LVR_TITRE VARCHAR(256) NOT NULL,
LVR_ANNEE_PARUTION SMALLINT,
LVR_RESUME CLOB,
LVR_TEXTE_INTEGAL BLOB,
FULLTEXT(LVR_TITRE, LVR_RESUME))Cela indexe les colonnes LVR_TITRE et LVR_RESUME en tant que candidat à la recherche plein texte.
III-B. Solution Microsoft SQL Server▲
Il faut procéder en deux temps : la création d'un catalogue d'indexation qui définit l'emplacement du stockage et la prise en compte de la sensibilité aux accents et autres caractères diacritiques puis dans un deuxième temps, créer autant d'index textuels que nécessaire, avec au moins un index par table à indexer, chaque index textuel pouvant avoir sa propre méthode de population et sa propre liste de mots noirs.
Exemple - 1er temps : création d'un catalogue d'indexation textuel SQL Server…
CREATE FULLTEXT CATALOG FTC_ROMANS
ON FILEGROUP STORAGE_FTS
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT;Ce qui crée un catalogue d'indexation textuelle, qui sera celui à défaut, dont les données seront stockées dans l'espace de stockage (filegroup) STORAGE_FTS et de nom FTC_ROMANS. Cet index ne sera pas sensible aux accents et autres caractères diacritiques (ACCENT_SENSITIVITY = OFF) et sera le catalogue d'indexation par défaut de tout nouvel index créé dans la base (AS DEFAULT).
Exemple - 2e temps : création d'un index textuel MS SQL Server…
CREATE FULLTEXT INDEX
ON T_LIVRE_LVR (LVR_TITRE LANGUAGE French,
LVR_RESUME LANGUAGE French,
LVR_TEXTE_INTEGAL TYPE COLUMN '.rtf' LANGUAGE French)
KEY INDEX PK_LVR
ON FTC_ROMANS
WITH ( CHANGE_TRACKING = MANUAL,
STOPLIST = SYSTEM);L'index textuel est créé sur la table T_LIVRE_LVR et sur les colonnes de langue française LVR_TITRE, LVR_RESUME et LVR_TEXTE_INTEGAL, avec pour ce dernier l'indication que cette colonne contient des fichiers électroniques au format RTF.
On indique en sus quel index unique servira de clef pour référencer les lignes de la table (ici PK_LVR qui est l'index sous-jacent à la clef primaire) et sur quel catalogue d'indexation textuel on greffe cet index textuel.
Enfin on définit la méthode de population qui peut être manuelle ou au fil de l'eau, et la liste des mots noirs qui peut être celle à défaut (SYSTEM), une liste particulière spécialement créée ou encore aucune (OFF).
IV. Possibilités de recherche▲
La norme SQL permet d'effectuer des recherches de mots dans les documents avec les limites suivantes :
- un ou plusieurs mots avec des combinaisons de ET (&), OU (|) et NON (NOT), au sein de la ligne de la table, d'un paragraphe au sein de la ligne ou encore d'une phrase au sein d'un paragraphe ;
- les mots peuvent être exprimés en partie à l'aide de jokers ;
- la recherche peut prendre en compte les formes fléchies des mots (pluriels, féminins, conjugaisons, etc.) ;
- la proximité de mots (en distance entre mots, phrases ou paragraphes) ;
- la recherche peut s'effectuer sur des synonymes ou des « expansions » ;
- elle peut inclure un floutage de l'expression (IS ABOUT) ou de certains mots (FUZZY).
La norme SQL propose plusieurs méthodes d'interrogation, à l'aide des fonctions CONTAINS, SCORE et NUMBEROFMATCHES, décrites ci-dessous.
IV-A. Recherches avec CONTAINS▲
La méthode CONTAINS('motif plein texte') retourne 1 si la recherche aboutit pour une ligne d'une table, sinon 0.
Exemple :
SELECT *
FROM T_LIVRE_LVR
WHERE CONTAINS ( ' "roman" | "nouve*" ') = 1Recherche les lignes de la table T_LIVRE_LVR contenant le mot roman ou un mot commençant par nouv (comme nouveau, nouvel, nouveaux…).
IV-B. Recherches avec SCORE▲
La méthode SCORE('motif plein texte') renvoie un DOUBLE_PRECISION (réel 64 bits) donnant un indice de pertinence de la recherche.
Exemple :
SELECT *
FROM T_LIVRE_LVR
WHERE SCORE ( ' "roman" | "nouve*" ') > 0.5Recherche les lignes de la table T_LIVRE_LVR contenant le mot « roman » ou un mot commençant par « nouve » (comme nouveau, nouvel, nouveaux…) avec un score supérieur à 0,5.
IV-C. Recherches avec NUMBEROFMATCHES ▲
La méthode NUMBEROFMATCHES('motif plein texte') renvoie un INTEGER (entier) indiquant le nombre de fois où le mot ou bien une phrase a été trouvé dans la ligne.
Exemple :
SELECT *
FROM T_LIVRE_LVR
WHERE NUMBEROFMATCHES ( ' "roman * nouve*" ') BETWEEN 1 AND 2Recherche les lignes de la table T_LIVRE_LVR contenant une phrase constituée du mot « roman » suivi d'un mot commençant par « nouve » (comme nouveau, nouvel, nouveaux…) quelque soit la distance entre ces deux mots et à condition que ce motif soit présent 1 à 2 fois.
V. Motifs de recherche textuelle▲
Toute la difficulté de la recherche textuelle repose sur le paramètre constituant la recherche elle même qui est de type FT_pattern, dérivé du type CHARACTER VARYING avec une longueur maximale dépendante de l'implémentation qui est fait par chaque éditeur de SGBDR.
Un motif plein texte est une chaîne de caractères composée des mots ou expressions à chercher accompagnés d'éventuels indicateurs. Chaque mot ou expression doit être entouré de guillemets.
Exemples divers…
Type de recherche Expression
------------------------------ ---------------------------------------------------------
Mot simple ' "roman" '
Expression ' "tire bouchon" '
' "tire-bouchon" '
Mot avec joker ' "nouve_*" '
Différents mots (ou) ' "guerre", "paix" '
Synonyme de "guerre" ' THESAURUS "militaire" EXPAND SYNONYM TERM OF "guerre" '
Expansion de "guerre" ' THESAURUS "militaire" EXPAND BROADER TERM OF "guerre" '
Forme fléchie de "nouveau" ' STEMMED FORM OF FRENCH "nouveau" '
Mots dans une même phrase ' "tristesse" IN SAME SENTENCE AS "vague" '
Mots dans un même paragraphe ' "tristesse" IN SAME PARAGRAPH AS "vague" '
Mots proches ' "guerre" NEAR "paix" WITHIN 3 WORDS IN ORDER '
' "guerre" NEAR "paix" WITHIN 60 CHARACTERS '
' "guerre" NEAR "paix" WITHIN 3 SENTENCES IN ORDER '
' "guerre" NEAR "paix" WITHIN 2 PARAGRAPHS '
Terme flou via Soundex ' SOUNDS LIKE "guerre" '
Terme flou spécifique ' FUZZY FORM OF "russie" '
Texte flou ' IS ABOUT "la guerre et la paix en Russie" '
Combinaison de recherches ' ("roman" & "nouv_*" & STEMMED FORM OF FRENCH "vague") | (THESAURUS "militaire" EXPAND SYNONYM TERM OF "guerre" & "paix") 'Pour les synonymes et expansions, il faut créer différents thesaurus. Dans notre exemple celui qui porte le nom « militaire ». En effet un thésaurus est spécifique à une sémantique particulière. Par exemple le mot four n'a pas la même signification en matière de cuisine ou en matière de théâtre…
Une expansion consiste à trouver une expression à partir d'un terme simple. Par exemple pour le mot guerre on peut définir dans le thésaurus, les expressions « conflit armé » et « engagement militaire ».
Une forme fléchie est une déclinaison d'un mot dans ses diverses acceptations grammaticales : pluriel, féminin, conjugaison.
La recherche de mots proches peut se faire dans l'ordre de lecture ou indifféremment (option IN ORDER) et dans un espace limité aux mots, phrases ou paragraphes (par exemple au plus trois mots).
Pour la recherche avec un terme flou spécifique, chaque éditeur peut proposer son propre algorithme éventuellement enfichable. Par exemple, un Levenshstein.
Pour la recherche de texte flou, chaque éditeur peut proposer sa méthode, à l'image de ce que fait Google par exemple.
VI. Traitement des mots noirs▲
Pour ne pas tenir compte des mots noirs, l'expression de recherche peut commencer par la référence de langue.
Exemple :
-
'FRENCH"laguerreetlapaixenRussie"'
Propose une méthode de recherche équivalente à l'expression :
-
'FRENCH"guerrepaixRussie"'
En supposant que les mots « la », « et » et « en » sont des mots noirs relatifs à la langue FRENCH.
VII. Comparaisons des solutions▲
VII-A. Mise en place de l'indexation textuelle▲
Les limitations sont sévères avec Oracle MySQL. En effet :
- l'indexation textuelle n'est possible que pour des tables de format ISAM ;
- l'index est peuplé de manière synchrone ce qui ralentit les mises à jour ;
- il n'est pas possible d'indexer des documents électroniques contenus dans une colonne de type BLOB ;
- la sensibilité aux accents n'est pas paramétrable. La recherche est insensible à la casse ;
- certains jeux de caractères ne sont pas supportés (en particulier UNICODE) ;
- toutes les colonnes d'un même index doivent avoir le même jeu de caractères et la même collation ;
- il n'existe qu'une seule liste de mots noirs pour toutes les langues, mais elle est paramétrable ;
- MySQL considère comme mot noir tout mot de moins de n caractères, n étant paramétrable (paramètre ft_min_word_len) et à défaut de 4 ;
- il n'est pas possible de faire des recherches de synonyme ou d'expansion, car MySQL n'implémente pas de thésaurus.
Compte tenu de ces limitations, l'indexation opère a peu près correctement si une seule langue ne comportant aucun accent constitue l'essentiel des données. Compte tenu que que notre langue française comporte de nombreux accents et la cédille, l'indexation MySQL s'avère difficilement exploitable en production.
Particularité des index textuels Microsoft SQL Server :
- l'index est peuplé de manière asynchrone, soit au fil de l'eau, soit de manière manuelle (par exemple avec une planification journalière) en totalité (reconstruction) ou par différence (un journal de suivi est activé dans ce dernier cas) ;
- SQL Server indexe tous les mots, même les mots noirs quelle que soit leur longueur. Ce n'est qu'à la restitution que les mots noirs sont ignorés ou pas et en fonction de la langue choisie ;
- il est possible d'indexer n'importe quel type de documents électroniques à l'aide des ifilters (dll d'extraction de texte par format de fichiers standardisés). En standard, SQL Server propose 50 formats parmi les plus courants (.doc, .htm, .html, .ini, .log, .ppt, .rtf, .txt, .url, .xls, .xml…) ;
- la sensibilité aux accents et autres caractères diacritiques est réglée au niveau du catalogue. La recherche est toujours insensible à la casse ;
- il est possible d'effectuer des recherches multilingues pourvu que l'on puisse repérer la langue dans laquelle l'information a été saisie (par exemple en ajoutant à la table une colonne indiquant la langue). Dans ce cas il est inutile de préciser la langue lors de la construction de l'index ;
- il est possible de créer ses propres listes de mots noirs en fonction de chacune des langues ;
- SQL Server implémente un thésaurus par langue qui se présente sous la forme d'un document XML éditable et permet la recherche des synonymes ou des expansions.
Exemple - extrait d'un thésaurus Microsoft SQL Server en langue française pour la recherche de couleurs dans des textes :
<XML ID="Microsoft Search Thesaurus">
<!-- Commented out (SQL Server 2008)
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>couleur</sub>
<sub>teinte</sub>
<sub>coloris</sub>
<sub>nuance</sub>
</expansion>
<replacement>
<pat>bleu</pat>
<sub>azur</sub>
<sub>bleu acier</sub>
<sub>bleu canard</sub>
<sub>bleu ciel</sub>
<sub>bleu cobalt</sub>
<sub>bleu de Prusse</sub>
<sub>bleu électrique</sub>
<sub>bleu Klein</sub>
<sub>bleu Majorelle</sub>
<sub>bleu nuit</sub>
<sub>bleu outremer</sub>
<sub>bleu pétrole</sub>
<sub>cyan </sub>
<sub>indigo</sub>
<sub>lavande</sub>
<sub>marine</sub>
<sub>pervenche</sub>
<sub>saphir</sub>
<sub>turquoise </sub>
</replacement>
...
</thesaurus>
-->
</XML>Dans cet exemple les mots « couleur », « teinte », « coloris » et « nuance » sont considérés comme synonymes (substitute) pour la recherche (expansion). Quand au mot bleu » (pattern) il ne sera pas recherché, mais les mots et expressions comme « azur » ou « bleu acier » le seront (remplacement).
La seule véritable restriction de MS SQL Server est de ne permettre qu'un seul thésaurus par langue alors qu'il aurait fallu le faire par langue/sémantiques.
VII-B. Interrogation dans l'index textuel▲
Oracle MySQL propose un seul prédicat de recherche en tout et pour tout MATCH/AGAINST. Il n'est pas conforme à la norme SQL.
SQL Server propose quatre méthodes pour le recherche textuelle. Deux prédicats : CONTAINS et FREETEXT, et deux fonctions tables : CONTAINSTABLE et FREETEXTTABLE. CONTAINS est l'un des prédicats conforme à la norme SQL.
Les possibilités de recherche des deux SGBDR sont présentées dans le tableau suivant :
Notons que sur treize possibilités, MySQL n'en accepte que cinq et avec des restrictions importantes : liste de mots noirs par langue impossible, gestion des caractères diacritiques mal organisée, combinaisons booléennes très limitées, pas d'indexations de document électroniques…
En revanche, MS SQL Server en accepte directement dix, et il est possible de réaliser manuellement une recherche par terme flous, car SQL Server met à disposition des développeurs les tables système contenant les mots indexés, comme l'emplacement des mots dans les données. Ceci permet aussi de compter le nombre de mots ou d'expressions correspondant à une recherche précise et permet de simuler le prédicat normatif NUMBEROFMATCHES. Enfin, outre la publication de résultats pondérés (équivalent du prédicat normatif SCORE) SQL Server offre la possibilité de prévoir une pondération des expressions recherchées.
Exemple :
CONTAINSTABLE (dbo.T_CRASH_CRH, *,
' ISABOUT (FORMSOF (INFLECTIONAL, "guerre") weight (.6),
FORMSOF (INFLECTIONAL, "napoléon") weight (.8),
"russie" weight (.7))')VIII. Nouvelles fonctionnalités avec SQL Server 2012▲
SQL Server 2012 permet de recherche de manière plus précise les mots proches en spécifiant une distance maximum.
Exemple :
SELECT *
FROM T_ANNONCE_ANC
WHERE CONTAINS(ANC_RESUME 'NEAR((expert, "SQL Server"), 5, TRUE)');Qui signifie, cherche les ligne de la table T_ANNONCE_ANC dont la colonne ANC_RESUME contient les termes « expert » et « SQL Server » dans cet ordre et avec une distance maximale de cinq mots (y compris mots noirs).
On peut en sus avoir une idée de l'imbrication des mots dans les phrases et paragraphes à l'aide de la fonction table sys.dm_fts_parser qui indique les césures de phrases et de paragraphes.
En sus, SQL Server rajoute la recherche « sémantique », soit en indiquant à SQL Server quels sont les tags délimitant les zones de texte (par exemple pour un document XML ce peut être des balises de type attributs que l'on repère), soit en utilisant des dictionnaires intégrés.
IX. Conclusion▲
La complexité d'une recherche textuelle pertinente est évidente et les solutions retenues par les deux éditeurs sont diamétralement opposées. Sans tenir compte des performances, pour le cas de MySQL il s'agit de faire croire que cela existe dans le SGBDR avec comme résultat quelque chose de difficilement exploitable en production, surtout pour des langues latines constituées pour beaucoup de mots accentués. En sus, le moteur d'indexation textuel de MySQL est mal programmé et arrive à planter le serveur dans le cas d'une trop forte charge (passage en un seul lot d'un fort volume de données à indexer, comme cela arrive lors des imports de données).
En revanche pour SQL Server on est très proche d'une solution implémentant toutes les possibilités de recherches de la norme SQL et facile à exploiter tant en matière fonctionnelle qu'en matière administrative. Avec SQL Server, l'accès aux données indexées, comme les méthodes de gestion de l'index sont nombreuses et permettent d'indexer d'énormes volumes de données sans que cela ne pénalise les ressources du serveur, notamment via l'indexation asynchrone en mode fil de l'eau. De même, la recherche est très efficace et même en concurrence ne pénalise pas fortement le serveur.
En savoir plus…
À lire en complément.
- Principes de l'indexation textuelle (un écrit qui date un peu !) : http://sqlpro.developpez.com/cours/indextextuelle/.
- Les soundex et autres fonctions de consonances : http://sqlpro.developpez.com/cours/soundex/.
- Des fonctions de comparaison de motifs : http://sqlpro.developpez.com/cours/sql/comparaisons-motifs/.