oracle模糊查询导致cpu高,oracle模糊查询效率提高「建议收藏」

分2种思路考虑模糊查询的效率的提高。–注：专注处理百万级数据量，小量数据就算了

第一种：把数据存到业务内存中，通过查询内存提高效率。(要求：硬件内存要高)

1，如果想实现内存动态数据，可以监控表数据变化更新内存。

第二种：提高oracle数据库的查询方式。

1、使用两边加‘%’号的查询，oracle是不通过索引的，所以查询效率很低。

例如：select count(*) from lui_user_base t where t.user_name like ‘%cs%’;

2、like ‘…%’和 like’%…’虽然走了索引，但是效率依然很低。

3、有人说使用如下sql，他的效率提高了10倍，但是数据量小的时候

select count(*) from lui_user_base where rowid in (

select rowid from lui_user_base t where t.user_name like ‘%cs%’

)

我拿100w跳数据做了测试，效果一般，依然很慢，原因：

select rowid from lui_user_base t where t.user_name like ‘%cs%’   这条sql执行很快，那是相当的快，

但是放到select count(*) from lui_user_base where rowid in()里后，效率就会变的很慢了。

4、select count(*) from lui_user_base t where instr(t.user_name,’cs’)> 0

这种查询效果很好，速度很快，推荐使用这种。因为我对oracle内部机制不是很懂，只是对结果做了一个说明。。。惭愧啊。。。。

5、有人说了用全文索引，我看了，步骤挺麻烦，但是是个不错的方法，留着备用：

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

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

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.51cto.com/archiver/tid-26270.html

8，http://oracle.chinaitlab.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