Oracle綁定變量如何提升相關(guān)效率
當(dāng)我們把一個sql語句提交完后，Oracle綁定變量會檢查一下在共享緩沖池（shared pool）中有無與之完全相同的相關(guān)語句，如果有的話只須執(zhí)行軟分析即可，否則就得進(jìn)行硬分析。以下就是文章的具體內(nèi)容介紹。
而唯一使得oracle 能夠重復(fù)利用執(zhí)行計劃的方法就是采用綁定變量。綁定變量的實質(zhì)就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次提交的sql語句都完全一樣。
在字段(包括字段集)建有索引，且字段(集)的集的勢非常大（也就是有個值在字段中出現(xiàn)的比例特別的大）的情況下，使用綁定變量可能會導(dǎo)致查詢計劃錯誤，因而會使查詢效率非常低。這種情況最好不要使用綁定變量。
但是并不是任何情況下都需要使用綁定變量，下面是兩種例外情況：
1．對于隔相當(dāng)一段時間才執(zhí)行一次的SQL語句，這是利用綁定變量的好處會被不能有效利用優(yōu)化器而抵消
2．?dāng)?shù)據(jù)倉庫的情況下。
綁定變量不能當(dāng)作嵌入的字符串來使用，只能當(dāng)作語句中的變量來用。不能用Oracle綁定變量來代替表名、過程名、字段名等.
從效率來看，由于Oracle10G放棄了RBO，全面引入CBO，因此，在10G中使用綁定變量效率的提升比9i中更為明顯。
舉例：
普通sql語句：
SELECT fname, lname, pcode FROM cust WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id = 234;
SELECT fname, lname, pcode FROM cust WHERE id = 332;
含綁定變量的sql 語句：
SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;
Sql*plus 中使用Oracle綁定變量：
SQL> set timing on
SQL> variable x number;
SQL> exec :x :=8
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.03
SQL> select * from A;
ID
3
5
已用時間: 00: 00: 00.06
SQL> insert into A values(:x);
已創(chuàng)建 1 行。
已用時間: 00: 00: 00.01
SQL> select * from A;
ID
3
8
5
已用時間: 00: 00: 00.01
PL/SQL很多時候都會自動綁定變量而無需編程人員操心，即很多你寫得sql語句都會自動利用Oracle綁定變量，如下例所示：
SQL> Set timing on
SQL> declare
2 I NUMBER;
3 BEGIN
4 FOR I IN 1..1000 LOOP
5 INSERT INTO A VALUES(I);
6 end loop;
7 end;
8 /
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.12
這段代碼是不需要使用Oracle綁定變量的方法來提高效率的，Oracle會自動將其中的變量綁定。
SQL> create table D ( id varchar(10));
表已創(chuàng)建。
已用時間: 00: 00: 00.50
SQL> declare
2 i number;
3 sqlstr varchar(2000);
4 begin
5 for i in 1..1000 loop
6 sqlstr :=' insert into d values('||to_char(i)||')';
7 execute immediate sqlstr;
8 end loop;
9 end;
10 /
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.68
這段代碼同樣是執(zhí)行了1000條insert語句，但是每一條語句都是不同的，因此Oracle會把每條語句硬解析一次，其效率就比前面那段就低得多了。如果要提高效率，不妨使用綁定變量將循環(huán)中的語句改為
SQL> declarev
2 i number;
3 sqlstr varchar(2000);
4 begin
5 for i in 1..1000 loop
6 sqlstr :=' insert into d values(:i)';
7 execute immediate sqlstr using i;
8 end loop;
9 end;
10 /
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.18
這樣執(zhí)行的效率就高得多了。
在PL/SQL中，引用變量即是引用綁定變量。但是在pl/sql中動態(tài)sql并不是這樣。在vb，java以及其他應(yīng)用程序中都得顯式地利用Oracle綁定變量。對于綁定變量的支持不僅僅限于Oracle,其他RDBMS向SQL SERVER也支持這一特性。
【編輯推薦】
