Oracle綁定變量如何提升相關(guān)效率

1、當(dāng)我們把一個sql語句提交完后，Oracle綁定變量會檢查一下在共享緩沖池（ shared pool ）中有無與之完全相同的相關(guān)語句，如果有的話只須執(zhí)行軟分析即可，否則就得進(jìn)行硬分析。而唯一使得oracle能夠重復(fù)利用執(zhí)行計(jì)劃的方法就是采用綁定變量。綁定變量的實(shí)質(zhì)就是用于替代sql語句中的常量的替代變量。綁定變量能夠使得每次提交的sql語句都完全一樣。在字段（包括字段集）建有索引，且字段（集）的集的勢非常大（也就是有個值在字段中出現(xiàn)的比例特別的大）的情況下，使用綁定變量可能會導(dǎo)致查詢計(jì)劃錯誤，因而會使查詢效率非常低。這種情況最好不要使用綁定變量。但是并不是任何情況下都需要使用綁定變量，下面是兩

2、種例外情況:1 .對于隔相當(dāng)一段時(shí)間才執(zhí)行一次的SQL語句，這是利用綁定變量的好處會被不能有效利用優(yōu)化器而抵消2.數(shù)據(jù)倉庫的情況下。綁定變量不能當(dāng)作嵌入的字符串來使用,只能當(dāng)作語句中的變量來用。不能用Oracle綁定變量來代替表名、過程名、字段名等從效率來看，由于 OraclelOG放棄了RBO全面引入CBO因此，在10G中使用綁定變量效率的提升比9i中更為明顯。舉例:普通sql語句:SELECT fname, l name, pcode FROM cust WHERE id = 674;SELECT fname, l name, pcode FROM cust WHERE id = 234;

3、SELECT fname, l name, pcode FROM cust WHERE id = 332;含綁定變量的sql語句:SELECT fname, l name, pcode FROM cust WHERE id = :cust_ no;Sql*plus中使用Oracle綁定變量:SQL> set tim ing onSQL> variable x nu mber;SQL> exec :x :=8P L/SQL過程已成功完成。已用時(shí)間：00: 00: 00.03SQL> select * from A;ID已用時(shí)間：00: 00: 00.06SQL> i

4、nsert into A values(:x);已創(chuàng)建1行。已用時(shí)間：00: 00: 00.01SQL> select * from A;ID已用時(shí)間：00: 00: 00.01PL/SQL很多時(shí)候都會自動綁定變量而無需編程人員操心，即很多你寫得sql語句都會自動利用 Oracle綁定變量，如下例所示:SQL> Set timi ng onSQL> declare2 I NUMBER;3 BEGIN4 FOR I IN 1.1000 LOOP5 INSERT INTO A VALUES(I);6 end loop;7 end;P L/SQL過程已成功完成。已用時(shí)間：00: 0

5、0: 00.128 /這段代碼是不需要使用Oracle綁定變量的方法來提高效率的， Oracle會自動將其中的變量綁定。SQL> create table D ( id varchar(IO);表已創(chuàng)建。已用時(shí)間：00: 00: 00.50SQL> declare2 i nu mber;3 sqistr varchar(2000);4 begi n5 for i in 1.1000 loop6 sqlstr :=' i nsert into d values('|to_char(i)|')'7 execute immediate sqlstr;8 e

6、nd loop;9 end;10 /P L/SQL過程已成功完成。已用時(shí)間：00: 00: 00.68這段代碼同樣是執(zhí)行了1000條insert語句，但是每一條語句都是不同的，因此Oracle會把每條語句硬解析一次，其效率就比前面那段就低得多了。如果要提高效率，不妨使用綁定變量將循環(huán)中的語句改為SQL> declarev2 i nu mber;3 sqlstr varchar(2000);4 begin5 for i in 1.1000 loop6 sqlstr :=' i nsert into d values(:i)'7 execute immediate sqlstr using i;8 end loop;9 end;10 /P L/SQL過程已成功完成。已用時(shí)間：00: 00: 00.18這樣執(zhí)行的效率就高得多了。在PL/SQL中，引用變量即是引用綁定變量。但是在 Pl/sql中動態(tài)sql并不是這樣。在 vb，java以及其他應(yīng)用程序