Oracle 模糊查詢 最佳化

標籤：

模糊查詢是資料庫查詢中經常用到的，一般常用的格式如下：

（1）欄位  like ‘%關鍵字%‘   欄位包含"關鍵字“的記錄   即使在目標欄位建立索引也不會走索引，速度最慢  

（2）欄位  like ‘關鍵字%‘      欄位以"關鍵字"開始的記錄   可以使用到在目標欄位建立的升序索引

（3）欄位 like ‘%關鍵字‘      欄位以"關鍵字“結束的記錄    可以使用到目標欄位建立的降序索引

 

對於無法使用索引的 ‘%關鍵字%‘ 模式，有沒有辦法最佳化呢，答案是肯定的，

在ORacle中提供了instr(strSource,strTarget)函數，比使用‘%關鍵字%‘的模式效率高很多。

instr函數說明：

INSTR

　　(源字串, 目標字串, 起始位置, 匹配序號)

　　在Oracle/PLSQL中，instr函數返回要截取的字串在源字串中的位置。只檢索一次，就是說從字元的開始

　　到字元的結尾就結束。

　　文法如下：

　　instr( string1, string2 [, start_position [, nth_appearance ] ] )

　　參數分析：

　　string1

　　源字串，要在此字串中尋找。

　　string2

　　要在string1中尋找的字串.

　　start_position

　　代表string1 的哪個位置開始尋找。此參數可選，如果省略預設為1. 字串索引從1開始。如果此參數為正，從左至右開始檢索，如果此參數為負，從右至左檢索，返回要尋找的字串在源字串中的開始索引。

　　nth_appearance

　　代表要尋找第幾次出現的string2. 此參數可選，如果省略，預設為 1.如果為負數系統會報錯。

　　注意：

　　如果String2在String1中沒有找到，instr函數返回0.

　　樣本：

　　SELECT instr(‘syranmo‘,‘s‘) FROM dual; -- 返回 1

　　SELECT instr(‘syranmo‘,‘ra‘) FROM dual;  -- 返回 3

　　SELECT instr(‘syran mo‘,‘a‘,1,2) FROM dual;  -- 返回 0

　　對比：

　　 instr(title,‘手冊‘)>0  相當於  title like ‘%手冊%‘

 　　instr(title,‘手冊‘)=1  相當於  title like ‘手冊%‘

 　　instr(title,‘手冊‘)=0  相當於  title not like ‘%手冊%‘

模糊查詢最佳化：

　　瞭解了instr函數的用法，最佳化就變得簡單了,例如 %關鍵字%   等同於  instr(欄位,‘關鍵字‘)>0 

 

實際應用：

t表中將近有1100萬資料，很多時候，我們要進行字串匹配，在SQL語句中，我們通常使用like來達到我們搜尋的目標。但經過實際測試發現，like的效率與instr函數差別相當大。下面是一些測試結果：

SQL> set timing on
SQL> select count(*) from t where instr(title,‘手冊‘)>0;

  COUNT(*)
----------
     65881

Elapsed: 00:00:11.04
SQL> select count(*) from t where title like ‘%手冊%‘;

  COUNT(*)
----------
     65881

Elapsed: 00:00:31.47
SQL> select count(*) from t where instr(title,‘手冊‘)=0;

  COUNT(*)
----------
  11554580

Elapsed: 00:00:11.31
SQL> select count(*) from t where title not like ‘%手冊%‘;

  COUNT(*)
----------
  11554580

另外，我在結另外一個2億多的表，使用8個並行，使用like查詢很久都不出來結果，但使用instr,4分鐘即完成尋找，效能是相當的好。這些小技巧用好，工作效率提高不少。通過上面的測試說明，ORACLE內建的一些函數，是經過相當程度的最佳化的。

 

instr(title,’aaa’)>0 相當於like

instr(title,’aaa’)=0 相當於not like

 

特殊用法：

 

select   id, name from users where instr(‘101914, 104703‘, id) > 0; 
  它等價於 
select   id, name from users where id = 101914 or id = 104703;

 

 

 

使用Oracle的instr函數與索引配合提高模糊查詢的效率

一般來說，在Oracle資料庫中，我們對tb表的name欄位進行模糊查詢會採用下面兩種方式：
1.select * from tb where name like ‘%XX%‘;
2.select * from tb where instr(name,‘XX‘)>0;

若是在name欄位上沒有加索引，兩者效率差不多，基本沒有區別。

為提高效率，我們在name欄位上可以加上非唯一性索引：
create index idx_tb_name on tb(name);

這樣，再使用

select * from tb where instr(name,‘XX‘)>0;

這樣的語句查詢，效率可以提高不少，表資料量越大時兩者差別越大。但也要顧及到name欄位加上索引後DML語句會使索引資料重新排序的影響。

 

 

另一種未知的方案：

有人說了用全文索引，我看了，步驟挺麻煩，但是是個不錯的方法，留著備用：

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

Oracle 模糊查詢 最佳化