繫結變數就一定好麼

通常在OLTP系統中，我們儘可能的使用繫結變數,因為可以減少硬解析.那麼有個問題,使用繫結變數就一定好麼?
我們來看一個試驗:

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCRI VARCHAR2(20)

SQL> select id,count(1) from t group by id;

ID COUNT(1)
---------- ----------
1 65536
99 1

可以看到,表t中id的分佈嚴重不平衡.
SQL> create index t_inx on t (id);

索引已建立。

SQL> set autotrace traceonly
SQL> select * from t where id=1;

已選擇65536行。

執行計劃
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62797 | 1533K| 58 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 62797 | 1533K| 58 (6)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=1)

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
9 recursive calls
0 db block gets
4614 consistent gets
5 physical reads
0 redo size
883013 bytes sent via SQL*Net to client
48459 bytes received via SQL*Net from client
4371 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65536 rows processed

SQL>

SQL> select * from t where id=99;

執行計劃
----------------------------------------------------------
Plan hash value: 1579008347

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_INX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=99)

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
7 recursive calls
0 db block gets
71 consistent gets
1 physical reads
0 redo size
473 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到，在上面的例子中，不使用繫結變數，當id=1時資料庫選擇了全表掃描，而id=99時資料庫選擇了索引.

我們來看使用繫結變數的情況
SQL> var id_var number

SQL> exec :id_var:=1

PL/SQL 過程已成功完成。

SQL>
SQL>
SQL>
SQL> select * from t where id=:id_var;

已選擇65536行。

執行計劃
----------------------------------------------------------
Plan hash value: 775956470

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628 | 15700 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 628 | 15700 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IX | 251 | | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=TO_NUMBER(:ID_VAR))

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
13 recursive calls
0 db block gets
4614 consistent gets
0 physical reads
0 redo size
883013 bytes sent via SQL*Net to client
48459 bytes received via SQL*Net from client
4371 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
65536 rows processed

SQL> exec :id_var:=99

PL/SQL 過程已成功完成。

SQL>
SQL>
SQL>
SQL> select * from t where id=:id_var;

執行計劃
----------------------------------------------------------
Plan hash value: 775956470

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628 | 15700 | 54 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 628 | 15700 | 54 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IX | 251 | | 64 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=TO_NUMBER(:ID_VAR))

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
239 consistent gets
0 physical reads
0 redo size
473 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

我們看到，當使用繫結變數的情況下，由於在開始時已經完成了硬解析，在之後的執行過程中都是按照硬解析生成的執行計劃執行，所以不管id=1還是id=99都是用來索引.這對id=1的情況，造成了額外的IO浪費.

因此，使用繫結變數並不一定好.根據自己的情況來選擇是否使用.

[@more@]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23850820/viewspace-1041579/，如需轉載，請註明出處，否則將追究法律責任。