Le langage SQL incorpore dans sa norme SQL ISO 13249 des extensions pour la recherche plain texte (2 – Full-Text).

La recherche plain texte permet de retrouver des données contenant certains mots, expressions ou formes fléchies de mots, synonymes, etc. dans les lignes des tables, y compris pour des colonnes de type LOB pouvant contenir de grand textes (CLOB, NCLOB) voire des fichiers électroniques binarisés (BLOB).
Cet article propose une comparaison de la norme avec les solutions proposées par Oracle MySQL 5.0 et Microsoft SQL Server 2008 pour l’indexation textuelle aussi appelée indexation de texte intégrale et son corolaire, la recherches plain texte (ou encore la recherche en texte intégral – Full text search).

NOTA : une partie de cet article est extraite du chapitre 8 du Livre « SQL (3e édition)« , collection Synthex – Pearson Education 2010 – Auteurs : Frédéric Brouard, Christian Soutou, Rudi Bruchezouverez de nombreux autres commentaires sur ce sujet dans cet ouvrage.

0 – Introduction :

L’indexation plain texte, aussi appelée indexation de texte intégrale, 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. contenu 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 couteuse sur un volume de données appréciable. En effet aucun index SQL classique ne permet d’accélérer de telles recherches.

1 – 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 vide de sens sur lesquels les recherches n’ont aucun intérêt, comme par exemple les articles, les pronoms, etc.

2 – Construction d’un index textuel

Chaque éditeur propose sa propre solution de création des index textuel, 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.

2.1 – 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))

Ceci indexe les colonnes LVR_TITRE et LVR_RESUME en temps que candidat à la recherche plain texte.

2.1 – 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éé 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).

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

3.1 – Recherches avec CONTAINS

La méthode CONTAINS( ‘ motif plain 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*" ') = 1

Recherche les lignes de la table T_LIVRE_LVR contenant le mot roman ou un mot commençant par nouv (comme nouveau, nouvel, nouveaux …).

3.2 – Recherches avec SCORE

La méthode SCORE( ‘ motif plain 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.5

Recherche les lignes de la table T_LIVRE_LVR contenant le mot roman ou un mot commençant par nouv (comme nouveau, nouvel, nouveaux …) avec un score supérieur à 0,5.

3.3 – Recherches avec NUMBEROFMATCHES

La méthode NUMBEROFMATCHES( ‘ motif plain 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 2

Recherche les lignes de la table T_LIVRE_LVR contenant une phrase constituée du mot roman suivi d’un mot commençant par nouv (comme nouveau, nouvel, nouveaux …) quelque soit la distance entre ces deux mots et à condition que ce motif soit présent 1 à 2 fois.

4 – 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 plain texte est une chaine 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 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 3 mots).
Pour la recherche avec un terme flou spécifique, chaque éditeurs peut proposer son propre algorithme éventuellement plugable. 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.

5 – 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 "la guerre et la paix en Russie" '
Propose une méthode de recherche équivalente à l’expression :
' FRENCH "guerre paix Russie" '
En supposant que les mots la, et et en sont des mots noirs relatifs à la langue FRENCH.

6 – Comparaisons des solutions

6.1 – 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 qu’en 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.

6.2 – interrogation dans l’index textuel

Oracle MySQL propose 1 seul prédicat de recherche en tout et pour tout MATCH/AGAINST. Il n’est pas conforme à la norme SQL.

SQL Server propose 4 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 :

Cliquez ici pour une image plus grosse

Notons que sur 13 possibilités, MySQL n’en accepte que 5 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 10, 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))')

7 – 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 5 mots (y compris mots noirs.
On peut en sus avoir une idée de l’imbrication des mots dans les phrases et paragrpahes à l’aide de la fonction table sys.dm_fts_parser qui indique les césures de phrases et de paragraphe.

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.

8 – conclusion

La complexité d’une recherche textuelle pertinente est évidente et les solutions retenues par les deux éditeurs sont diamétralement opposés. 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…

A 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 consonnances : http://sqlpro.developpez.com/cours/soundex/
• Des fonctions de comparaison de motifs : http://sqlpro.developpez.com/cours/sql/comparaisons-motifs/

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *