oracle text

 ads：

关注以下公众号查看更多文章

参考oracle官方关于oracle全文索引的说明：

Oracle Text Indexing Elements

//system=>sra

GRANT CTXAPP TO SRA
GRANT EXECUTE ON CTX_DDL TO SRA;
//sra账户
exec ctx_ddl.create_preference('MYLEXER','world_lexer');
create index doc_qualdetail on SRA.TRNQUAL(QUALDETAILS)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
create index doc_memo on SRA.TRNCAREER(MEMO)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
create index doc_schldeptname on SRA.TRNSCHLCR(SCHLDEPTNAME)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
create index doc_schlname on SRA.TRNSCHLCR(SCHLNAME)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
create index doc_ocupcareer on SRA.TRNCAREER_KEIREKI(KEIREKI)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');


create index doc_compapplied on SRA.TRNCAREER(COMPAPPLIED)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
create index doc_impresscomment on SRA.TRNCAREER(IMPRESSCOMMENT)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');


create index doc_wkcontents on SRA.TRNBUSICR(WKCONTENTS)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
create index doc_compname on SRA.TRNBUSICR(COMPNAME)
  indextype is  CTXSYS.CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');
//同步索引
exec ctx_ddl.sync_index('doc_compname');--同步索引，将新的数据同步到索引
exec ctx_ddl.optimize_index('doc_compname','FULL');--优化索引，清楚已删除的数据
//查询
select COUNT(1) from SRA.TRNBUSICR where contains(COMPNAME,'北京') > 0;


//创建存储过程
CREATE OR REPLACE PROCEDURE SRA.RGF_SYNC_INDEX 
AS 
BEGIN 
ctx_ddl.sync_index('doc_compapplied'); 
ctx_ddl.sync_index('doc_impresscomment'); 
ctx_ddl.sync_index('doc_compname'); 
ctx_ddl.sync_index('doc_wkcontents'); 
ctx_ddl.sync_index('doc_ocupcareer'); 
ctx_ddl.sync_index('doc_schlname'); 
ctx_ddl.sync_index('doc_schldeptname'); 
ctx_ddl.sync_index('doc_qualdetail'); 
ctx_ddl.sync_index('doc_memo'); 
END;
//创建job
declare  test_job_really number;  
begin  
dbms_job.submit(test_job_really,'rgf_sync_index;',sysdate,'sysdate+1/24/4');    
end;  
//查询job
SELECT job, schema_user, INTERVAL, what FROM user_jobs; 
//执行job
exec dbms_job.run(6);


BEGIN
dbms_job.remove(8);
END;


select table_name from user_tab_columns where column_name = 'QUALDETAIL'