oracle全文索引实践

--oracle 全文索引实践

/*
首先需要对ctxsys用户解锁，以获得ctx_ddl包的操作权。进入system用户，输入如下命令，解锁ctxsys用户
grant ctxapp to wang;
在ctxsys用户下，输入如下命令，赋予目标用户ctx_ddl包操作权限
grant execute on ctx_ddl to wang;  
*/
--建表
create table YU_TEST(  
    id number,  
    name varchar2(50)  
);  

--插入数据
insert into YU_TEST values(1,'重庆市沙坪坝区');  
insert into YU_TEST values(2,'成都市青羊区');  
insert into YU_TEST values(3,'北京市西城区');  
insert into YU_TEST values(4,'重庆市两江新区');  
insert into YU_TEST values(5,'上海市浦东新区金桥镇');  
insert into YU_TEST values(6,'上海东方明珠');  
insert into YU_TEST values(7,'江苏省无锡市国家软件园');  
insert into YU_TEST values(8,'成都市天府软件园');  
insert into YU_TEST values(10,'北京用友软件园');  
insert into YU_TEST values(11,'郑州航空工业管理学院');
insert into YU_TEST values(15,' 山东大学');

insert into YU_TEST values(16,' 华北水利水电学院');

insert into YU_TEST values(16,'中华人民共和国');
--删除过滤词组
begin
ctx_ddl.drop_stoplist('my_stoplist');
end;

--删除分析器
begin
ctx_ddl.drop_preference('my_lexer');
end;

begin
--创建一个“chinese_vgram_lexer”分析器，名称为my_lexer。 
ctx_ddl.create_preference('my_lexer','chinese_vgram_lexer');

--创建过滤词组
ctx_ddl.create_stoplist('my_stoplist');  

--创建过滤词组成功以后，需要自定义需要过滤的词组
ctx_ddl.add_stopword('my_stoplist','有限公司');  
ctx_ddl.add_stopword('my_stoplist','股份有限公司');  
end;

--创建索引
create index YU_TEST_INDEX on YU_TEST(name) indextype is CTXSYS.CONTEXT parameters('lexer my_lexer stoplist my_stoplist');  

--测试例子
select * from YU_TEST where contains(name,'重庆')>0;

select * from yu_test where contains(name,'区')>0;

select * from YU_TEST where contains(name,'重庆市')>0;  --  可以用contains来使用oracle的全文检索

select score(1),y.* from YU_TEST y where contains(name,'重庆市',1)>0 order by score(1) desc; 

select * from YU_TEST where contains(name,'重庆,天府')>0;

select * from YU_TEST where contains(name,'用友')>0;
select * from YU_TEST where contains(name,'华北')>0;
select * from YU_TEST where contains(name,'中华')>0;

--索引同步：
begin
ctx_ddl.sync_index('yu_test_index');
ctx_ddl.optimize_index('yu_test_index','full');
end;  

--索引优化：
begin
ctx_ddl.drop_preference('my_lexer');
end;

--创建了一个名称为my_policy的policy过程，分析器用到了前面创建的my_lexer分析器,处理上边出现的问题
begin
CTX_DDL.CREATE_POLICY('MY_POLICY', LEXER => 'my_lexer');  
end;

create or replace function p_split_chinese(p_input in varchar2)  
    return varchar2 as   
       v_tab CTX_DOC.TOKEN_TAB;  
       v_return VARCHAR2(323767);  
    begin  
       CTX_DOC.POLICY_TOKENS('my_policy',p_input,v_tab);  
       for i in 1..v_tab.count loop  
          v_return := v_return || ',' || v_tab[i].token;  
       end loop;  
       return LTRIM(v_return,',');  
     end; 
     


     
create trigger update_index
after insert  on yu_test for each row
begin
ctx_ddl.sync_index('yu_test_index');
end;

drop trigger update_index;
/*设置同步

打开pl sql developer
右击jobs，选择新建
What值：
BEGIN

      ctx_ddl.sync_index('myindex');

      commit;

END;
间隔中写：
SYSDATE + (1/24/24)
这表示间隔15分钟同步一次
*/