高性能 SQL

简介

在过去一年半的时间里，在 标准 TPC-C 基准 方面有很多 DB2 for LUW 活动，这个基准常用于测试在线事务处理环境中的性能。其结果跨度很大，在具备通常配置的机器上，它可能是 非常小的数字，在那些家庭和工作中不多见的 特殊配置 上，又可能是非常大的数字。

虽然填充数据库模式的行的数量可能被调整到一定规模，但有一个地方仍然是相同的：SQL。在本文中，您将认识 TPC-C 基准的逻辑，理解 SQL 技术，并发现如何在实际的客户环境中使用 SQL 技术。

为了达到这个目的，作者 Serge Rielau 这样来组织这篇文章：首先介绍该基准的 DB 模式以及它的事务。接着，他将分析每个事务，并解释每个事务的 SQL 特性。到本文的最后，您应该对 TPC-C 基准以及 DB2 的高级 SQL 特性，包括它们背后的机理有更好的理解。

TPC-C: 基准简述

TPC-C 基准使用一小组事务和表来模拟一家分销企业，该企业有一个产品销售周期。每个事务的外部模式和语义都被 正式指定。为了简短地概述一下，并提供一个基本模式以供使用，下面给出了一个概要。 其中，表用 粗体标记，事务用 斜体 显示：

用于该基准的数据库主要由仓库（warehouses）、地区（districts）、产品（items）和客户（customers）组成。在每个 WAREHOUSE 中的 STOCK（库存）里有 100,000 个 ITEM。每个仓库为 10 个 DISTRICT 服务。每个地区为 3000 名 CUSTOMER 服务，这些客户 ORDER（订购）新产品。 每份 ORDER（订单）最多可以由 15 种不同产品组成，称作 ORDER_LINE（订购项）。在给订单发货之前，订单作为一个 NEW_ORDER 进行排队，仓库中每种产品的库存要进行调整……在 DELIVERY（发货）时，要向客户收费。在 PAYMENT（支付）时，该事务被存档在 HISTORY（历史）中，收入要记帐。

此外，客户可以查问他们的 ORDER STATUS（订单状态），仓库管理员可以查问订单上所订产品的 STOCK LEVEL（库存水平）。

为了更切合实际，客户容易忘记他们的客户 ID，因此必须能够按用户的姓氏（last name）查找客户 ID，但客户的姓可能有重复现象。而且，本地的仓库可能无法满足所有的订单，这要求从远处的仓库发货过来。

一个仓库所能处理的新订单的数量限制在每分钟 9 到 12.86 个事务之间。这意味着，为了达到 320 万 TpmC （NEW ORDER Transactions per minute TPC-C），至少需要借助 256,000 多个仓库。这意味着有 77 亿 注册客户。因此，除非您有足够的磁盘和一个大的保险丝，否则不要试。

在介绍 5 个事务之前，先看看各个表的 DDL。这些 DDL 都很明了，无需解释。

清单 1. 创建 TPC-C 数据库表的 DDL

