oracle 全文检索实践

前段时间，项目组长让我看一下有没有实时检索数据的方案，并说明不是用数据库模糊查询关键字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用户下建表。

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

oracle全文检索需要ctxsys用户的支持，其实主要是需要使用ctxsys用户下的ctx_ddl这个包，这个包中绝大部分过程的创建都与全文检索有关。

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

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

alter user ctxsys account unlock;

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

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

grant execute on ctx_ddl to testuser;

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

3.创建分析器

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

oracle text中的分析器有3种：

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

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

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

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

4.创建过滤词组

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

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

exec ctx_ddl.create_stoplist('my_stoplist');

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

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

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

5.创建索引

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

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

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

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.使用索引

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

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

exec ctx_ddl.sync_index('yu_test_index')

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

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

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