DB2 9 使用开发（733 测验）认证指南，第 8 局部: 初级编程(3)

用户定义的数据标准、并发性思量因素等等

参数标记

什么是参数标记？

参数标记是 SQL 语句中变量的占位符。它们由一个问号字符示意（?）。经过历程利用参数标记，可以淘汰需求编译和优化的 SQL 语句数量。您大年夜概想知道，假若已经利用了静态 SQL，那么为什么要在使用递次中利用参数标记，并且每次都要构建语句字符串。您很快就会体味这个罕有成果的谜底。

肯定要记着，一个参数标记交换一个字面值，如下所示：

SELECT firstname, lastname
FROM employee
WHERE empno = ?
SELECT firstname, lastname
FROM employee
WHERE hiredate > ?
    AND mgrno = ?

我们来看三个示例，它们演示了在利用参数标记时罕有的错误。上面是第一个示例：

SELECT ?, lastname
FROM employee
WHERE empno = ?

上面的 SQL 语句用一个参数标记交换 SELECT 列表中的一个列名。为了优化究诘，DB2 需求知道将要前往哪些列中的数据。所前往的列会极大年夜地影响 DB2 如何检索数据。比喻，它们可以确定是利用索引，照样阻止表扫描。

让我们看一看另一个错误代码的示例：

SELECT firstname, lastname
FROM ?
WHERE empno = ?

在这个示例中，在 FROM 子句顶用一个参数标记交换表名。DB2 无法确定从哪个表中提取数据，是以无法编译这个 SQL 语句。

如今是最初一个示例：

SELECT firstname, lastname
FROM employee
WHERE ? = 14
    AND ? = ?

这个示例在 WHERE 子句中利用参数标记。它起首利用参数标记示意一个要与值 14 阻止对照的字段。它还利用两个参数标记来指定第二个谓词中的列和值字段。在这种情况下，DB2 无法确定谓词是什么以及值是什么，是以它无法编译这个语句。





回页首

利用参数标记的好处

参数标记会带来良多好处。个中主要的好处是淘汰了语句编译时分。DB2 在可以执行 SQL 语句之前先准备这个语句。在准备阶段，创建一个造访阴谋，它决定如何检索数据。当编译这个 SQL 语句时，DB2 反省称为 “包缓存（package cache）” 的数据库内存地区，个中搜罗比来执行的 SQL 语句及其相干的造访阴谋。假若缓存中的一个语句与提交的语句婚配，那么 DB2 可以重用它，而无需从新编译。

要想重用包缓存中的语句，提交的语句必需与缓存中的语句精确地婚配。让我们看一些示例，从而分析什么样的语句是婚配的。假定以下语句是包缓存中的初始语句：

SELECT deptName, location
FROM department
WHERE mgrno = '000056'

以下是一个婚配的语句：

SELECT deptName, location
FROM department
WHERE mgrno = '000056'

但是，上面这些语句都不会被当作婚配的语句：

SELECT deptName, location
FROM department
WHERE mgrno = '000055'
SELECT deptName, location
FROM department
WHERE mgrno = ?
SELECT deptName, location
FROM department
WHERE mgrno = :varMgrno

第一个语句不婚配是因为 '000055' 与 '000056' 差别。DB2 利用直接的文本婚配来反省语句能否相同。第二个语句不婚配是因为参数标记与 '000055' 不婚配。第三个示例婚配丢失败也是因为非常的缘由。

但是，假若在初始语句中利用了参数标记，那么在参数标记的职位地方上利用任何值的任何语句都会婚配。可以利用在运转时提供的任何值执行利用参数标记的 SQL 语句，比喻 '000055'、'005600'、'000123' 等等。

上面这个示例更深化地标明晰这个看法。假定以下语句是包缓存中的初始语句：

SELECT deptName, location
FROM department
WHERE mgrno = ?

下列语句将婚配它：

SELECT deptName, location
FROM department
WHERE mgrno = ?

下列语句不婚配：

SELECT deptName, location
FROM department
WHERE mgrno = 55

假若思量一下究诘大年夜概要花多长工夫阻止编译，那么可以大年夜概跳过编译阶段的劣势就变得很显著白。假定我们有一个究诘，其编译时分为 0.001 秒，执行时分为 0.001 秒。那么将要用多长工夫执行这个究诘 10,000 次呢？以下是确定总时分的简略公式：

总语句时分 = 语句编译时分   语句执行时分

在利用参数标记时，成效是：

0.001   (10000 * 0.001) = 10.001 秒

换句话说，语句只需阻止一次编译，然后执行语句 10,000 次。假若倒霉用参数标记，语句每次执行前都要编译，因为在包缓存中找不到婚配的语句，成效就是：

(10000 * 0.001)   (10000 * 0.001) = 20 秒

在这种情况下，在每次执行时都要包袱编译和执行成本。正如这个简略的示例所示，利用参数标记会将总语句时分减半！

每种编程措辞都依照本人的方法在运转时为参数标记提供理论值。清单 1 给出一个 SQL 存储历程示例，它在一个准备语句中利用参数标记，然后在执行这个语句之前为参数标记提供值。