1 CREATE TABLE WAREHOUSE 2 ( 3 W_NAME CHAR(10) NOT NULL, 4 W_STREET_1 CHAR(20) NOT NULL, 5 W_STREET_2 CHAR(20) NOT NULL, 6 W_CITY CHAR(20) NOT NULL, 7 W_STATE CHAR(2) NOT NULL, 8 W_ZIP CHAR(9) NOT NULL, 9 W_TAX INTEGER NOT NULL, 10 W_YTD BIGINT NOT NULL, 11 W_ID INTEGER NOT NULL, 12 PRIMARY KEY (W_ID) 13 ); 14 15 CREATE TABLE DISTRICT 16 ( 17 D_NEXT_O_ID INTEGER NOT NULL, 18 D_TAX INTEGER NOT NULL, 19 D_YTD BIGINT NOT NULL, 20 D_NAME CHAR(10) NOT NULL, 21 D_STREET_1 CHAR(20) NOT NULL, 22 D_STREET_2 CHAR(20) NOT NULL, 23 D_CITY CHAR(20) NOT NULL, 24 D_STATE CHAR(2) NOT NULL, 25 D_ZIP CHAR(9) NOT NULL, 26 D_ID SMALLINT NOT NULL, 27 D_W_ID INTEGER NOT NULL, 28 PRIMARY KEY (D_ID, D_W_ID) 29 ); 30 31 CREATE TABLE ITEM 32 ( 33 I_NAME CHAR(24) NOT NULL, 34 I_PRICE INTEGER NOT NULL, 35 I_DATA VARCHAR(50) NOT NULL, 36 I_IM_ID INTEGER NOT NULL, 37 I_ID INTEGER NOT NULL, 38 PRIMARY KEY (I_ID) 39 ); 40 41 CREATE TABLE STOCK 42 ( 43 S_REMOTE_CNT INTEGER NOT NULL, 44 S_QUANTITY INTEGER NOT NULL, 45 S_ORDER_CNT INTEGER NOT NULL, 46 S_YTD INTEGER NOT NULL, 47 S_DATA VARCHAR(50) NOT NULL, 48 S_DIST_01 CHAR(24) NOT NULL, 49 S_DIST_02 CHAR(24) NOT NULL, 50 S_DIST_03 CHAR(24) NOT NULL, 51 S_DIST_04 CHAR(24) NOT NULL, 52 S_DIST_05 CHAR(24) NOT NULL, 53 S_DIST_06 CHAR(24) NOT NULL, 54 S_DIST_07 CHAR(24) NOT NULL, 55 S_DIST_08 CHAR(24) NOT NULL, 56 S_DIST_09 CHAR(24) NOT NULL, 57 S_DIST_10 CHAR(24) NOT NULL, 58 S_I_ID INTEGER NOT NULL, 59 S_W_ID INTEGER NOT NULL, 60 PRIMARY KEY (S_I_ID, S_W_ID) 61 ); 62 63 CREATE TABLE CUSTOMER 64 ( 65 C_ID INTEGER NOT NULL, 66 C_STATE CHAR(2) NOT NULL, 67 C_ZIP CHAR(9) NOT NULL, 68 C_PHONE CHAR(16) NOT NULL, 69 C_SINCE BIGINT NOT NULL, 70 C_CREDIT_LIM BIGINT NOT NULL, 71 C_MIDDLE CHAR(2) NOT NULL, 72 C_CREDIT CHAR(2) NOT NULL, 73 C_DISCOUNT INTEGER NOT NULL, 74 C_DATA VARCHAR(500) NOT NULL, 75 C_LAST VARCHAR(16) NOT NULL, 76 C_FIRST VARCHAR(16) NOT NULL, 77 C_STREET_1 VARCHAR(20) NOT NULL, 78 C_STREET_2 VARCHAR(20) NOT NULL, 79 C_CITY VARCHAR(20) NOT NULL, 80 C_D_ID SMALLINT NOT NULL, 81 C_W_ID INTEGER NOT NULL, 82 C_DELIVERY_CNT INTEGER NOT NULL, 83 C_BALANCE BIGINT NOT NULL, 84 C_YTD_PAYMENT BIGINT NOT NULL, 85 C_PAYMENT_CNT INTEGER NOT NULL, 86 PRIMARY KEY (C_ID, C_D_ID, C_W_ID) 87 ); 88 CREATE INDEX CUST_IDXB 89 ON CUSTOMER (C_LAST, C_W_ID, C_D_ID, C_FIRST, C_ID); 90 91 CREATE TABLE HISTORY 92 ( 93 H_C_ID INTEGER NOT NULL, 94 H_C_D_ID SMALLINT NOT NULL, 95 H_C_W_ID INTEGER NOT NULL, 96 H_D_ID SMALLINT NOT NULL, 97 H_W_ID INTEGER NOT NULL, 98 H_DATE BIGINT NOT NULL, 99 H_AMOUNT INTEGER NOT NULL, 100 H_DATA CHAR(24) NOT NULL 101 ); 102 103 CREATE TABLE ORDERS 104 ( 105 O_C_ID INTEGER NOT NULL, 106 O_ENTRY_D BIGINT NOT NULL, 107 O_CARRIER_ID SMALLINT NOT NULL, 108 O_OL_CNT SMALLINT NOT NULL, 109 O_ALL_LOCAL SMALLINT NOT NULL, 110 O_ID INTEGER NOT NULL, 111 O_W_ID INTEGER NOT NULL, 112 O_D_ID SMALLINT NOT NULL, 113 PRIMARY KEY (O_ID, O_W_ID, O_D_ID) 114 ); 115 CREATE INDEX ORDR_IDXB 116 ON ORDERS (O_C_ID, O_W_ID, O_D_ID, O_ID DESC); 117 118 CREATE TABLE ORDER_LINE 119 ( 120 OL_DELIVERY_D BIGINT NOT NULL, 121 OL_AMOUNT INTEGER NOT NULL, 122 OL_I_ID INTEGER NOT NULL, 123 OL_SUPPLY_W_ID INTEGER NOT NULL, 124 OL_QUANTITY SMALLINT NOT NULL, 125 OL_DIST_INFO CHAR(24) NOT NULL, 126 OL_O_ID INTEGER NOT NULL, 127 OL_D_ID SMALLINT NOT NULL, 128 OL_W_ID INTEGER NOT NULL, 129 OL_NUMBER SMALLINT NOT NULL, 130 PRIMARY KEY (OL_O_ID, OL_W_ID, OL_D_ID, OL_NUMBER) 131 ); 132 133 CREATE TABLE NEW_ORDER 134 ( 135 NO_O_ID INTEGER NOT NULL, 136 NO_D_ID SMALLINT NOT NULL, 137 NO_W_ID INTEGER NOT NULL, 138 PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID) 139 );

NEW ORDER 事务（本地）

NEW ORDER 事务处理来自一个客户的新订单。这里需要做各种不同的事情：

1. 获取下一个订单 id 和客户所在地区的地区税率。
2. 增加客户所在地区的下一个订单 id，以便用于随后的事务。
3. 对于客户订购的每种产品：
   1. 从 ITEM 表检索产品的名称、价格和描述。
   2. 从 STOCK 表检索该产品的地区信息和剩下的库存水平。
   3. 将该产品的库存水平减去订购的数量。如果库存低于阈值，则应该订购货物（通过简单地增加库存值来实现）。
   4. 将获得的相关数据，包括总价格，插入到 ORDER_LINE 中。
4. 将订单插入 ORDERS 和 NEW_ORDER 表。
5. 从 CUSTOMER 检索客户名、折扣、信用信息。
6. 从 WAREHOUSE 检索销售税。
7. 考虑折扣和税，计算总价格。

这是很大的工作量。不过，DB2 使用 SQL 语句来处理所有这些工作。其原理如下：

首先，DB2 处理 DISTRICT 表。这里需要返回数据，并执行更新。经验告诉我们，这需要 2 条 SQL 语句，并且 UPDATE 应该在 SELECT 语句之前执行；否则，当并发增多时，可能会发生死锁。

