【引用】Oracle全文检索方面的研究(全6)

3.6 Wordlist 属性

Oracle 全文检索的wordlist 属性用来设置模糊查询和同词根查询，wordlist 属性还支持

子查询和前缀查询，oracle 的wordlist 属性只有basic_wordlist 一种（原文：Use the wordlist

preference to enable the query options such as stemming, fuzzy matching for your language. You

can also use the wordlist preference to enable substring and prefix indexing, which improves

performance for wildcard queries with CONTAINS and CATSEARCH.）

3.6.1 例子：

Create table my_word (id number, docs varchar2(1000));

Insert into my_word values (1, 'Specify the stemmer used for word stemming in Text queries');

Insert into my_word values (2, 'Specify which fuzzy matching routines are used for the

column');

Insert into my_word values (3, 'Fuzzy matching is currently supported for English');

Insert into my_word values (4, 'Specify a default lower limit of fuzzy score. Specify a

number between 0 and 80');

Insert into my_word values (5, 'Specify TRUE for Oracle Text to create a substring index

matched.');

commit;

/

--建立wordlist

Begin

ctx_ddl.drop_preference('mywordlist');

ctx_ddl.create_preference('mywordlist', 'basic_wordlist');

ctx_ddl.set_attribute('mywordlist','fuzzy_match','english'); --模糊匹配，英语

ctx_ddl.set_attribute('mywordlist','fuzzy_score','0'); --匹配得分

ctx_ddl.set_attribute('mywordlist','fuzzy_numresults','5000');

ctx_ddl.set_attribute('mywordlist','substring_index','true'); --左查询，适用%to,%to%

ctx_ddl.set_attribute('mywordlist','stemmer','english'); --词根

ctx_ddl.set_attribute('mywordlist', 'prefix_index', 'true'); --右查询，适用t0%

End;

Create index indx_m_word on my_word(docs) indextype is ctxsys.context

parameters('wordlist mywordlist');

--例子

Select docs from my_word where contains(docs,'$match')>0 ; --词根查询

Select docs from my_word where contains(docs,'MA%')>0; --匹配查询

 

3.6.2 document 上的例子

create table quick( quick_id number primary key, text varchar(80) );

--- insert a row with 10 expansions for 'tire%'

insert into quick ( quick_id, text )

values ( 1, 'tire tirea tireb tirec tired tiree tiref tireg tireh tirei tirej');

commit;

/

begin

Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST');

ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 100) ;

end;

/

create index wildcard_idx on quick(text) indextype is ctxsys.context

parameters ('Wordlist wildcard_pref') ;

select quick_id from quick where contains ( text, 'tire%' ) > 0;

drop index wildcard_idx ;

begin

Ctx_Ddl.Drop_Preference('wildcard_pref');

Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST');

ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 5) ;--限制最大的匹配数，如

果超过这个数量，查询出现报错

end;

/

create index wildcard_idx on quick(text) indextype is ctxsys.context

parameters ('Wordlist wildcard_pref') ;

select quick_id from quick where contains ( text, 'tire%' ) > 0;

 

3.6.3.参考脚本

--建立wordlist

begin

ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');

ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE'); --定义wordlist 的参数

end;

--删除wordlist

begin

ctx_ddl.drop_preference('mywordlist');