全文检索的应用例子

如下是一个涉及到全文检索的应用例子:
1.建表,列servkeys将用到全文检索.
create table SERVICEKEY
(
SERVCODE VARCHAR2(12) not null,,
SERVKEYS VARCHAR2(2048) not null
);
2. 创建中文语义的定义
begin
ctx_ddl.create_preference('chinese_lexer','CHINESE_VGRAM_LEXER');
end;
/
3. 创建中文语义的索引
create index idx_ctx_servkey on servicekey(servkeys) indextype is ctxsys.context
parameters('lexer chinese_lexer');

4.全文检索中用于定时更新DR$idx_ctx_servkey$I、DR$idx_ctx_servkey$K、DR$idx_ctx_servkey$R和DR$idx_ctx_servkey$N表中的记录，重建索引
create or replace procedure proc_syn_ctx_index
is
begin
execute immediate
'alter index idx_ctx_servkeys rebuild online' ||
' parameters ( ''sync'' )' ;
execute immediate
'alter index idx_ctx_servkeys rebuild online' ||
' parameters ( ''optimize full maxtime unlimited'' )' ;
end proc_syn_ctx_index;
/
5.设置job,定时更新和同步优化全文检索
Set ServerOutput on
declare
v_job number;
begin
Dbms_Job.Submit
(
job => v_job,
what => 'proc_syn_ctx_index;',
next_date => sysdate, /* default */
interval => 'sysdate + 1/24'
);
Dbms_Job.Run ( v_job );
Dbms_Output.Put_Line ( 'Submitted as job # ' || to_char ( v_job ) );
end;
/

6.应用引用
SELECT * FROM SERVICEKEY
WHERE contains(SERVKEYS ,'百宝箱')>0;

备注:需授权ctxapp角色给用户.