但是，DB2 支持一种新的 SQL 特性，这种特性正处在标准化的过程中。该特性允许访问触发器中所谓的 过渡表（transition table）。 OLD TABLE 过渡表保存了受影响的行在被 UPDATE 或 DELETE 语句处理之前的初始状态。 NEW TABLE 过渡表保存处理了 INSERT 或 UPDATE 语句之后受影响的行。这是 AFTER 触发器被激发之前的状态。懂得 Microsoft 或 Sybase 的用户可能知道这两个表，在 Microsoft 或 Sybase 中，这两个表的表名是 DELETED 和 INSERTED。

DB2 所做的就是允许 UPDATE、DELETE 和 INSERT 出现在选择语句的 FROM 子句中，并允许用户选择应该从哪个过渡表进行选择：

清单 2. 使用过渡表

1 SELECT D_TAX, D_NEXT_O_ID 2 INTO :dist_tax , :next_o_id 3 FROM OLD TABLE ( UPDATE DISTRICT 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1 5 WHERE D_W_ID = :w_id 6 AND D_ID = :d_id 7 ) AS OT

通过查看 清单 3 中所示的优化器计划，可以很容易看出这种逻辑的优点：

清单 3. 使用过渡表的访问计划

Rows RETURN ( 1) Cost I/O | 1 UPDATE ( 2) 25.7261 2 /---+--\ 1 26 FETCH TABLE: SRIELAU ( 3) DISTRICT 12.872 1 /----+---\ 1 26 IXSCAN TABLE: SRIELAU ( 4) DISTRICT 0.0175755 0 | 26 INDEX: SYSIBM SQL0410231029415

这个组合的计划在结构上与单独的 UPDATE 语句几乎一样。TPC-C 规范将下一个订单 id 的存储放在 DISTRICT 表中。在客户环境中，可以很容易地转而使用一个 SEQUENCE 事务，以避免锁在一起。

现在，看看这三条 SQL 语句中的第二条 SQL 语句：

清单 4. 第二条 SQL 语句

1 WITH DATA AS ( SELECT O_ID , D_ID , W_ID , OL_NUMBER , I_ID 2 , W_ID AS I_SUPPLY_W_ID 3 , 0 AS OL_DELIVERY_D 4 , I_QTY 5 , ( I_PRICE * I_QTY ) AS TOTAL_PRICE 6 , OL_DIST_INFO , I_PRICE, I_NAME, I_DATA, S_DATA 7 , S_QUANTITY 8 FROM ( SELECT :next_o_id as O_ID 9 , :w_id AS W_ID 10 , :d_id as D_ID 11 , OL_NUMBER , I_ID , I_QTY 12 FROM TABLE( VALUES 13 ( 1 , :id0 , :ol_quantity0 ) 14 , ( 2 , :id1 , :ol_quantity1 ) 15 , ( 3 , :id2 , :ol_quantity2 ) 16 , ( 4 , :id3 , :ol_quantity3 ) 17 , ( 5 , :id4 , :ol_quantity4 ) 18 , ( 6 , :id5 , :ol_quantity5 ) 19 , ( 7 , :id6 , :ol_quantity6 ) 20 , ( 8 , :id7 , :ol_quantity7 ) 21 , ( 9 , :id8 , :ol_quantity8 ) 22 , ( 10 , :id9 , :ol_quantity9 ) 23 , ( 11 , :id10 , :ol_quantity10 ) 24 , ( 12 , :id11 , :ol_quantity11 ) 25 , ( 13 , :id12 , :ol_quantity12 ) 26 , ( 14 , :id13 , :ol_quantity13 ) 27 , ( 15 , :id14 , :ol_quantity14 ) 28 ) AS X ( OL_NUMBER , I_ID , I_QTY ) 29 ) AS ITEMLIST 30 , TABLE( NEW_OL_LOCAL( I_ID , I_QTY , W_ID 31 , O_ID , D_ID , SMALLINT(OL_NUMBER) 32 ) 33 ) AS NEW_OL_LOCAL 34 WHERE NEW_OL_LOCAL.I_PRICE IS NOT NULL 33 ) 36 SELECT I_PRICE , I_NAME , I_DATA , OL_DIST_INFO , S_DATA , S_QUANTITY 37 FROM NEW TABLE ( INSERT INTO ORDER_LINE 38 ( OL_O_ID , OL_D_ID , OL_W_ID 39 , OL_NUMBER , OL_I_ID , OL_SUPPLY_W_ID 40 , OL_DELIVERY_D , OL_QUANTITY , OL_AMOUNT 41 , OL_DIST_INFO 42 ) 43 INCLUDE ( I_PRICE INTEGER 44 , I_NAME CHAR(24) 45 , I_DATA VARCHAR(50) 46 , S_DATA VARCHAR(50) 47 , S_QUANTITY SMALLINT ) 48 SELECT O_ID , D_ID , W_ID 49 , OL_NUMBER , I_ID , I_SUPPLY_W_ID 60 , OL_DELIVERY_D , I_QTY , TOTAL_PRICE 61 , OL_DIST_INFO , I_PRICE , I_NAME 62 , I_DATA , S_DATA , S_QUANTITY 63 FROM DATA 64 ) AS INS

这是一条 重量级 SQL 语句。接下来我们逐步来分析这个查询：

1. ITEMLIST 由输入参数组合而成。它包括产品以及每种产品的数量，这是在一个 VALUES 子句中收集到的。对于列表中的所有产品，订单 id、地区和仓库是不变的。
2. 通过使用一个关联连接（correlated join）， NEW_OL_LOCAL SQL 表函数逐个处理列表中的每种产品。很快您就可以看到，该函数返回产品的价格、某些元数据和库存信息。
3. 对于不能找到的产品（价格为 NULL），可以将其过滤掉。将产生的表称作 DATA。
4. 将 DATA 中的每种产品插入到 ORDER_LINE 中。
5. 将每种产品的价格、名称、元数据和库存信息返回给用户。

