ORACLE 10G 使用全文索引步骤

 

 

1.给指定用户(tecamocmsuser)授权（ctxapp和execute on ctx_ddl）

grant ctxapp to tecamocmsuser;

 

grant execute on ctx_ddl to tecamocmsuser;

 

2创建分词

 

ctx_ddl.create_preference ('my_LEXER', 'chinese_vgram_lexer');

 

3创建索引

CREATE INDEX INX_Archive ON CMS_ODDITIONAL_ARTICLE (body) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER  my_LEXER ');

4.数据同步

create or replace procedure PROC_SYNC_FULLTEXT

is

begin

ctx_ddl.sync_index('INX_Archive');

ctx_ddl.optimize_index('INX_Archive','FULL');

end;

5.定时同步

BEGIN

DBMS_JOB.ISUBMIT(job=>1010,what => 'begin PROC_SYNC_FULLTEXT ();end;',

next_date => SYSDATE, interval => 'SYSDATE + (1/24/12)');

commit;

END;

6.使用查询

select id from cms_odditional_article  where contains (body, '广州')>1;