【转载】oracle 全文检索实践_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2820 | 回复: 0   主题: 【转载】oracle 全文检索实践        下一篇 
红与黑
注册用户
等级:上尉
经验:644
发帖:51
精华:1
注册:2013-2-25
状态:离线
发送短消息息给红与黑 加好友    发送短消息息给红与黑 发消息
发表于: IP:您无权察看 2013-2-27 10:48:32 | [全部帖] [楼主帖] 楼主

前段时间,项目组长让我看一下有没有实时检索数据的方案,并说明不是用数据库模糊查询关键字like,而是像baidu那样的搜索效果,做到最大匹配。并提示我看一下lucene。

我就熟悉了下lucene,然后写了个demo,他们又说为了一个简单的查询检索,确引入了一个lucene,有点不划算。所以让我找其他的解决办法。

我找呀找,居然找到了select * from T where contains(F,'test',1)>0,这种oracle全文检索的方式,以前没见过的,貌似能解决我的问题。所以我就学习了一下oracle的全文检索。

在这里我记录了一下我的学习笔记,如果各位想要了解更全面的全文检索要点,请看这篇博客,讲的很详细,我认为非常非常的好。

http://yangtingkun.itpub.net/post/468/246823
1.oracle text


首先这里说oracle 全文检索,是针对我机器上的oracle 10g版本的。至少 10g 或以上的版本 适合这样来使用。

oracle 的全文检索,操作步骤为:将表中需要检索的字段,创建为全文检索的索引,然后通过select * from T where contains(F,'test',1)>0的语句进行全文检索,达到预期效果。

然后,oracle全文检索还是很强大的,能够检索文本啊、还有其他多种格式的文档。我做的测试只是针对数据库中的某一个字段的检索。比如针对地址表中的地址进行检索。

2.准备操作

首先,先建一个表用于测试,在名为testuser用户下建表。

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. createtable YU_TEST( 
  2. id number, 
  3. name varchar2(50) 
  4. ); 


create table YU_TEST(
id number,
name varchar2(50)
);


插入测试数据

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. insertinto YU_TEST values(1,'重庆市沙坪坝区');
  2. insertinto YU_TEST values(2,'成都市青羊区');
  3. insertinto YU_TEST values(3,'北京市西城区');
  4. insertinto YU_TEST values(4,'重庆市两江新区');
  5. insertinto YU_TEST values(5,'上海市浦东新区金桥镇');
  6. insertinto YU_TEST values(6,'上海东方明珠');
  7. insertinto YU_TEST values(7,'江苏省无锡市国家软件园');
  8. insertinto YU_TEST values(8,'成都市天府软件园');

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,'成都市天府软件园');



oracle全文检索需要ctxsys用户的支持,其实主要是需要使用ctxsys用户下的

ctx_ddl这个包,这个包中绝大部分过程的创建都与全文检索有关。

首先需要对ctxsys用户解锁,以获得ctx_ddl包的操作权。

进入system用户,输入如下命令,解锁ctxsys用户

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. alteruser ctxsys account unlock; 


alter user ctxsys account unlock;


然后将ctx_ddl包的操作权限赋给testuser用户。

也是在system用户下,输入如下命令,赋予目标用户ctx_ddl包操作权限

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. grantexecuteon ctx_ddl to testuser; 


grant execute on ctx_ddl to testuser;


至此,准备工作已经完成了

3.创建分析器

oracle text的分析器,类似于lucene中的分词器,将需要检索的记录,按照一定的方式进行词组拆分,然后存放在索引表中。检索的时候根据索引表中存放的拆分词组,对传入的关键字进行匹配,并返回匹配结果。

oracle text中的分析器有3种:

  • basic_lexer:只能根据空格和标点来进行拆分。比如“中国重庆”,只能拆分为“中国重庆”一个词组
  • chinese_vgram_lexer:专门的汉语分析器,按字单元进行拆分,比如“中国重庆”,可以拆分为“中”、“中国“、”国重”、“重庆”、“庆”五个词组。这种方式的好处是能够将所有有可能的词组全部保存进索引表,使得数据不会遗漏。
  • chinese_lexer:一种新的汉语分析器,能够认识大部分常用的汉语词汇,并按常用词汇进行拆分存储。比如“中国重庆”,只会被拆分为“中国”、“重庆”两个词组。

这里我使用chinese_lexer这个分词器,用testuser用户登录,执行下面的命令,创建分析器。

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. exec ctx_ddl.create_preference ('my_lexer', 'chinese_lexer'); 


exec ctx_ddl.create_preference ('my_lexer', 'chinese_lexer');



这句话的意思是,创建一个“chinese_lexer”分析器,名称为my_lexer。

4.创建过滤词组

在我们建索引的时候,通常需要对一些常用的词组进行过滤,比如对公司名称进行检索时,肯定不希望输入“有限公司”、“公司”等关键词时,也会有搜索结果。

用testuser用户登录,执行下面的命令,创建过滤词组

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. exec ctx_ddl.create_stoplist('my_stoplist'); 


exec ctx_ddl.create_stoplist('my_stoplist');


