綁定變數的弱點——《Oracle高效設計》學習筆記

綁定變數雖然有很多優點，但在某些情況下不可以使用綁定變數，

 

比如，對於被綁定的欄位的值，如果對於不同的值，Oracle應該使用不同的查詢計劃，這種情況下就不應該使用綁定變數。

 

 

以下的例子說明了這個問題：

 

create table records_to_be_processed
as
select decode( mod(rownum,100), 0, 'N', 'Y' ) processed, a.*
  from all_objects a;

create index processed_idx on records_to_be_processed(processed);

analyze table records_to_be_processed compute statistics
for table
for all indexes
for all indexed columns
/

 

Alter system flush shared_pool;
Alter session set sql_trace=true;
variable processed varchar2(1);
exec :processed := 'N';
select *
  from records_to_be_processed initially_N
 where processed = :processed;

exec :processed := 'Y';
select *
  from records_to_be_processed initially_N
 where processed = :processed;

exec :processed := 'Y';
select *
  from records_to_be_processed initially_Y
 where processed = :processed;

exec :processed := 'N';
select *
  from records_to_be_processed initially_Y
 where processed = :processed;
set autotrace off

tkprof的結果摘錄如下（設定aggregate=no）：

查詢1：

select *
  from records_to_be_processed initially_N
 where processed = :processed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       35      0.00       0.00          0        532          0         496
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.00       0.01          0        532          0         496

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

Rows     Row Source Operation
-------  ---------------------------------------------------
    496  TABLE ACCESS BY INDEX ROWID RECORDS_TO_BE_PROCESSED (cr=532 pr=0 pw=0 time=9940 us)
    496   INDEX RANGE SCAN PROCESSED_IDX (cr=36 pr=0 pw=0 time=4966 us)(object id 52912)

 

查詢2：
select *
  from records_to_be_processed initially_N
 where processed = :processed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3279      0.20       0.27          0       7289          0       49157
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3281      0.20       0.27          0       7289          0       49157

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  49157  TABLE ACCESS BY INDEX ROWID RECORDS_TO_BE_PROCESSED (cr=7289 pr=0 pw=0 time=491723 us)
  49157   INDEX RANGE SCAN PROCESSED_IDX (cr=3369 pr=0 pw=0 time=196654 us)(object id 52912)

查詢3：

select *
  from records_to_be_processed initially_Y
 where processed = :processed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3279      0.21       0.21          0       3925          0       49157
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3281      0.21       0.21          0       3925          0       49157

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

Rows     Row Source Operation
-------  ---------------------------------------------------
  49157  TABLE ACCESS FULL RECORDS_TO_BE_PROCESSED (cr=3925 pr=0 pw=0 time=196768 us)

查詢4：

select *
  from records_to_be_processed initially_Y
 where processed = :processed

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       35      0.00       0.00          0        734          0         496
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.00       0.00          0        734          0         496

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

Rows     Row Source Operation
-------  ---------------------------------------------------
    496  TABLE ACCESS FULL RECORDS_TO_BE_PROCESSED (cr=734 pr=0 pw=0 time=6489 us)

 

這個結果說明，如果使用了綁定變數，Oracle在第一次查詢時，會通過綁定變數窺視，確定使用何種查詢計劃，

 

以後就使用完全相同的查詢計劃，不再考慮因不同的輸入值而更新查詢計劃，這樣做在有些情況下會導致資料庫

 

執行錯誤的查詢計劃。

 

因此，對於這種情況，正確的做法是不使用綁定變數。