避開綁定變數的弱點的一個經典解決方案——《Oracle高效設計》學習筆記

假如我們在應用程式中對資料庫進行查詢時，經常會對某個欄位使用Like語句，而資料庫是對這個欄位添加了索引的，如果查詢的sql中like

 

的內容是以%開頭的，那麼索引很可能會被忽略，但當like的內容不是以%開頭的，索引會被利用。假設這個查詢會被經常使用，我們希望對

 

於這些查詢使用綁定變數，這樣可以減少硬解析的次數，但使用綁定變數的代價是，在查詢時我們很可能使用錯誤的查詢計劃。

 

對於這個問題《Oracle高效設計》中給出了一個很經典的解決方案，使得我們在使用綁定變數同時，也能夠使用正確的查詢計劃。

 

先做一個實驗：

create table t
as
select a.* from all_objects a;
create index t_idx on t(object_name);

analyze table t compute statistics
for table for all indexes for all indexed columns;

variable x varchar2(50)
alter session set sql_trace=true;
set termout off
exec := '%';
select * from t x_was_percent where object_name like :x;
exec := 'Y%';
select * from t x_was_NOT_PCT where object_name like :x;

 

使用tkprof工具，查看跟蹤結果：

 

BEGIN := '%'; END;

select *
from
 t x_was_percent where object_name like

 

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 

 

Rows     Row Source Operation
-------  ---------------------------------------------------
  49654  TABLE ACCESS FULL T (cr=3950 pr=0 pw=0 time=198752 us)

 

BEGIN := 'Y%'; END;
select *
from
 t x_was_NOT_PCT where object_name like

 

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=69 us)
      1   INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=31 us)(object id 52917)

 

從測試結果可以看出，兩個查詢都使用了正確的查詢計劃。由於Oracle會在第一次執行綁定變數的sql時，

 

使用綁定變數窺視技術（根據綁定變數實際值確定查詢計劃），又因為，我們對於兩個查詢做了點改動（注意黑體，

 

第一條sql的t被命名為x_was_percent ，第二條sql的t被命名為x_was_NOT_PCT ），

 

使得Oracle把這兩個相同的查詢做為不同查詢來看待，每個查詢都解析了一遍，這就使得兩個查詢都使用了正確的

 

查詢計劃，只要保證程式，在後續的類似查詢凡是以%號開頭的，都使用第一條sql，如果不以%開頭的都使用第二

 

條sql，就可以保證雖然使用了綁定變數，但每次查詢都使用了正確的查詢計劃。