简介
在过去一年半的时间里，在 标准TPC-C基准方面有很多DB2 for LUW 活动，这个基准常用于测试在线事务处理环境中的性能，
通过DB2 TPCC基准实现探索SQL(1)
。其结果跨度很大，在具备通常配置的机器上，它可能是非常小的数字，在那些家庭和工作中不多见的特殊配置上，又可能是非常大的数字。虽然填充数据库模式的行的数量可能被调整到一定规模，但有一个地方仍然是相同的：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 事务处理来自一个客户的新订单。这里需要做各种不同的事情：
获取下一个订单 id 和客户所在地区的地区税率。
增加客户所在地区的下一个订单 id，以便用于随后的事务。
对于客户订购的每种产品：
从 ITEM 表检索产品的名称、价格和描述。
从 STOCK 表检索该产品的地区信息和剩下的库存水平。
将该产品的库存水平减去订购的数量。如果库存低于阈值，则应该订购货物（通过简单地增加库存值来实现）。
将获得的相关数据，包括总价格，插入到 ORDER_LINE 中。
将订单插入 ORDERS 和 NEW_ORDER 表。
从 CUSTOMER 检索客户名、折扣、信用信息。
从 WAREHOUSE 检索销售税。
考虑折扣和税，计算总价格。
这是很大的工作量。不过，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 语句。接下来我们逐步来分析这个查询：
ITEMLIST 由输入参数组合而成。它包括产品以及每种产品的数量，这是在一个 VALUES 子句中收集到的。对于列表中的所有产品，订单 id、地区和仓库是不变的。
通过使用一个关联连接（correlated join）， NEW_OL_LOCAL SQL 表函数逐个处理列表中的每种产品。很快您就可以看到，该函数返回产品的价格、某些元数据和库存信息。
对于不能找到的产品（价格为 NULL），可以将其过滤掉。将产生的表称作 DATA。
将 DATA 中的每种产品插入到 ORDER_LINE 中。
将每种产品的价格、名称、元数据和库存信息返回给用户。
这里有两件有趣的事情。
首先， 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，
电脑资料
《通过DB2 TPCC基准实现探索SQL(1)》(https://www.unjs.com)。该函数为一个订购项（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)，它表示整个函数，从中可以看到一个按三种方法排序的连接：
初始化本地变量。
执行函数体。
返回结果表。
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 是一个非常简单的事务：
送货者从 NEW_ORDER 队列中找到时间最久的订单 id。
将该订单从队列中删除。
从存储在 ORDER 中的订单检索客户 id。
从 ORDER_LINE 计算应付总额。注意，在正常的情况下，总数决不会存储在订单本身当中。
通过设置送货日期，将订购项标记为已发货。
通过设置送货日期，将订单标记为已发货。
更新客户的结余和发货计数。
返回订单 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 条子语句。怎么回事？
在 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 结果。
检索客户 id 和更新订单被合成一条语句。
通过更新 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)。
到目前为止，我们一直假设，作为一名勤恳的读者，应该对所有其他操作都是熟悉的。