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