创建过滤词组成功以后,需要自定义需要过滤的词组

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. ctx_ddl.add_stopword('my_stoplist','有限公司');
  2. ctx_ddl.add_stopword('my_stoplist','股份有限公司');

ctx_ddl.add_stopword('my_stoplist','有限公司');
ctx_ddl.add_stopword('my_stoplist','股份有限公司');

意思就是,创建了一个名为“my_stoplist”的过滤词组,“有限公司”、“股份有限公司”这两个词组不会被创建为索引

5.创建索引

其实前面的工作,都是为创建索引做准备的。

我要对YU_TEST表中的name字段进行检索,首先必须对name字段创建索引。

这里需要注意的是,name字段不能为nvarchar2类型,并且这个表的主键也不能为nvarchar2型,否则无法创建索引。

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. createindex YU_TEST_INDEX on YU_TEST(name) indextype is CTXSYS.CONTEXT parameters('lexer my_lexer stoplist my_stoplist'); 


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

这句话的意思就是,在YU_TEST_INDEX表中的name字段上创建索引,索引类系那个为context类型,该索引用到的分析器为前面定义的my_lexer,该索引用到的过滤词组为前面定义得my_stoplist。

索引创建成功后,你回发现,在当前用户的表中,多了四个表

北京联动北方科技有限公司

其中YU_TEST表中name字段被拆分后的词组保存在DR$YU_TEST_INDEX$I表中

北京联动北方科技有限公司

这样可以看见索引的详细信息。

6.使用索引

Sql代码
北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司

  1. select * from YU_TEST wherecontains(name,'重庆')>0;

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

可以用contains来使用oracle的全文检索。

这样查出来的数据是没有经过排序的,有时候我们的需求是按照关键字的匹配程度排序,使用下面的语句:



Sql代码 北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司


  1. select score(1),y.* from YU_TEST y wherecontains(name,'重庆',1)>0 orderby score(1) desc;

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


这里的score是oracle全文检索对关键字的匹配程度所计算的分数,contains里的最后一个参数“1”就是对这个分数的一个标识



7.索引优化

前面已经能够进行检索了,现在就是对索引进行优化

当我们需要修改YU_TEST表中的数据,比如添加、删除、更新等操作时,YU_TEST_INDEX索引是不会同步更新数据的,需要我们在程序中手动的更新,可以写一个oracle的触发器,当添加、删除、修改操作时,进行索引更新。也可以定时进行更新。

索引同步:



Sql代码 北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司


  1. exec ctx_ddl.sync_index('yu_test_index')

exec ctx_ddl.sync_index('yu_test_index')


索引优化:



Sql代码 北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司


  1. exec ctx_ddl.optimize_index('yu_test_index','full')

exec ctx_ddl.optimize_index('yu_test_index','full')


8.用户输入关键词切词



在执行检索的时候会发现这样的问题



北京联动北方科技有限公司

表中存在两条“重庆”相关的记录和一条“天府”相关的记录,但是我传入“重庆天府”关键字时,确检索不出记录,这显然不是我们想要的结果,因为我们需要的是最大程度的匹配,传入“重庆天府”时应该同时检索出“重庆”相关和“天府”相关的信息。

要实现这种效果,需要用到oracle 10g的新特性,可以将传入的关键词先进行切词,然后在进行检索。

首先需要先创建一个POLICY过程



Sql代码 北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司


  1. exec CTX_DDL.CREATE_POLICY('MY_POLICY', LEXER => 'my_lexer');

exec CTX_DDL.CREATE_POLICY('MY_POLICY', LEXER => 'my_lexer');


这里创建了一个名称为my_policy的policy过程,分析器用到了前面创建的my_lexer分析器



写一个oracle函数,来处理关键词切词:

Sql代码 北京联动北方科技有限公司北京联动北方科技有限公司北京联动北方科技有限公司


  1. createorreplacefunction p_split_chinese(p_input in varchar2)
  2. return varchar2 as
  3. v_tab CTX_DOC.TOKEN_TAB;
  4. v_return VARCHAR2(323767);
  5. begin
  6. CTX_DOC.POLICY_TOKENS('my_policy',p_input,v_tab);
  7. for i in 1..v_tab.count loop
  8. v_return := v_return || ',' || v_tab[i].token;
  9. end loop;
  10. return LTRIM(v_return,',');
  11. end;
  12. /

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;
/


在plsql 中执行这个函数



北京联动北方科技有限公司

这样就可以将关键词切词,在程序中直接将这个切词结果,进行检索。



北京联动北方科技有限公司

可以看到这里显示的只有“天府”相关的信息,那么“重庆”相关的呢?

先看一下DR$YU_TEST_INDEX$I表中的结构。



北京联动北方科技有限公司

北京联动北方科技有限公司

发现token_text字段中,只有重庆市、北京市这些词,而没有重庆、北京之类的

所以会发现,oracle全文检索的chinese lexer分析器,对"重庆市",只会认为这只有一个词组,而不会把“重庆市”分为“重庆”和“重庆市”。

所以,如果需要比较精确的检索到结果,还是 chinese_vgram_lexer分析器最好用,能够匹配到所有的词组。






赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论