This post is a compilation of informations about\u00a0 the use of Oracle Text.\u00a0The particular context of Name Matching is illustrated by some features extracted from the documentation. 28 juin 2011 – Oracle V 0.1<\/p>\n
Il existe deux types d’index qui sont adapt\u00e9s \u00e0 l’optimisation des recherches sur des textes:<\/p>\n
En principe, CTXCAT est mieux adapt\u00e9 pour l’indexation des fragments de texte courts (des noms, par exemple) . cela n’emp\u00eache pas l’utilisation du premier type\u00a0: CONTEXT.<\/p>\n
The\u00a0CTXCAT\u00a0indextype is well-suited for indexing small text fragments and related information. If created correctly, this type of index can provide better structured query performance over a\u00a0CONTEXT\u00a0index.<\/p>\n
<\/p>\n
Lors de la cr\u00e9ation d’un index de type CONTEXT, on pr\u00e9cise en g\u00e9n\u00e9ral un jeu de pr\u00e9f\u00e9rences qui agit sur le comportement des recherches ult\u00e9rieures, vis-\u00e0-vis notamment de r\u00e8gles grammaticales.<\/p>\n
On peut dire que les pr\u00e9f\u00e9rences les plus importantes sont\u00a0:<\/p>\n
Pour le stemmer, c’est un stemmer multi-langues qui vaudra mieux activer. Pour le v\u00e9rifier, il faudrait faire une recherche avec \u00ab\u00a0cheval\u00a0\u00bb qui devrait retourner les textes contenant \u00ab\u00a0chevaux\u00a0\u00bb, par exemple. Cela d\u00e9pend du default langue de la database.<\/p>\n
Dans Oracle Text, l’op\u00e9rateur de stemming est le \u00ab\u00a0$\u00a0\u00bb<\/p>\n
La recherche ci-dessus s’exprimerait ainsi\u00a0:<\/p>\n
Select * from TEST where CONTAINS(espece, ‘$cheval’)>0<\/p>\n
En ce qui concerne la stop List, il faut consid\u00e9rer que les recherches s’appliquent \u00e0 des noms propres, et que par cons\u00e9quent, tout les mots sont significatif. Alors que l’article \u00ab\u00a0de\u00a0\u00bb est consid\u00e9r\u00e9 comme un mot vide, ici dans le cas d’un phone book, il devra pouvoir faire l’objet de recherche. Par cons\u00e9quent, la stoplist ne sera pas activ\u00e9e pour l’index.
\nVoici un extrait de la doc sur Oracle Text concernant le traitement des formes \u00ab\u00a0fl\u00e9chies\u00a0\u00bb.
\nles formes \u00ab\u00a0plurielles\u00a0\u00bb d’un mot sont bien trait\u00e9es dans le cas du Fran\u00e7ais (inflectional stemming).<\/p>\n
Stemming expands a search to include all words with the same linguistic root, by performing a morphological
\nanalysis of the word which allows for a search on both the root form of a word, and its inflected or derived
\nforms.<\/p>\n
1. Inflectional Stemming ? For all the supported languages, the stemmers return standard inflected forms of a word. In English, an
\ninflection is a change in the number such as the plural form of a noun – dog \u00e0 dogs, or a change in the tense such as the conjugated
\nforms of a verb – to run \u00e0 run, runs, running, ran.
\n2. Derivational Stemming ? For English, an French\u00a0the stemmer also returns standard derived forms of a word.
\nLa recherche de formes au pluriel fonctionne correctement pour le Fran\u00e7ais.
\nPour reprendre l’exemple \u00e9quin, une recherche sur CHEVAL, ramenera bien CHEVAL mais \u00e9galement CHEVAUX, pour peu que le param\u00e9trage des options d’indexation ait \u00e9t\u00e9 r\u00e9alis\u00e9 pr\u00e9alablement.<\/p>\n
create table ttext (lib varchar2(2000)); create index ttext_i on ttext(lib) indextype is ctxsys.context ONLINE insert into ttext values (‘cheval’); Test de bon fonctionnement:<\/p>\n R\u00e9sultat\u00a0: Some languages contain characters with diacritical marks such as tildes, umlauts, and accents. When your indexing operation converts words containing diacritical marks to their base letter form, queries need not contain diacritical marks to score matches. For example in Spanish with a base-letter index, a query of\u00a0energ\u00eda\u00a0matches\u00a0energ\u00eda\u00a0and\u00a0energia\u00a0in the index.<\/p>\n However, with base-letter indexing disabled, a query of\u00a0energ\u00eda\u00a0matches only\u00a0energ\u00eda.<\/p>\n You can enable and disable base-letter indexing for your language with the\u00a0base_letter\u00a0attribute of the\u00a0BASIC_LEXER\u00a0preference type.<\/p>\n Fuzzy matching enables you to match similarly spelled words in queries.<\/p>\n Fuzzy matching and stemming are automatically enabled in your index if Oracle Text supports this feature for your language.<\/p>\n Fuzzy matching is enabled with default parameters for its similarity score lower limit and for its maximum number of expanded terms. At index time you can change these default parameters.<\/p>\n To improve the performance of stem queries, create a stem index by enabling the\u00a0index_stems\u00a0attribute of the\u00a0BASIC_LEXER.<\/p>\n Wildcard queries enable you to enter left-truncated, right-truncated and doubly truncated queries, such as\u00a0%ing,\u00a0cos%, or\u00a0%benz%. With normal indexing, these queries can sometimes expand into large word lists, degrading your query performance.<\/p>\n Wildcard queries have better response time when token prefixes and substrings are recorded in the index.<\/p>\n By default, token prefixes and substrings are not recorded in the Oracle Text index. If your query application makes heavy use of wildcard queries, consider indexing token prefixes and substrings. To do so, use the wordlist preference type. The trade-off is a bigger index for improved wildcard searching.<\/p>\n The following example sets the wordlist preference for prefix and substring indexing. Having a prefix and sub-string component to your index improves performance for wildcard queries.<\/p>\n For prefix indexing, the example specifies that Oracle Text create token prefixes between three and four characters long:<\/p>\n begin ctx_ddl.create_preference(‘mywordlist’, ‘BASIC_WORDLIST’); ctx_ddl.set_attribute(‘mywordlist’,’PREFIX_INDEX’,’TRUE’); ctx_ddl.set_attribute(‘mywordlist’,’PREFIX_MIN_LENGTH’, ‘3’); ctx_ddl.set_attribute(‘mywordlist’,’PREFIX_MAX_LENGTH’, ‘4’); ctx_ddl.set_attribute(‘mywordlist’,’SUBSTRING_INDEX’, ‘YES’); end; <\/p>\n begin Ctx_Ddl.Create_Preference (‘BASE_LETTER_PREF’,’BASIC_LEXER’); Ctx_Ddl.Set_Attribute ( ‘BASE_LETTER_PREF’, ‘BASE_LETTER’, ‘YES’); Ctx_Ddl.Create_Preference(‘STEM_FUZZY_PREF’, ‘BASIC_WORDLIST’); ctx_ddl.set_attribute(‘STEM_FUZZY_PREF’,’FUZZY_MATCH’,’FRENCH’); ctx_ddl.set_attribute(‘STEM_FUZZY_PREF’,’FUZZY_SCORE’,’0′); ctx_ddl.set_attribute(‘STEM_FUZZY_PREF’,’FUZZY_NUMRESULTS’,’5000′); ctx_ddl.set_attribute(‘STEM_FUZZY_PREF’,’STEMMER’,’FRENCH’); end; \/ <\/p>\n ctx_ddl.create_stoplist(‘liste_stop’,’MULTI_STOPLIST’); ctx_ddl.create_preference(‘WL_MULTI’,’basic_wordlist’); ctx_ddl.set_attribute(‘WL_MULTI’, ‘STEMMER’, ‘AUTO’); ctx_ddl.set_attribute(‘WL_MULTI’, ‘FUZZY_MATCH’, ‘AUTO’); execute Ctx_Ddl.Create_Preference (‘FRENCH_BASIC_LEXER’,’BASIC_LEXER’) ctx_ddl.set_attribute(‘FRENCH_BASIC_LEXER’,’base_letter’,’yes’); Ctx_Ddl.Add_Sub_Lexer ( ‘GLOBAL_LEXER’, ‘f’,’FRENCH_BASIC_LEXER’ ); <\/p>\n Le code qui suit permet de forcer la construction de l’index pour les particularit\u00e9s de la langue Fran\u00e7aise.<\/p>\n BEGIN ctx_ddl.set_attribute(‘WKSYS.WK_WORDLIST’, BEGIN ctx_ddl.set_attribute(‘WKSYS.WK_BASIC_LEXER’, <\/p>\n <\/p>\n CREATE INDEX [schema.]index ON [schema.]table(txt_column) INDEXTYPE IS ctxsys.context [ONLINE] [FILTER BY filter_column[, filter_column]…] [ORDER BY oby_column[desc|asc][, oby_column[desc|asc]]…] [LOCAL [(PARTITION [partition] [PARAMETERS(‘paramstring’)] [, PARTITION [partition] [PARAMETERS(‘paramstring’)]])] [PARAMETERS(paramstring)] [PARALLEL n] [UNUSABLE]]; <\/p>\n exemple de pr\u00e9dicat en format natif<\/p>\n<\/td>\n Commentaires<\/p>\n<\/td>\n Type d’op\u00e9rateur<\/p>\n<\/td>\n<\/tr>\n Riz<\/p>\n<\/td>\n mot simple<\/p>\n<\/td>\n $sardine<\/p>\n<\/td>\n op\u00e9rateur de stemming <\/a>($) afin de rechercher les expansion<\/p>\n<\/td>\n<\/tr>\n $fr\u00e9mir<\/p>\n<\/td>\n op\u00e9rateur de stemming pour rechercher les formes conjugu\u00e9es (inflectional stemming<\/em>). Le derivational stemming<\/em> ne semble pas etre support\u00e9 pour le Fran\u00e7ais<\/p>\n<\/td>\n expansion<\/p>\n<\/td>\n<\/tr>\n radis NOT beurre<\/p>\n<\/td>\n op\u00e9rateur NOT<\/p>\n<\/td>\n bool\u00e9en<\/p>\n<\/td>\n<\/tr>\n radis & beurre<\/p>\n<\/td>\n op\u00e9rateur & ou bien AND<\/p>\n<\/td>\n bool\u00e9en<\/p>\n<\/td>\n<\/tr>\n radis,beurre<\/p>\n<\/td>\n le symbole \u00ab\u00a0,\u00a0\u00bb agit sur le score. l’ordre des recettes sera d\u00e9livr\u00e9e en fonction du score.<\/p>\n<\/td>\n score<\/p>\n<\/td>\n<\/tr>\n radis \u2013 beurre<\/p>\n<\/td>\n le symbole \u00ab\u00a0–\u00a0\u00bb devrait donner, en priorit\u00e9, les recettes avec radis mais sans beurre. (l’exemple ne semble ne pas fonctionner!)<\/p>\n<\/td>\n score<\/p>\n<\/td>\n<\/tr>\n %sardi%<\/p>\n<\/td>\n le caract\u00e8re jocker est \u00ab\u00a0%\u00a0\u00bb et non pas \u00ab\u00a0*\u00a0\u00bb<\/p>\n<\/td>\n expansion<\/p>\n<\/td>\n<\/tr>\n cuill\u00e8re;arachide<\/p>\n<\/td>\n operateur de proximit\u00e9 NEAR ou bien \u00ab\u00a0;\u00a0\u00bb. proximit\u00e9<\/p>\n<\/td>\n<\/tr>\n NEAR((cuill\u00e8re,arachide), 10, TRUE)<\/p>\n<\/td>\n op\u00e9rateur de proximit\u00e9 proximit\u00e9<\/p>\n<\/td>\n<\/tr>\n ?pome<\/p>\n<\/td>\n op\u00e9rateur fuzzy pour les mots mal orthographi\u00e9s ou avec des lettres manquantes<\/p>\n<\/td>\n expansion<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n <\/p>\n Someone accustomed to the spelling rules of one culture can have difficulty applying those same rules to a name originating from a different culture. Name matching provides a solution to match proper names that might differ in spelling due to orthographic variation. It also enables you to search for somewhat inaccurate data, such as might occur when a record’s first name and surname are not propertly segmented.<\/p>\n ndata_thesaurus<\/p>\n Specify a name of the thesaurus used for alternate name expansion. The indexing engine expands names in documents using synonym rings in the thesaurus. A user should make use of homographic disambiguating feature of the thesaurus to distinguish common nicknames.<\/p>\n An example is:<\/p>\n Albert<\/p>\n SYN Al<\/p>\n SYN Bert<\/p>\n Alfred<\/p>\n SYN Al<\/p>\n SYN Fred<\/p>\n A simple definition such as the above will put Albert, Alfred, Al, Bert, and Fred into the same synonym ring. This will cause an unexpected expansion such that the expansion of Bert includes Fred. To prevent this, you can use homographic disambiguation as in:<\/p>\n Albert<\/p>\n SYN Al (Albert)<\/p>\n SYN Bert (Albert)<\/p>\n Alfred<\/p>\n SYN Al (Alfred)<\/p>\n SYN Fred (Alfred)<\/p>\n This forms two synonym rings, Albert-Al-Bert and Alfred-Al-Fred. Thus, the expansion of Bert no longer includes Fred. A more detailed example is:<\/p>\n begin ctx_ddl.create_preference(‘NDAT_PREF’, ‘BASIC_WORDLIST’); ctx_ddl.set_attribute(‘NDATA_PREF’, ‘NDATA_ALTERNATE_SPELLING’, ‘FALSE’); ctx_ddl.set_attribute(‘NDATA_PREF’, ‘NDATA_BASE_LETTER’, ‘TRUE’); ctx_ddl.set_attribute(‘NDATA_PREF’, ‘NDATA_THESAURUS’, ‘NICKNAMES’); end; <\/p>\n A sample thesaurus for names can be found in the\u00a0$ORACLE_HOME\/ctx\/sample\/thesdirectory. This file is\u00a0dr0thsnames.txt.<\/p>\n <\/p>\n ndata_join_particles<\/p>\n Specify a list of colon-separated name particles that can be joined with a name that follows them. A name particle, such as da, is written separately from or joined with its following name like da Vinci or daVinci. The indexing engine generates index data for both separated and join versions of a name when it finds a name particle specified in this prefence. The same happens in the query processing for better recall.<\/p>\n Use the\u00a0NDATA\u00a0operator to find matches that are spelled in a similar way or where rearranging the terms of the specified phrase is useful. It is helpful for finding more accurate results when there are frequent misspellings (or inaccurate orderings) of name data in the document set. This operator can be used only on defined\u00a0NDATA\u00a0sections. The\u00a0NDATA\u00a0syntax enables you to rank the result set so that documents that contain words with high orthographic similarity are scored higher than documents with lower similarity.<\/p>\n Normalization<\/p>\n A lexer does not process\u00a0NDATA\u00a0query phrases. Users can, however, set base letter and alternate spelling attributes for a particular section group containing\u00a0NDATA\u00a0sections. Query case is normalized and non-character data (except for white space) is removed (for example, numerical or punctuation).<\/p>\n
\nexec ctx_ddl.create_preference(‘basic_wtest’, ‘BASIC_WORDLIST’)
\nexec ctx_ddl.set_attribute(‘basic_wtest’,’STEMMER’,’FRENCH’)
\nexec ctx_ddl.set_attribute(‘basic_wtest’,’FUZZY_MATCH’,’FRENCH’)<\/p>\n
\nparameters (‘wordlist basic_wtest sync on-commit’ );<\/p>\n
\ninsert into ttext values (‘chevaline’);
\ninsert into ttext values (‘chevaux’);
\n<\/span><\/p>\n
\nselect * from ttext
\nwhere contains (lib, ‘$cheval’) >0;<\/span><\/p>\n
\nLIB
\n—————————-
\ncheval
\nchevaux
\n<\/span><\/p>\nCaract\u00e8res diacritiques (accents, etc..)<\/h3>\n
Recherches approch\u00e9es<\/h3>\n
Caract\u00e8res jokers<\/h3>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n‘STEMMER’,
\n‘FRENCH’);
\nEND;<\/p>\n
\n‘INDEX_THEMES’, ‘YES’);
\nctx_ddl.set_attribute(‘WKSYS.WK_BASIC_LEXER’,
\n‘CONTINUATION’,’-‘);
\nctx_ddl.set_attribute(‘WKSYS.WK_BASIC_LEXER’,
\n‘THEME_LANGUAGE’,
\n‘FRENCH’);
\nEND;
\nALTER INDEX WK_TEST.WK$DOC_PATH_IDX
\nREBUILD\u00a0 PARAMETERS (‘REPLACE LEXER WKSYS.WK_BASIC_LEXER
\nWORDLIST WKSYS.WK_WORDLIST’);
\n<\/span><\/p>\nSyntaxe pour la cr\u00e9ation d’un index textuel\u00a0:<\/h4>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\nExemples de syntaxes de recherche<\/h3>\n
\n
\n Interrogation<\/td>\n \n \n \n \n recettes avec du riz<\/td>\n \n \n <\/td>\n<\/tr>\n \n recettes avec des sardines<\/td>\n \n \n
\nformes fl\u00e9chies<\/a> (formes plurielles dans ce cas)<\/p>\n<\/td>\n\n \n Recettes avec le verbe ventiler<\/td>\n \n \n \n \n Recettes avec des radis mais SANS beurre<\/td>\n \n \n \n \n Recettes avec des radis ET du beurre<\/td>\n \n \n \n \n Recettes avec, de pr\u00e9f\u00e9rence, des radis et du beurre ensemble<\/td>\n \n \n \n \n Recettes avec des radis, mais de pr\u00e9f\u00e9rence sans beurre<\/td>\n \n \n \n \n Recettes contenant le fragment\u00a0 sardi<\/td>\n \n \n \n \n Recette avec les termes cuill\u00e8re et\u00a0 arachide assez proches l’un de l’autre<\/td>\n \n \n
\n100 mots d’\u00e9cart maximum par d\u00e9faut<\/p>\n<\/td>\n\n \n Recette avec les termes cuill\u00e8re et\u00a0 arachide proches l’un de l’autre<\/td>\n \n \n
\n\u00e9cart de moins de 10 mots, et dans l’ordre pr\u00e9cis\u00e9<\/p>\n<\/td>\n\n \n Recettes avec pomme ou pome<\/td>\n \n \n \n Name matching<\/h3>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\n
\n<\/span><\/p>\nNDATA<\/h3>\n