Oracle 绑定变量

oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.

一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。

之所以这样是因为门闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。

 

绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。

 

绑定变量是在通常情况下能提升效率，非正常的情况如下：

 

在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。这种情况最好不要使用绑定变量。

 

但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况：
1．对于隔相当一段时间才执行一次的SQL语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消
2．数据仓库的情况下。

 

 

绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用绑定变量来代替表名、过程名、字段名等.

 

从效率来看，由于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 中使用绑定变量：

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语句都会自动利用绑定变量，如下例所示：
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会自动将其中的变量绑定。

 

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> declare

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,其他RDBMS向SQL SERVER也支持这一特性

什么是SQL绑定变量，如何实现绑定变量？

1. 认识绑定变量：

绑定变量是为了减少解析的，比如你有个语句这样
select aaa,bbb from ccc where ddd=eee;
如果经常通过改变eee这个谓词赋值来查询，像如下
select aaa,bbb from ccc where ddd=fff;
select aaa,bbb from ccc where ddd=ggg;
select aaa,bbb from ccc where ddd=hhh;
每条语句都要被数据库解析一次，这样比较浪费资源，如果把eee换成“:1”这样的绑定变量形式，无论ddd后面是什么值，都不需要重复解析

Java实现绑定变量的方法：

PreparedStatement pstmt = con.prepareStatement("UPDATE employees SET salay = ? WHERE id = ?");
pstmt.setBigDecimal(1, 15.00);
pstmt.setInt(2, 110592);
/result statmement:   UPDATE employees SET salay = 15.00 WHERE id = 110592
pstmt.executeQuery();

假设要将id从1到10000的员工的工资都更新为150.00元，不使用绑定变量，则：

sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 1");
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 2");
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 3");
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 4");
....
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 10000");

使用绑定变量，则：

PreparedStatement pstmt;
for (id = 1; id < 10000; id++)
{
  if (null == pstmt)
    pstmt = con.prepareStatement("UPDATE employees SET salay = ? WHERE id = ?");
  pstmt.setBigDecimal(1, 150.00);
  pstmt.setInt(2, id);
  pstmt.executeQuery();
}

二者区别在于，不用绑定变量，则相当于反复解析、执行了1w个sql语句。使用绑定变量，解析sql语句只用了一次，之后的9999次复用第一次生成的执行计划。显然，后者效率会更高一些。

2. 什么时候不应该/不必要使用绑定变量

• a. 如果你用数据仓库，一条大查询一跑几个小时，根本没必要做绑定变量，因为解析的消耗微乎其微。
• b. 变量对优化器产生执行计划有很重要的影响的时候：绑定变量被使用时，查询优化器会忽略其具体值，因此其预估的准确性远不如使用字面量值真实，尤其是在表存在数据倾斜(表上的数据非均匀分布)的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。

3. 绑定变量在OceanBase中的实现

• 目前OceanBase中实现了绑定变量，目的主要是为了编程方便，而不是为了降低生成执行计划的代价。为什么呢？因为OceanBase中目前使用的是一种”静态执行计划“，无论什么Query，执行流程都一样。OB在前端代理ObConnector中实现绑定变量，将用户传入的变量进行to_string()操作，替代SQL语句中相应的部分，形成一个完整的SQL。然后这个SQL传递给MS，MS按照标准流程来解析和执行。相信不远的将来，OB将会实现真正意义上的绑定变量，让用户享受到绑定变量带来的好处。