这里有两件有趣的事情。

首先， NEW_OL _LOCAL 表函数实际上一次只返回一行。由于这一层关系，它操作起来更像是一个 用户定义的关系操作符。

另外，正如您后面将会看到的， NEW_OL _LOCAL 表函数实际上是写一个表。这里您看到的是一个连接，内表在其中执行数据库修改。为允许这一点，并使数据库保持一致的状态，DB2 需要确信连接的内表和外表没有冲突；或者，DB2 需要外加一个称作 dam 的临时表，以确保在这个表函数开始处理之前，连接的外表被完全导出。由于这个 SQL 表函数是以 内联 SQL PL 编写的，这是 DB2 用于 SQL PL 的宏语言，因此 DB2 可以看穿这个函数，并得出结论，认为该事务在行为上没有问题，这种情况下不需要 dam。

提到连接的内表和外表之间各种可能的冲突之后，还应该提到的是，在这个函数与 INSERT 操作之间也可能发生类似的问题。为了避免太多的复杂性，DB2 强加了一条简单的规则：除非嵌入在相关的函数里面，否则 UPDATE、DELETE 或 INSERT （ 数据更改操作）不应该出现在连接中。而且， 数据更改操作 也不应该出现在嵌套查询中。

相反， 数据更改操作 应该限制在公共表表达式（CTE，也叫做“WITH 子句”）的顶层 SELECT 或最上面的 SELECT 中。在上述例子中，整个过程是这样的： DATA 执行一个 数据更改操作，并作为 CTE 放在 WITH 子句中。 现在它可以为 INSERT 操作提供数据了。

为什么要有这些规则？让 数据更改操作 留在 WITH 子句中，实际上便强加了一个非常自然的顺序，这样可用于解决冲突。

最后，在这个事务中还暴露了有关 数据更改操作 的另一个更新颖的特性： 那就是 INCLUDE-子句，像任何其他 数据更改语句 一样，INSERT 可以使用这个子句。这个子句允许通过附加额外的列来 扩展 OLD TABLE 或 NEW TABLE 过渡表。这些列只是通过 INSERT 操作搭载的，并且上面的 SELECT 可以访问这些列。在这个例子中，产品价格、名称、数据以及有关库存的数据不是 ORDER_LINE 表的一部分。这些列只是暂时传递、用以返回给用户的。

为什么会这么混乱呢？为什么不从 DATA 选择两次 —— 一次用于 INSERT，一次用于最后的 SELECT？ 答案很简单：就像 SORT 一样，TEMP 是一个“4 个字母的单词”。

如果没有看 NEW_OL_LOCAL 表函数，那么对 NEW ORDER 事务的探索就不算完：

清单 5. NEW_OL_LOCAL 表函数

1 CREATE FUNCTION NEW_OL_LOCAL( I_ID INTEGER 2 , I_QTY SMALLINT 3 , W_ID INTEGER 4 , O_ID INTEGER 5 , D_ID SMALLINT 6 , OL_NUMBER SMALLINT 7 ) 8 RETURNS TABLE( I_PRICE INTEGER 9 , I_NAME CHAR(24) 0 , I_DATA VARCHAR(50) 11 , OL_DIST_INFO CHAR(24) 12 , S_DATA VARCHAR(50) 13 , S_QUANTITY SMALLINT 14 ) 15 SPECIFIC NEW_OL_LOCAL 16 MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL 17 18 VAR: BEGIN ATOMIC 19 DECLARE I_PRICE INTEGER ; 20 DECLARE I_NAME CHAR(24) ; 21 DECLARE I_DATA VARCHAR(50) ; 22 DECLARE OL_DIST_INFO CHAR(24) ; 23 DECLARE S_DATA VARCHAR(50) ; 24 DECLARE S_QUANTITY SMALLINT ; 25 26 SET ( I_PRICE , I_NAME , I_DATA ) 27 = ( SELECT 28 I_PRICE 29 , I_NAME 30 , I_DATA 31 32 FROM ITEM 33 WHERE ITEM.I_ID = NEW_OL_LOCAL.I_ID 34 ) 35 ; 36 SET ( OL_DIST_INFO , S_DATA , S_QUANTITY ) 37 = ( SELECT OL_DIST_INFO 38 , S_DATA 39 , S_QUANTITY 40 FROM NEW TABLE ( UPDATE STOCK 41 INCLUDE ( OL_DIST_INFO CHAR( 24 ) ) 42 SET S_QUANTITY = CASE WHEN S_QUANTITY - NEW_OL_LOCAL.I_QTY >= 10 43 THEN S_QUANTITY - NEW_OL_LOCAL.I_QTY 44 ELSE S_QUANTITY - NEW_OL_LOCAL.I_QTY + 91 45 END 46 , S_ORDER_CNT = S_ORDER_CNT + 1 47 , S_YTD = S_YTD + NEW_OL_LOCAL.I_QTY 48 , OL_DIST_INFO = CASE D_ID WHEN 1 THEN S_DIST_01 49 WHEN 2 THEN S_DIST_02 50 WHEN 3 THEN S_DIST_03 51 WHEN 4 THEN S_DIST_04 52 WHEN 5 THEN S_DIST_05 53 WHEN 6 THEN S_DIST_06 54 WHEN 7 THEN S_DIST_07 55 WHEN 8 THEN S_DIST_08 56 WHEN 9 THEN S_DIST_09 57 WHEN 10 THEN S_DIST_10 58 END 59 WHERE S_I_ID = NEW_OL_LOCAL.I_ID 60 AND S_W_ID = NEW_OL_LOCAL.W_ID 61 ) AS U 62 ) 63 ; 64 RETURN VALUES( VAR.I_PRICE 65 , VAR.I_NAME 66 , VAR.I_DATA 67 , VAR.OL_DIST_INFO 68 , VAR.S_DATA 69 , VAR.S_QUANTITY 70 ) 71 ; 72 END

