Oracle模糊查询之（4.采用全文索引解决模糊查询，给出具体步骤）采用全文索引解决模糊查询速度慢的问题[主文]

采用全文索引解决模糊查询速度慢的问题

上一篇 /下一篇  2009-09-22 20:58:34

查看( 281 ) / 评论( 2 ) / 评分( 8 / 0 )

转自http://sandish.itpub.net/post/4899/464369

 

众所周知，使用 like 进行模糊查询速度极差，包括 like 'AAA%' ，like '%AAA'，like '%AAA%'，like '%A%A%'以及采用“_”进行单字符匹配的那些模糊查询。网上有很多文章讲到如何提高like查询，提到 like 'AAA%'能够使用到索引，而like '%AAA' ，使用创建反向函数的索引来提高查询效率。但一般情况下，是无法约定客户端采用哪种like查询，难道说把所有的这些情况都进行if判断吗？

为这个事情脑袋疼了无数次。最近，一客户“无理”要求对用户地址模糊查询速度太慢。在数十万的用户记录下查询，要求5秒之内必须查询到记录。

想破脑袋还是找不到方法。有同事找了本Lucene的书给我看，说是能解决。翻来覆去的看了2，3遍，始终想不出这玩意儿怎么用。

突然想到oracle也有全文索引一说，以前只是别人提起过这个词。与网上朋友一聊，说是似乎可以解决，但他忘了怎么用了。

半夜12点，赶紧爬起来，到google上查资料。还真有两下子，研究了几个小时，有所获。第二天白天没时间研究，晚上继续，最终把全文索引搞定，解决了模糊查询速度慢的问题，在数十万条用户数据中， 对用户地址进行模糊查询速度在2秒以内就能够查到。

-------------------------------------------------------------------------

以下是创建全文索引的方法（网上有文章提到使用图形化界面，我用图形化界面创建全文索引，创建了一个晚上，第二天起床居然还没完。但用SQL命令15分钟左右就搞定）：

对cmng_custominfo 表中的address字段做全文检索：
1，在oracle9201中需要创建一个分词的东西：

BEGIN
ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');
--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用
end;

2，创建全文检索：

CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');

3，查询时候，使用：

select * from cmng_custominfo where contains (address, '金色新城')>1;

自己测试,发现select * from cmng_custominfo where contains (address, '%金色新城%')>1;才能实现模糊查询,并且使用了索引,数据库版本为oracle9.0.1.1.1

4，需要定期进行同步和优化：
同步：根据新增记录的文本内容更新全文搜索的索引。

begin
ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');
end;

优化：根据被删除记录清除全文搜索索引中的垃圾

begin
ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');
end;

5，采用job做步骤4中的工作：

1)该功能需要利用oracle的JOB功能来完成
因为oracle9I默认不启用JOB功能，所以首先需要增加ORACLE数据库实例的JOB配置参数：
job_queue_processes=5
重新启动oracle数据库服务和listener服务。

2)同步 和 优化
--同步 sync:
variable jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');', SYSDATE, 'SYSDATE + (1/24/4)');
　commit;
END;

--优化
variable jobno number;
begin
　DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');
　commit;
END;

其中， 第一个job的SYSDATE + (1/24/4)是指每隔15分钟同步一次，第二个job的SYSDATE + 1是每隔1天做一次全优化。具体的时间间隔，可以根据应用的需要而定

6，索引重建
重建索引会删除原来的索引，重新生成索引，需要较长的时间。
重建索引语法如下：
ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

据网上一些用家的体会，oracle重建索引的速度也是比较快的，有一用家这样描述：

Oracle 的全文检索建立和维护索引要比ms sql server都要快得多，笔者的65万记录的一个表建立索引只需要20分钟，同步一次只需要1分钟。
因此，也可以考虑用job的办法定期重建索引。

参考资料：
1，http://blog.csdn.net/yurenjia/archive/2007/04/08/1556306.aspx
2，http://topic.csdn.net/u/20080117/23/34004f4a-4989-47ef-8764-0b7e3bf737a7.html
3，http://tenwe.com/tech/database/oracle/200702/content_561_4.shtml
4，http://www.knowsky.com/389357.html
5，http://yangtingkun.itpub.net/post/468/195520
6，http://bbs.zdnet.com.cn/archiver/tid-120474.html
7，http://bbs.违规广告.com/archiver/tid-26270.html
8，http://oracle.**.com/exploiture/720104_3.html
9，http://www.33kuai.cn/html/shujuku/20080126/5314_2.html
10，http://www.xrss.cn/Dev/DataBase/20084218963.Html