Oracle绑定变量如何提升相关效率 |
|
本文标签：Oracle绑定变量 当我们把一个sql语句提交完后，Oracle绑定变量会检查一下在共享缓冲池（shared pool）中有无与之完全相同的相关语句，如果有的话只须执行软分析即可，否则就得进行硬分析 。以下就是文章的具体内容介绍 。 在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低 。这种情况最好不要使用绑定变量 。 但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况： 1．对于隔相当一段时间才执行一次的SQL语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消 2．数据仓库的情况下 。 绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用 。不能用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); 已创建 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)); 表已创建 。 已用时间: 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 这段代码同样是执行了1000条insert语句，但是每一条语句都是不同的，因此Oracle会把每条语句硬解析一次，其效率就比前面那段就低得多了 。如果要提高效率，不妨使用绑定变量将循环中的语句改为 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 这样执行的效率就高得多了 。 在PL/SQL中，引用变量即是引用绑定变量 。但是在pl/sql中动态sql并不是这样 。在vb，java以及其他应用程序中都得显式地利用Oracle绑定变量 。对于绑定变量的支持不仅仅限于Oracle,其他RDBMS向SQL SERVER也支持这一特性 。 |