这个函数实现上述步骤中的步骤 2。该函数为一个订购项（order line）检索产品信息，并执行必要的库存更新。注意，这个函数被定义为 MODIFIES SQL DATA。这个子句允许 SQL 表函数包含 UPDATE、DELETE、INSERT 和 MERGE 语句。还应注意，这里使用了 INCLUDE 子句，这一次是为了放弃 OL_DIST_INFO 列。这个列来自所存储产品的特定地区信息。最后，注意在 RETURN 语句中使用了单行的 VALUES 子句，以便将最后的结果以单行的表的形式返回。

为了从这个复杂的 SQL 语句中赚取性能，DB2 选择一个非常精妙的查询计划：

清单 6. 第二条 SQL 语句的访问计划

Rows RETURN ( 1) Cost I/O | 14.4 TBSCAN ( 2) 26.2997 2.02765 | 14.4 SORT ( 3) 26.2978 2.02765 | 14.4 INSERT ( 4) 26.2922 2.02765 /---+---\ 14.4 44 FILTER TABLE: SRIELAU ( 5) ORDER_LINE 13.4359 1.02765 | 15 NLJOIN ( 6) 13.4334 1.02765 /----------+---------\ 15 1 TBSCAN NLJOIN ( 7) ( 8) 0.000201927 13.2461 0 1.02765 | /----------+----------\ 15 1 1 TABFNC: SYSIBM NLJOIN TBSCAN GENROW ( 9) ( 18) 13.246 4.48727e-005 1.02765 0 /----------+---------\ | 1 1 1 TBSCAN UNION TABFNC: SYSIBM ( 10) ( 11) GENROW 4.48727e-005 13.2457 0 1.02765 | /----------+----------\ 1 0.96 1 TABFNC: SYSIBM FETCH UPDATE GENROW ( 13) ( 15) 12.8727 0.371624 1 0.027648 /----+---\ /---+---\ 1 36 0.013824 9 IXSCAN TABLE: SRIELAU FETCH TABLE: SRIELAU ( 14) ITEM ( 16) STOCK 0.0182935 0.193765 0 0.013824 | /----+---\ 36 0.013824 9 INDEX: SYSIBM IXSCAN TABLE: SRIELAU SQL0410231029418 ( 17) STOCK 0.0157303 0 | 9 INDEX: SYSIBM SQL0410231029421

这是一个好计划 —— 但是它与前面的查询有什么关系呢？下面对该计划中的各项作一番介绍：

• FETCH(13) 表示函数中的第一条 SET 语句，它从 ITEM 表中进行选择。
• UPDATE(15) 显然是 STOCK 表的 UPDATE，包括将其派生出来的 SET 语句。
• UNION(11) 不如它所声明的那么完整。在 内联 SQL PL 的上下文中，UNION 用于以一种连续的方式派生出语句。因此，这个 UNION 就相当于函数体 BEGIN ATOMIC...END。
• TBSCAN(10) 初始化函数中的本地变量。这是 DECLARE 语句链。 FETCH(13) 和 UPDATE(15) 实际上是读取 NLJOIN(9) 的外表的值，并赋上新值，覆盖本地变量的默认值。
• TBSCAN(18) 表示这个 SQL 表函数中的 RETURN 语句。
• 我们看一下 NLJOIN(8)，它表示整个函数，从中可以看到一个按三种方法排序的连接：
  1. 初始化本地变量。
  2. 执行函数体。
  3. 返回结果表。
• TBSCAN(7) 是该连接的外表，它为函数提供产品列表。
• FILTER(5) 抛弃 I_PRICE IS NULL 的不合适的产品。
• INSERT(4) 显然是到 ORDER_LINE 的 INSERT。
• SORT(3) 有点特殊。因为这个查询是一个游标，因此 DB2 正面临一个窘境。该查询通过更新 STOCK 和 ORDER_LINE，而更改数据库的状态。然而，我们无法保证用户将真正取表中的所有行，从而使这些更改通过。因此，DB2 需要 dam 结果集，以确保当游标被打开时整个查询已经完成。出于性能的原因，这里为常规临时表上的 dam 选择一个没有排序键的 SORT。这些 常用的（do-at-open） 语义实际上有一个非常好的副作用。通过使用 cursor with hold，可以执行复杂的数据库操作，并在打开后提交这些操作。然后就可以直接读取和分析结果集，而不必持有任何锁。

对于那些对内联 SQL PL 的内部原理真正感兴趣的读者，可以在本文 参考资料 一节中给出的参考资料中发现更详细的信息。

NEW_ORDER 事务中的第三条语句，也即最后一条语句是：

清单 7. 第三条 SQL 语句

1 SELECT W_TAX, C_DISCOUNT, C_LAST, C_CREDIT 2 INTO :ware_tax, :c_discount, :c_last, :c_credit 3 FROM TABLE ( NEW_WH ( :next_o_id 4 , :w_id 5 , :d_id 6 , :c_id 7 , :o_entry_d 8 , :inputItemCount 9 , :allLocal 10 ) 11 ) AS NEW_WH_TABLE

清单 8. NEW_WH 函数