清单 1. 在存储历程中利用参数标记

                    
CREATE PROCEDURE SIMPLE_UPDT_PROC (IN  p_emp_id      SMALLINT, 
                                   IN  p_new_salary  DECIMAL)
LANGUAGE SQL
BEGIN
  DECLARE v_sqltext       VARCHAR(1000);
  SET v_sqltext = 'UPDATE STAFF SET salary=? WHERE id=?';
  PREPARE v_stmt1 FROM v_sqltext;
  EXECUTE v_stmt1 USING p_new_salary, p_emp_id;
END

在这个存储历程中，起首准备一个 UPDATE 语句，个中搜罗两个参数标记，然后执行这个语句。因为准备语句时带有参数标记，所以从此对这个历程的挪用不会招致语句从新编译，这是因为在包缓存中可以找到这个语句。理论的参数值是在 EXECUTE 语句中经过历程 USING 子句提供的。





回页首

利用参数标记的缺陷

利用参数标记也大年夜概有缺陷。最主要的缺陷就是缺乏参数值的有关信息。当利用参数标记时，DB2 在编译该语句时不知道参数的值是什么。在执行时，它将以用户提供的值交换参数标记。假若该变量的值对付所选择的 SQL 造访阴谋具有较大年夜影响，就大年夜概会招致成果。

比喻，思量一个名为 “bigTable” 的表，个中搜罗玩具的有关信息。假定一共有 50,000 件白色玩具、5 件棕色玩具、800,000 件粉色玩具和 75,000 件绿色玩具。这个使用递次究诘从此出售的玩具数量。以下是表的定义：

CREATE TABLE toyLists (
	id       INTEGER NOT NULL,
	color    CHAR(10),
	type     CHAR(10),
	price    INTEGER,
	amount   INTEGER
)

假定这个表在 color 列上有索引。以下两个究诘分析当利用这个表时，以何种方法利用参数标记会招致很大年夜的服从差别：

SELECT price, amount
FROM toyLists
WHERE color = 'PINK'
	AND type = 'CAR'
SELECT price, amount
FROM toyLists
WHERE color = 'BROWN'
	AND type = 'CAR'

在第一个究诘中，将为颜色 'PINK' 前往 800,000 笔记载。然后基于这些粉色玩具中有若干件标准为 'CAR' 的玩具，进一步淘汰该列表。简略地扫描该表而倒霉用索引是居心义的，因为会前往如斯多的记载。

第二个示例基于颜色 'BROWN' 只前往 5 笔记载；当将其搜刮规模增加到 'CAR' 标准时，前往的记载数量大年夜概更少。最好是利用 colour 上的索引来淘汰必需为标准 'CAR' 搜刮的条款数。

如今让我们思量一下，假若利用参数标记阻止雷同的搜刮，将发作什么情况：

SELECT price, amount
FROM toyLists
WHERE color = ?
	AND type = 'CAR'

在这个示例中，DB2 不知道必需检索什么颜色。它不知道是要前往 5 条照样 300,000 笔记载。假若利用不同的颜色值，就大年夜概选择完全不同的造访阴谋。

当触及参数标记时，DB2 利用列的平均统计数据天生造访阴谋。DB2 确定每种颜色平均有若干笔记载。因为 'PINK' 非常普遍，所以每种颜色的平均记载数量会迥殊非常高，是以 DB2 大年夜概利用表扫描执行每个以颜色作为谓词的究诘。这对付过滤 'PINK' 颜色的究诘很切合，但是对付 'RED' 或 'BROWN' 颜色却很蹩脚。在后两种情况下，在究诘中倒霉用参数标记很大年夜概会更好一些，因为分外的编译时分大年夜概比因为不精确利用表扫描招致的分外措置时分要少良多。





回页首

何时应该利用参数标记

在良多情况下，参数标记可以改进使用递次的服从。应该在以下情况下搜罗参数标记：

• 执行几次并只变化其参数值的究诘。
• 其编译时分大年夜概善于执行时分的究诘。
• 短的、OLTP 标准的究诘。

但是，有一些究诘不会受益于参数标记的利用：

• 只执行一次且其参数值老是相同的究诘。
• 大年夜的、OLAP 标准的究诘，其编译时分只占总执行时分的一小局部。
• 个中变量值极大年夜地影响 SQL 造访阴谋的创建方法的究诘。
• 个中参数标记值大年夜概相互联系关系并招致 DB2 可以大年夜概辨认统计数据中的情势或数据分布（比喻 state-city 或 year-month 对）的究诘。
• 对付不均匀分布的数据集运转的究诘，因为利用参数标记大年夜概招致 DB2 优化器对付某些值纰漏紧张的统计倾向。

侥幸的是，在 DB2 9 for Linux, UNIX, and Windows 中有一些新的改进，使我们可以大年夜概影响默许的究诘优化举动，这意味着可以压迫上面所述的一些缺陷。更多信息请参考文章 “Influence query optimization with optimization profiles and statistical views in DB2 9”（参见 参考材料 一节）。




版权声明： 原创作品，许可转载，转载时请务必以超链接情势标明文章 原始来因 、作者信息和本声明。不然将清查法律责任。