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;