1 CREATE FUNCTION NEW_WH ( O_ID INTEGER 2 , W_ID INTEGER 3 , D_ID SMALLINT 4 , C_ID INTEGER 5 , O_ENTRY_D BIGINT 6 , O_OL_CNT SMALLINT 7 , O_ALL_LOCAL SMALLINT 8 ) 9 RETURNS TABLE ( W_TAX INTEGER 10 , C_DISCOUNT INTEGER 11 , C_LAST VARCHAR(16) 12 , C_CREDIT CHAR(2) 13 ) 14 SPECIFIC NEW_WH 15 MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL 16 VAR: BEGIN ATOMIC 17 DECLARE C_DISCOUNT INTEGER ; 18 DECLARE C_LAST VARCHAR(16) ; 19 DECLARE C_CREDIT CHAR(2) ; 20 DECLARE W_TAX INTEGER ; 21 22 INSERT 23 INTO NEW_ORDER ( NO_O_ID, NO_D_ID, NO_W_ID ) 24 VALUES ( O_ID 25 , D_ID 26 , W_ID 27 ) 28 ; 29 INSERT 30 INTO ORDERS ( O_C_ID , O_ENTRY_D , O_CARRIER_ID , O_OL_CNT 31 , O_ALL_LOCAL , O_ID , O_W_ID , O_D_ID ) 32 VALUES ( C_ID , O_ENTRY_D , 0 , O_OL_CNT 33 , O_ALL_LOCAL , O_ID , W_ID , D_ID ) 34 ; 35 SET ( C_DISCOUNT, C_LAST, C_CREDIT ) 36 = ( SELECT C_DISCOUNT, C_LAST, C_CREDIT 37 FROM CUSTOMER 38 WHERE C_ID = NEW_WH.C_ID 39 AND C_W_ID = W_ID 40 AND C_D_ID = D_ID 41 ) 42 ; 43 SET W_TAX 44 = ( SELECT W_TAX 45 FROM WAREHOUSE 46 WHERE W_ID = NEW_WH.W_ID 47 ) 48 ; 49 RETURN VALUES ( W_TAX , C_DISCOUNT , C_LAST , C_CREDIT ) ; 50 END

您可以看到， NEW_WH 函数中的语句相互之间是独立的。那么，为什么还要这么麻烦地将这些语句封装成一个函数呢？将这四条语句合并在一起的原因是，减少与 SQL 语句的调用相关的代码路径（codepath）。在一个常规的客户环境中，能够进行封装而又于性能无损，这种能力是很好的。DB2 在这里证明，好的编码风格和基准程序不一定要不一致。

相应的计划如下：

清单 9. 第三条 SQL 语句的访问计划

在这里可以看到 UNION 如何派生出这四条语句。还应注意的是，在顶部没有 SORT，因为该语句是一种 SELECT INTO，而不是游标。

本文将不谈论处理远程仓库的 NEW_ORDER 事务。这种事务的结构与这里所述的事务很类似，因此没有什么新东西需要了解。

DELIVERY 事务

DELIVERY 是一个非常简单的事务：

1. 送货者从 NEW_ORDER 队列中找到时间最久的订单 id。
2. 将该订单从队列中删除。
3. 从存储在 ORDER 中的订单检索客户 id。
4. 从 ORDER_LINE 计算应付总额。注意，在正常的情况下，总数决不会存储在订单本身当中。
5. 通过设置送货日期，将订购项标记为已发货。
6. 通过设置送货日期，将订单标记为已发货。
7. 更新客户的结余和发货计数。
8. 返回订单 id。

如果队列为空，则什么也不做，只返回 NULL。

清单 10. Delivery 表函数

1 CREATE FUNCTION DEL( W_ID INTEGER 2 , D_ID SMALLINT 3 , CARRIER_ID SMALLINT 4 , DELIVERY_D BIGINT 5 ) 6 RETURNS TABLE ( O_ID INTEGER ) 7 SPECIFIC DELIVERY 8 MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL 9 VAR: BEGIN ATOMIC 10 DECLARE O_ID INTEGER ; 11 DECLARE C_ID INTEGER ; 12 DECLARE AMOUNT INTEGER ; 13 14 /* Delete the order from new order table */ 15 SET VAR.O_ID = ( SELECT NO_O_ID 16 FROM OLD TABLE ( DELETE 17 FROM ( SELECT NO_O_ID 18 FROM NEW_ORDER 19 WHERE NO_W_ID = DEL.W_ID 20 AND NO_D_ID = DEL.D_ID 21 ORDER BY NO_O_ID ASC 22 FETCH FIRST 1 ROW ONLY 23 ) AS NEW_ORDER 24 ) AS D 25 ) 26 ; 27 /* Update the order as delivered and retrieve the customer id */ 28 SET VAR.C_ID = ( SELECT O_C_ID 29 FROM OLD TABLE ( UPDATE ORDERS 30 SET O_CARRIER_ID = DEL.CARRIER_ID 31 WHERE O_W_ID = DEL.W_ID 32 AND O_D_ID = DEL.D_ID 33 AND O_ID = VAR.O_ID 34 ) AS U 35 ) 36 ; 37 SET VAR.AMOUNT = ( SELECT SUM( OL_AMOUNT ) 38 FROM OLD TABLE ( UPDATE ORDER_LINE 39 SET OL_DELIVERY_D = DEL.DELIVERY_D 40 WHERE OL_W_ID = DEL.W_ID 41 AND OL_D_ID = DEL.D_ID 42 AND OL_O_ID = VAR.O_ID 43 ) AS U 44 ) 45 ; 46 /* Charge the customer */ 47 UPDATE CUSTOMER 48 SET C_BALANCE = C_BALANCE + VAR.AMOUNT 49 , C_DELIVERY_CNT = C_DELIVERY_CNT + 1 50 WHERE C_W_ID = DEL.W_ID 51 AND C_D_ID = DEL.D_ID 52 AND C_ID = VAR.C_ID 53 ; 54 /* Return the order id to the caller (or NULL) */ 55 RETURN VALUES VAR.O_ID ; 56 END

