Oracle text setup for People Search

This post is a compilation of informations about  the use of Oracle Text. The particular context of Name Matching is illustrated by some features extracted from the documentation. 28 juin 2011 – Oracle V 0.1

Index Setup

Type d’index

Il existe deux types d’index qui sont adaptés à l’optimisation des recherches sur des textes:

• CONTEXT
• CTXCAT

En principe, CTXCAT est mieux adapté pour l’indexation des fragments de texte courts (des noms, par exemple) . cela n’empêche pas l’utilisation du premier type : CONTEXT.

The CTXCAT indextype 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 CONTEXT index.

 

Pour le lexer et les variations grammaticales :

Lors de la création d’un index de type CONTEXT, on précise en général un jeu de préférences qui agit sur le comportement des recherches ultérieures, vis-à-vis notamment de règles grammaticales.

On peut dire que les préférences les plus importantes sont :

• La liste des mots vides (stoplist)
• Le lexer (caractère accentués, manière de découper les tokens, etc)
• Le stemmer : Recherche des formes fléchies (formes plurielles, conjuguées)
  préférence BASIC_WORDLIST

Pour le stemmer, c’est un stemmer multi-langues qui vaudra mieux activer. Pour le vérifier, il faudrait faire une recherche avec « cheval » qui devrait retourner les textes contenant « chevaux », par exemple. Cela dépend du default langue de la database.

Dans Oracle Text, l’opérateur de stemming est le « $ »

La recherche ci-dessus s’exprimerait ainsi :

Select * from TEST where CONTAINS(espece, ‘$cheval’)>0

En ce qui concerne la stop List, il faut considérer que les recherches s’appliquent à des noms propres, et que par conséquent, tout les mots sont significatif. Alors que l’article « de » est considéré comme un mot vide, ici dans le cas d’un phone book, il devra pouvoir faire l’objet de recherche. Par conséquent, la stoplist ne sera pas activée pour l’index.
Voici un extrait de la doc sur Oracle Text concernant le traitement des formes « fléchies ».
les formes « plurielles » d’un mot sont bien traitées dans le cas du Français (inflectional stemming).

Stemming

Stemming expands a search to include all words with the same linguistic root, by performing a morphological
analysis of the word which allows for a search on both the root form of a word, and its inflected or derived
forms.

1. Inflectional Stemming ? For all the supported languages, the stemmers return standard inflected forms of a word. In English, an
inflection is a change in the number such as the plural form of a noun – dog à dogs, or a change in the tense such as the conjugated
forms of a verb – to run à run, runs, running, ran.
2. Derivational Stemming ? For English, an French the stemmer also returns standard derived forms of a word.
La recherche de formes au pluriel fonctionne correctement pour le Français.
Pour reprendre l’exemple équin, une recherche sur CHEVAL, ramenera bien CHEVAL mais également CHEVAUX, pour peu que le paramétrage des options d’indexation ait été réalisé préalablement.

Exemple de mise en œuvre d’Oracle text

create table ttext (lib varchar2(2000));
exec ctx_ddl.create_preference(‘basic_wtest’, ‘BASIC_WORDLIST’)
exec ctx_ddl.set_attribute(‘basic_wtest’,’STEMMER’,’FRENCH’)
exec ctx_ddl.set_attribute(‘basic_wtest’,’FUZZY_MATCH’,’FRENCH’)

create index ttext_i on ttext(lib) indextype is ctxsys.context ONLINE
parameters (‘wordlist basic_wtest sync on-commit’ );

insert into ttext values (‘cheval’);
insert into ttext values (‘chevaline’);
insert into ttext values (‘chevaux’);

Test de bon fonctionnement:

select * from ttext
where contains (lib, ‘$cheval’) >0;

Résultat :
LIB
—————————-
cheval
chevaux

Caractères diacritiques (accents, etc..)

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 energía matches energía and energia in the index.

However, with base-letter indexing disabled, a query of energía matches only energía.

You can enable and disable base-letter indexing for your language with the base_letter attribute of the BASIC_LEXER preference type.

Recherches approchées

Fuzzy matching enables you to match similarly spelled words in queries.

Fuzzy matching and stemming are automatically enabled in your index if Oracle Text supports this feature for your language.

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.

To improve the performance of stem queries, create a stem index by enabling the index_stems attribute of the BASIC_LEXER.

Caractères jokers

Wildcard queries enable you to enter left-truncated, right-truncated and doubly truncated queries, such as %ing, cos%, or %benz%. With normal indexing, these queries can sometimes expand into large word lists, degrading your query performance.

Wildcard queries have better response time when token prefixes and substrings are recorded in the index.

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.

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.

For prefix indexing, the example specifies that Oracle Text create token prefixes between three and four characters long:

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;

 

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;

/

 

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’ );

 

Le code qui suit permet de forcer la construction de l’index pour les particularités de la langue Française.

BEGIN ctx_ddl.set_attribute(‘WKSYS.WK_WORDLIST’,
‘STEMMER’,
‘FRENCH’);
END;

BEGIN ctx_ddl.set_attribute(‘WKSYS.WK_BASIC_LEXER’,
‘INDEX_THEMES’, ‘YES’);
ctx_ddl.set_attribute(‘WKSYS.WK_BASIC_LEXER’,
‘CONTINUATION’,’-‘);
ctx_ddl.set_attribute(‘WKSYS.WK_BASIC_LEXER’,
‘THEME_LANGUAGE’,
‘FRENCH’);
END;
ALTER INDEX WK_TEST.WK$DOC_PATH_IDX
REBUILD  PARAMETERS (‘REPLACE LEXER WKSYS.WK_BASIC_LEXER
WORDLIST WKSYS.WK_WORDLIST’);

 

Syntaxe pour la création d’un index textuel :

 

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]];

 

Exemples de syntaxes de recherche

 

Name matching

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.

ndata_thesaurus

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.

An example is:

Albert

SYN Al

SYN Bert

Alfred

SYN Al

SYN Fred

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:

Albert

SYN Al (Albert)

SYN Bert (Albert)

Alfred

SYN Al (Alfred)

SYN Fred (Alfred)

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:

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;

 

A sample thesaurus for names can be found in the $ORACLE_HOME/ctx/sample/thesdirectory. This file is dr0thsnames.txt.

 

ndata_join_particles

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.

NDATA

Use the NDATA operator 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 NDATA sections. The NDATA syntax 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.

Normalization

A lexer does not process NDATA query phrases. Users can, however, set base letter and alternate spelling attributes for a particular section group containing NDATA sections. Query case is normalized and non-character data (except for white space) is removed (for example, numerical or punctuation).

Syntax

ndata(sectionname, phrase [,order][,proximity])