oracle 全文检索实践

一、基础入门

1.oracle text

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

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

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

2.准备操作

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

Sql代码  

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

插入测试数据

Sql代码  

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

 

 

 

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

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

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

Sql代码  

1. alter user ctxsys account unlock;  

 

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

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

Sql代码  

1. 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');  

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

4.创建过滤词组

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

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

Sql代码  

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

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

Sql代码  

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

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

5.创建索引

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

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

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

Sql代码  

1. 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 where contains(name,'重庆')>0;  

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

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

Sql代码  

1. 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')  

索引优化：

Sql代码  

1. 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');  

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

写一个oracle函数，来处理关键词切词：

Sql代码  

1. create or replace function 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. /  

在plsql 中执行这个函数

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

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

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

 

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

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

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

二、oracle 中对于 CONTAINS 函数的用法

1. 查询住址在北京的学生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'beijing' )
remark: beijing是一个单词，要用单引号括起来。

2. 查询住址在河北省的学生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, '"HEIBEI province"' )
remark: HEBEI province是一个词组，在单引号里还要用双引号括起来。

 

3. 查询住址在河北省或北京的学生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, '"HEIBEI province" OR beijing' )
remark: 可以指定逻辑操作符(包括 AND ，AND NOT，OR )。

 

4. 查询有 '南京路' 字样的地址
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'nanjing NEAR road' )
remark: 上面的查询将返回包含 'nanjing road'，'nanjing east road'，'nanjing west road' 等字样的地址。
          A NEAR B，就表示条件： A 靠近 B。

 

5. 查询以 '湖' 开头的地址
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, '"hu*"' )
remark: 上面的查询将返回包含 'hubei'，'hunan' 等字样的地址。
          记住是 *，不是 %。

 

6. 类似加权的查询
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'ISABOUT (city weight (.8), county wright (.4))' )
remark: ISABOUT 是这种查询的关键字，weight 指定了一个介于 0~1之间的数，类似系数(我的理解)。表示不同条件有不同的侧重。

 

7. 单词的多态查询
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address, 'FORMSOF (INFLECTIONAL,street)' )
remark: 查询将返回包含 'street'，'streets'等字样的地址。
         对于动词将返回它的不同的时态，如：dry，将返回 dry，dried，drying 等等。

 

8. 词查询示例

　　词查询是对输入到 CONTAINS 运算符中单引号间的精确单词或短语的查询。在以下示例中，我们将查找文本列中包含 oracle 一词的所有文档。每行的分值由使用标签 1 的 SCORE 运算符选定：

　　SELECT SCORE（1） title from news WHERE CONTAINS（text，'oracle'，1）> 0；

　　在查询表达式中，可以使用 AND 和 OR 等文本运算符来获取不同结果。还可以将结构性谓词添加到 WHERE 子句中。可以使用 count（*）、CTX_QUERY.COUNT_HITS 或 CTX_QUERY.EXPLAIN 来计算查询的命中 （匹配） 数目。

 

9 ABOUT查询示例

　　在所有语言中，ABOUT查询增加了某查询所返回的相关文档的数目。在英语中，ABOUT 查询可以使用索引的主题词组件，该组件在默认情况下创建。这样，运算符将根据查询的概念返回文档，而不是仅依据所指定的精确单词或短语。例如，以下查询将查找文本列中关于主题 politics 的所有文档，而不是仅包含 politics 一词的文档：

 

　　SELECT SCORE（1） title from news WHERE CONTAINS（text， 'about（politics）'， 1） > 0

三、单表多字段检索

表test1，

CREATE TABLE TEST1
(
CHINAME VARCHAR2(4000 CHAR),
ABNAME VARCHAR2(50 CHAR),
ADDRESS VARCHAR2(200 CHAR),
CITY VARCHAR2(30 CHAR),
X NUMBER(10,5),
Y NUMBER(10,5),
POIID NUMBER,
ID NUMBER
)。

指定分词

oracle文档说'chinese_lexer'只能用于utf8字符集，经测试在9i，zhs16gbk，windows也可以。

BEGIN

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

END;

－－指定检索的字段

EXEC ctx_ddl.create_preference('mymds', 'MULTI_COLUMN_DATASTORE');

EXEC ctx_ddl.set_attribute('mymds', 'columns', 'chiname, abname, address, city, x ,y');

－－建索引

CREATE INDEX mc_idx ON test1(CHINAME) INDEXTYPE IS ctxsys.CONTEXT PARAMETERS('datastore mymds LEXER my_lexer')

－－测试

select * from test1 where contains(chiname,'中国',1)>0

－－查看分词结果

select * from DR$MC_IDX$I

－－查看错误信息

select * from ctx_user_index_errors