就像在 NEW_ORDER 事务中一样，所有的步骤被收集到一个 SQL 表函数中，以节省代码路径。这里执行的没有 8 个步骤，而只有 5 条子语句。怎么回事？

1. 在 SQL 中，处理队列上一个元素的传统方法是在第一步取这个元素。然后，在第二步中，从表中删除这个元素。这种方法的主要问题在于，除非打开 cursor-for-update，否则直接进行 select into 时，将不能充分地锁定这一行以便阻止另一个送货者尝试为相同的订单送货。而且，不管是否为游标，都需要执行两条语句。同样，将 数据更新操作（在这里是 DELETE）放入到 FROM 子句中被证明是有用的。然而，还需要更多的东西。为了发现“时间最久的”订单，必须执行一个 MIN() 函数。这个函数不能出现在 DELETE 语句的 WHERE 子句中，而只能出现在查询中。DB2 的 SQL 语言是高度正交的，这一事实有助于以一种优雅的方式解决问题。首先，DB2 允许对查询的 DELETE 具有等同于对视图（实际上也是查询）的 DELETE 的语义。其次，DB2 允许对一个已排序的查询进行 DELETE。在前面相当优雅的 DELETE 操作中，“ORDER BY O_ID FETCH FIRST ROW ONLY”将提供时间最久的订单 id 结果。
2. 检索客户 id 和更新订单被合成一条语句。
3. 通过更新 ORDER_LINES 中的 delivery 列，从各个订购项计算订单总值被合在一起。同样，由于 DB2 对 SQL 语言的选择的正交性，这样是可行的。因为 UPDATE 在 FROM 子句中，所以很容易使用标准 SUM() 来聚集最终结果。

而执行发货的真正语句却十分简单：

清单 11. 执行发货的 SQL 语句

1 SELECT O_ID 2 INTO :no_o_id :no_o_id_indicator 3 FROM TABLE ( DEL( :w_id , :d_id , :o_carrier_id , :ol_delivery_d ) ) AS T

同样，下面的计划展示了由于对 SQL 语句的有效使用，这个事务变得多么地紧凑：

清单 12. Delivery 访问计划

同样，我们快速看一下这个计划的关键特性：

• DELETE(6) 是来自队列的 POP。DB2 承认它只需一个 index fetch。 当然，如果有一个 SORT，这条语句也能工作，但那将是一个不好的计划。
• GRPBY(11) 在 UPDATE(12) 之上计算 SUM(OL_AMOUNT)。

到目前为止，我们一直假设，作为一名勤恳的读者，应该对所有其他操作都是熟悉的。

PAYMENT 事务

PAYMENT 事务有两种版本。对于那些提供了客户 id 的客户，使用第一种版本。对于不记得客户 ID，而只提供了姓氏的客户，使用第二种版本。这里只讨论第二种版本，因为其中提出了第一种版本中所没有的挑战。

在支付事务（按姓氏）中，必须发生以下步骤：

1. 检索地区的名称和地址。
2. 根据姓氏发现客户的客户 id。如果在该地区有多个同姓的客户，则正确的客户应该是根据客户的名字得来的“中间”客户。
3. 检索客户的个人信息。
4. 增加该地区至今为止的收入。
5. 增加仓库至今为止的收入。
6. 增加客户的支付额，如果客户信用不佳，则还应包括额外的数据。
7. 将这次的支付记录到历史中。

与前面的事务一样，这里的大部分逻辑被封装到一个名为 PAY_C_LAST() 的表函数中。

清单 13. 表函数 PAY_C_LAST

