绑定变量通过三种方式影响应用程序。
第一,从开发角度来看,它们既可以让编程变简单,也可以让编程变复杂(更准确地说,就是需要编写的代码或多或少)。这种情况下,影响取决于用来执行SQL语句的应用编程接口。例如,如果你正在编写PL/SQL代码,使用绑定变量来执行会更容易。另一方面,如果你正在使用JDBC编写Java程序,没有绑定变量的情况下执行SQL语句会更容易。
第二,从安全角度看,绑定变量减轻了SQL注入攻击的风险。
第三,从性能角度看,使用绑定变量有利有弊。
优势
绑定变量在性能方面的优势是它们允许共享库缓存中的父游标,这样就避免了硬解析以及相关的额外开销。接下来的例子是对脚本bind_variables_graduation.sql的输出的摘录,展示了三个INSERT语句由于使用绑定变量而共享了库缓存中的同一个游标。
DROP TABLE t purge;
CREATE TABLE t (n NUMBER, v VARCHAR2(4000));
VARIABLE n NUMBER
VARIABLE v VARCHAR2(32)
EXECUTE :n := 1; :v := 'Helicon';
INSERT INTO t (n, v) VALUES (:n, :v);
EXECUTE :n := 2; :v := 'Trantor';
INSERT INTO t (n, v) VALUES (:n, :v);
EXECUTE :n := 3; :v := 'Kalgan';
INSERT INTO t (n, v) VALUES (:n, :v);
SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';
但是有些情况下,即使使用了绑定变量,还是创建了几个子游标,如下面的例子所示。注意,INSERT语句和之前的例子是一样的。只是VARCHAR2变量的最大值发生了改变(从32到33)。
VARIABLE v VARCHAR2(33)
EXECUTE :n := 4; :v := 'Terminus';
INSERT INTO t (n, v) VALUES (:n, :v);
SELECT sql_id, child_number, executions
FROM v$sql
WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';
创建新的子游标(1)是因为前面三个INSERT语句和第四个之间的执行环境发生了改变。下面的例子中的不匹配项,可以通过查询v$sql_shared_cursor视图来确认。注意,bind_length_upgradeable列只在11.2版本中存在。在之前的版本中,这个信息由bind_mismatch列提供。
SELECT child_number, bind_length_upgradeable
FROM v$sql_shared_cursor
WHERE sql_id = '6cvmu7dwnvxwj';
这是因为数据库引擎使用了一个叫作绑定变量分级的特性。这个特性的目标是通过将绑定变量按等级(随大小变化)分成四个组来最小化子游标的数量。
第一组包含最大至32字节的绑定变量,
第二个组包含33至128字节的绑定变量,
第三组包含大小为129至2000字节的绑定变量,
最后一组包含大于2000字节的绑定变量。
NUMBER数据类型的绑定变量按它们的最大长度22字节划分等级。如下面的例子所示,v$sql_bind_metadata视图显示了每个组的最大长度。注意值128的用法,即使子游标1的绑定变量长度定义为33。
SELECT s.child_number,
m.position,
m.max_length,
decode(m.datatype, 1, 'VARCHAR2', 2, 'NUMBER', m.datatype) AS datatype
FROM v$sql s, v$sql_bind_metadata m
WHERE s.sql_id = '&sql_id'
AND s.child_address = m.address
ORDER BY 1, 2;
注意 这个例子展示了当使用不同组的绑定变量时出现了绑定错配的情况。只有当关联到新的组的绑定变量比原来大时才会出现这种情况。实际上,仔细回顾这个例子,绑定变量的大小一直在增加。如果它们是在减小,那么所有的执行都可以共享同一个子游标。如果用VARCHAR2类型的最大值创建子游标,那么所有比它小的VARCHAR2绑定变量都可以共享它。
很显然,每次产生一个新的子游标就表示一个执行计划的生成。这个新的执行计划是否能够被其他子游标使用也取决于绑定变量的值。
在WHERE条件中使用绑定变量对于性能方面的劣势是,在某些条件下会对查询优化器隐藏重要的信息。事实上,对于查询优化器而言,获取字面值比使用绑定变量更好。使用字面值时,查询优化器总能够做出最接近的估算。
当涉及范围比较谓词(例如基于BETWEEN、大于或小于的比较条件),检查一个值是否在可用值范围之外时(即小于列中存储的最小值或大于列中存储的最大值),或者使用直方图时,情况尤其如此。例如,拿一个1000行数据的表来说,在id列上,所有的整型值都在1(最小值)和1000(最大值)之间。
SELECT count(id),count(DISTINCT id),min(id),max(id)FROM t;
当一个用户选择id小于990的所有记录时,查询优化器就知道(归功于对象统计信息)表中大约99%的数据被选中了。因此,它会选择使用全表扫描的执行计划。同时还要注意估算的基数(执行计划中的Rows列)几乎准确对应查询应返回的行数。
SELECT count(pad) FROM t WHERE id