1 CREATE FUNCTION PAY_C_LAST( W_ID INTEGER 2 , D_ID SMALLINT 3 , C_W_ID INTEGER 4 , C_D_ID SMALLINT 5 , C_LAST VARCHAR(16) 6 , H_DATE BIGINT 7 , H_AMOUNT BIGINT 8 , BAD_CREDIT_PREFIX VARCHAR(34) 9 ) 10 RETURNS TABLE( W_STREET_1 CHAR(20) 11 , W_STREET_2 CHAR(20) 12 , W_CITY CHAR(20) 13 , W_STATE CHAR(2) 14 , W_ZIP CHAR(9) 15 , D_STREET_1 CHAR(20) 16 , D_STREET_2 CHAR(20) 17 , D_CITY CHAR(20) 11 , D_STATE CHAR(2) 19 , D_ZIP CHAR(9) 20 , C_ID INTEGER 21 , C_FIRST VARCHAR(16) 22 , C_MIDDLE CHAR(2) 23 , C_STREET_1 VARCHAR(20) 24 , C_STREET_2 VARCHAR(20) 25 , C_CITY VARCHAR(20) 26 , C_STATE CHAR(2) 27 , C_ZIP CHAR(9) 28 , C_PHONE CHAR(16) 29 , C_SINCE BIGINT 30 , C_CREDIT CHAR(2) 31 , C_CREDIT_LIM BIGINT 32 , C_DISCOUNT INTEGER 33 , C_BALANCE BIGINT 34 , C_DATA CHAR(200) 35 ) 36 SPECIFIC PAY_C_ID INHERIT ISOLATION LEVEL WITH LOCK REQUEST 37 MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL 38 VAR: BEGIN ATOMIC 39 DECLARE W_NAME CHAR(10) ; 40 DECLARE D_NAME CHAR(10) ; 41 DECLARE W_STREET_1 CHAR(20) ; 42 DECLARE W_STREET_2 CHAR(20) ; 43 DECLARE W_CITY CHAR(20) ; 44 DECLARE W_STATE CHAR(2) ; 45 DECLARE W_ZIP CHAR(9) ; 46 DECLARE D_STREET_1 CHAR(20) ; 47 DECLARE D_STREET_2 CHAR(20) ; 48 DECLARE D_CITY CHAR(20) ; 49 DECLARE D_STATE CHAR(2) ; 50 DECLARE D_ZIP CHAR(9) ; 51 DECLARE C_ID INTEGER ; 52 DECLARE C_FIRST VARCHAR(16) ; 53 DECLARE C_MIDDLE CHAR(2) ; 54 DECLARE C_STREET_1 VARCHAR(20) ; 55 DECLARE C_STREET_2 VARCHAR(20) ; 56 DECLARE C_CITY VARCHAR(20) ; 57 DECLARE C_STATE CHAR(2) ; 58 DECLARE C_ZIP CHAR(9) ; 59 DECLARE C_PHONE CHAR(16) ; 60 DECLARE C_SINCE BIGINT ; 61 DECLARE C_CREDIT CHAR(2) ; 62 DECLARE C_CREDIT_LIM BIGINT ; 63 DECLARE C_DISCOUNT INTEGER ; 64 DECLARE C_BALANCE BIGINT ; 65 DECLARE C_DATA CHAR(200) ; 66 67 /* Update District and retrieve its data */ 68 SET ( D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP ) 69 = ( SELECT D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP 70 FROM OLD TABLE ( UPDATE DISTRICT 71 SET D_YTD = D_YTD + PAY_C_ID.H_AMOUNT 72 WHERE D_W_ID = PAY_C_ID.W_ID 73 AND D_ID = PAY_C_ID.D_ID 74 ) AS U 75 ) 76 ; 77 /* Determine the C_ID */ 78 SET ( C_ID ) 79 = ( SELECT C_ID 80 FROM ( SELECT C_ID 81 , COUNT(*) OVER() AS COUNT 82 , ROWNUMBER() OVER ( ORDER BY C_FIRST) AS NUM 83 FROM CUSTOMER 84 WHERE C_LAST = PAY_C_LAST.C_LAST 85 AND C_W_ID = PAY_C_LAST.C_W_ID 86 AND C_D_ID = PAY_C_LAST.C_D_ID 87 ) AS T 88 WHERE NUM = (COUNT + 1) / 2 89 ) 90 ; 91 /* Update the customer */ 92 SET ( C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2 93 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM 94 , C_DISCOUNT, C_BALANCE, C_DATA ) 95 = ( SELECT C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2 96 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT 97 , C_CREDIT_LIM , C_DISCOUNT, C_BALANCE 98 , CASE WHEN C_CREDIT = 'BC' 99 THEN SUBSTR(C_DATA, 1, 200) END AS C_DATA 100 FROM NEW TABLE ( UPDATE CUSTOMER 101 SET C_BALANCE = C_BALANCE - PAY_C_ID.H_AMOUNT 102 , C_YTD_PAYMENT = C_YTD_PAYMENT + PAY_C_ID.H_AMOUNT 103 , C_PAYMENT_CNT = C_PAYMENT_CNT + 1 104 , C_DATA = CASE WHEN C_CREDIT = 'BC' 105 THEN BAD_CREDIT_PREFIX 106 || SUBSTR( C_DATA, 1, 466 ) 107 ELSE C_DATA 108 END 109 WHERE C_W_ID = PAY_C_ID.C_W_ID 110 AND C_D_ID = PAY_C_ID.C_D_ID 111 AND C_ID = PAY_C_ID.C_ID 112 ) AS U 113 ) 114 ; 115 /* Update the warehouse */ 116 SET ( W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP ) 117 = ( SELECT W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP 118 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15082138/viewspace-534786/，如需转载，请注明出处，否则将追究法律责任。 0 0 分享到： 上一篇： 通过UPDATE STATISTICS充分利用Informix Dynamic Server优化器 下一篇： DB2 用户交流：性能缺陷 请登录后发表评论 登录 全部评论 <%=items[i].createtime%> <%=items[i].content%> <%if(items[i].items.items.length) { %> <%for(var j=0;j <%=items[i].items.items[j].createtime%> 回复 <%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%> <%}%> <%if(items[i].items.total > 5) { %> 还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看 <%}%> <%}%> <%}%> ArtCode 注册时间：2008-08-05 博文量 269 访问量 557624 最新文章 Optim Development Studio 的新增功能，第 2 部分 编写高效 SQL 语句的最佳实践 DB2 pureScale 实战 整合 DB2 与 AIX 的 WLM 功能进行工作负载管理 IBM solidDB and IBM solidDB Universal Cache认证指南，第 4 部分 IBM solidDB and IBM solidDB Universal Cache认证指南，第 3 部分: 安装与配置 深入探索 IBM 数据分析和预测软件 - PASW Modeler 数据架构师: 您要治理什么？ 在 DB2 Database Partitioning Feature 环境中选择分区键 在DB2 for Linux, UNIX, Windows OLTP环境中使用Enterprise Flash Drives的好处 支持我们 作者招募 用户协议 FAQ Contact Us 北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

转载于:http://blog.itpub.net/15082138/viewspace-534786/