 | 级别： 初级 Serge Rielau (srielau@ca.ibm.com), 高级软件开发人员, IBM Canada Ltd.
2004 年 12 月 01 日 本文介绍 IBM DB2® Universal Database (UDB)™ for Linux™、UNIX® 和 Windows® （DB2 UDB for LUW） TPC-C 基准中使用的高级 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 事务处理来自一个客户的新订单。这里需要做各种不同的事情：
- 获取下一个订单 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。该函数为一个订购项（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)。
到目前为止，我们一直假设，作为一名勤恳的读者，应该对所有其他操作都是熟悉的。
PAYMENT 事务
PAYMENT 事务有两种版本。对于那些提供了客户 id 的客户，使用第一种版本。对于不记得客户 ID，而只提供了姓氏的客户，使用第二种版本。这里只讨论第二种版本，因为其中提出了第一种版本中所没有的挑战。
在支付事务（按姓氏）中，必须发生以下步骤：
- 检索地区的名称和地址。
- 根据姓氏发现客户的客户 id。如果在该地区有多个同姓的客户，则正确的客户应该是根据客户的名字得来的“中间”客户。
- 检索客户的个人信息。
- 增加该地区至今为止的收入。
- 增加仓库至今为止的收入。
- 增加客户的支付额，如果客户信用不佳，则还应包括额外的数据。
- 将这次的支付记录到历史中。
与前面的事务一样，这里的大部分逻辑被封装到一个名为 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
FROM OLD TABLE (
UPDATE WAREHOUSE
119
SET W_YTD = W_YTD + PAY_C_ID.H_AMOUNT
120
WHERE W_ID = PAY_C_ID.W_ID
121 )
AS U
122 )
123 ;
124 /* Finally insert into the history */
125
INSERT
126
INTO HISTORY ( H_C_ID, H_C_D_ID, H_C_W_ID, H_D_ID
127 , H_W_ID, H_DATA, H_DATE, H_AMOUNT )
128
VALUES ( PAY_C_ID.C_ID
129 , PAY_C_ID.C_D_ID
130 , PAY_C_ID.C_W_ID
131 , PAY_C_ID.D_ID
132 , PAY_C_ID.W_ID
133 , VAR.W_NAME || CHAR( ' ', 4 ) || VAR.D_NAME
134 , PAY_C_ID.H_DATE
135 , PAY_C_ID.H_AMOUNT
136 )
137 ;
138 /* Done - return the collected data */
139
RETURN VALUES ( W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP
140 , D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
141 , C_ID , C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
142 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT
143 , C_CREDIT_LIM , C_DISCOUNT, C_BALANCE, C_DATA
144 )
145 ;
146
END
|
清单 14. 用于支付事务的 SQL 语句
1
SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP
2 , D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP
3 , C_ID, C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2
4 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM
5 , C_DISCOUNT, C_BALANCE, C_DATA
6
INTO :w_street_1 , :w_street_2 , :w_city , :w_state , :w_zip
7 , :d_street_1 , :d_street_2 , :d_city , :d_state , :d_zip
8 , :c_id , :c_first , :c_middle , :c_street_1 , :c_street_2 , :c_city , :c_state
9 , :c_zip , :c_phone , :c_since , :c_credit , :c_credit_lim
10 , :c_discount , :c_balance, :c_data :c_data_indicator
11
FROM TABLE ( PAY_C_LAST( :w_id
12 , :d_id
13 , :c_w_id
14 , :c_d_id
15 , :c_last_input
16 , :h_date
17 , :h_amount
18 , :c_data_prefix_c_last
19 )
20 )
AS PAY_C_LAST
21
WITH RR USE AND KEEP UPDATE LOCKS
|
在通常的优化的基础上，还应注意两种新的技术：
- 为了确定正确的客户，需要读 CUSTOMER 表。只有在此基础上，才可以执行对 CUSTOMER 表的更新。默认情况下，这意味着所有姓氏有问题的客户行将获得一个
共享锁（share lock）。为执行更新，需要将共享锁转换为一个
更新锁（update lock）。这里有一个小小的风险，同一个客户可能想要在同一时间为另一个订单进行支付。如果是在取数据（fetch）和更新（update）之间发生这样的情况，那么就会出现
死锁（dead lock），因为如果另一个事务持有共享锁的话，这两个事务就都不能获得更新锁。
为了避免这样情况发生，DB2 V8.2 支持所谓的
lock-request-clause。在这个例子中，
WITH RR USE AND KEEP UPDATE LOCKS 将导致 DB2 在整个语句中收集最少的更新锁，而不是共享锁。为了语义上的纯净和未来的可扩展性，这个 SQL 函数使用一个匹配子句
INHERIT ISOLATION LEVEL WITH LOCK REQUEST。
- 为了发现中间的客户，这里选择了使用 ROW_NUMBER()。这个 OLAP 函数将所有同姓的客户按照他们的名字来编号。而且，这里决定不使用一个单独的查询来获得总的 COUNT。相反，这里再次使用 OLAP。这需要在用于缓冲所有匹配客户的内存消耗 —— 因为总 COUNT 必须跟在每个客户的后面，但是这个总 COUNT 只有到最后才知道 ——
和从客户表进行两次索引扫描之间作出取舍。对于行数较少并且每行的宽度不大的情况，实际上使用 COUNT(*) OVER() 的 (C_ID, COUNT, NUM) 要好一些。
清单 15 展示了支付事务的计划。
清单 15. 支付事务的访问计划
ORDER STATUS 查询
ORDER STATUS 查询的目的是允许客户检索有关他们订单的信息。与支付事务中一样，有的客户记得他们的客户 id，也有一些客户不记得他们的客户 id。下面是假定客户只提供姓氏的情况下的更为复杂的版本：
- 需要使用
PAYMENT 事务中相同的算法确定客户 ID。
- 检索客户的全名和帐户余额。
- 通过选择该客户的最高订单 id，确定最近的订单。
- 如果订单已发货，则确定送货人的 ID，以及提交订单时的日期。
- 检索每个订购项的发货日期、数量、总价和发货仓库。
清单 16. 订单状态函数
1
CREATE FUNCTION ORD_C_LAST( W_ID INTEGER
2 , D_ID SMALLINT
3 , C_LAST VARCHAR(16)
4 )
5
RETURNS TABLE( O_ID INTEGER
6 , O_CARRIER_ID SMALLINT
7 , O_ENTRY_D BIGINT
8 , C_BALANCE BIGINT
9 , C_FIRST VARCHAR(16)
10 , C_MIDDLE CHAR(2)
11 , C_ID INTEGER
12 )
13
SPECIFIC ORD_C_LAST
14
READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC LANGUAGE SQL
15 VAR:
BEGIN ATOMIC
16
DECLARE C_BALANCE BIGINT ;
17
DECLARE C_FIRST VARCHAR(16) ;
18
DECLARE C_MIDDLE CHAR(2) ;
19
DECLARE C_ID INTEGER ;
20
DECLARE O_ID INTEGER;
21
DECLARE O_CARRIER_ID SMALLINT;
22
DECLARE O_ENTRY_D BIGINT;
23
24 /* Retrieve the Customer information */
25
SET ( C_BALANCE, C_FIRST, C_MIDDLE, C_ID )
26 = (
SELECT C_BALANCE, C_FIRST, C_MIDDLE , C_ID
27
FROM (
SELECT C_ID
28 , C_BALANCE
29 , C_FIRST
30 , C_MIDDLE
31 , COUNT(*) OVER() AS COUNT
32 , ROWNUMBER() OVER (ORDER BY C_FIRST)
AS NUM
33
FROM CUSTOMER
34
WHERE C_W_ID = ORD_C_LAST.W_ID
35
AND C_D_ID = ORD_C_LAST.D_ID
36
AND C_LAST = ORD_C_LAST.C_LAST
37 )
AS V1
38
WHERE NUM = (COUNT + 1) / 2
39 )
40 ;
41 /* Take advantage of the index to fetch the first row (and hence max(o_id) ) */
42
SET ( O_ID , O_CARRIER_ID , O_ENTRY_D )
43 = (
SELECT O_ID
44 , O_CARRIER_ID
45 , O_ENTRY_D
46
FROM ORDERS
47
WHERE O_W_ID = ORD_C_LAST.W_ID
48
AND O_D_ID = ORD_C_LAST.D_ID
49
AND O_C_ID = VAR.C_ID
50
ORDER BY O_ID
DESC
51
FETCH FIRST 1
ROW ONLY
52 )
53 ;
54
RETURN VALUES ( VAR.O_ID
55 , VAR.O_CARRIER_ID
56 , VAR.O_ENTRY_D
57 , VAR.C_BALANCE
58 , VAR.C_FIRST
59 , VAR.C_MIDDLE
60 , VAR.C_ID
61 )
62 ;
63
END
|
清单 17. 调用函数
1
SELECT O_ID, O_CARRIER_ID, O_ENTRY_D, C_BALANCE, C_FIRST, C_MIDDLE, C_ID
2
INTO :o_id, :o_carrier_id , :o_entry_d , :c_balance, :c_first, :c_middle, :c_id
3
FROM TABLE ( ORD_C_LAST( :w_id
4 , :d_id
5 , :c_last_input
6 )
7 )
AS ORD_C_LAST
|
清单 18. 用于订单状态查询的 SQL 语句
1
SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DELIVERY_D
2
FROM ORDER_LINE
3
WHERE OL_W_ID = :w_id
4
AND OL_D_ID = :d_id
5
AND OL_O_ID = :o_id
6
FOR FETCH ONLY ;
|
这里同样应用了很多常用的提高性能的技巧。例如，所有未涉及订购项的步骤都被封装到一个 SQL 表函数中。而且，这里使用 OLAP 来检索“中间客户”。然而，最后从这个查询中还可以收集到一些有趣的事情：
天真的人可能会首先确定客户的最大订单 id，然后使用这个 ID 来检索送货人和订单日期。如果订单 id 按照降序排序，则一个客户的最大订单 id 也就是基于客户 id 和订单 id 的索引中的第一个订单 ID。然而，利用这一事实将那两个查询组合到一起则显得更为紧凑。给定一个匹配的索引，通过一个单独的取索引操作就可以得到要检索的行。在发货事务中也使用了相同的技巧，但此处则没有 DELETE 和 MAXIMUM。
注意，订购项是通过一个单独的游标来检索的。执行两条语句与返回这两个查询的笛卡儿积相比效率要高一些，后者将重复发送每个订购项的客户信息和订单信息。
清单 19 列出的计划展示了使用前面讨论的 ORDER BY 的 (FETCH(8)) 和 FETCH FIRST 1 ROW ONLY 的效率。
清单 19. 订单状态查询计划
Rows
RETURN
( 1)
Cost
I/O
|
1
NLJOIN
( 2)
12.928
2.008
/----------+---------\
1 1
NLJOIN TBSCAN
( 3) ( 10)
12.9279 4.48727e-005
2.008 0
/---------+--------\ |
1 1 1
TBSCAN UNION TABFNC: SYSIBM
( 4) ( 5) GENROW
4.48727e-005 0.108135
0 0.013056
| /-------+------\
1 1 1
TABFNC: SYSIBM FILTER FETCH
GENROW ( 6) ( 8)
0.0176324 0.0905021
0 0.005056
| /----+---\
0.2 0.005056 79
IXSCAN IXSCAN TABLE: SRIELAU
( 7) ( 9) ORDERS
0.0158334 0.0251716
0 0
| |
5 79
INDEX: SRIELAU INDEX: SRIELAU
CUST_IDXB ORDR_IDXB
|
STOCK LEVEL 查询
最后一点，也是重要的一点，
STOCK LEVEL 查询演习了一个三方（three-way）连接，以确定对于一个给定的、库存水平低于一个指定阈值的地区，在过去 20 份订单中产品的数量。关于这个查询没有很多要讲的，只有一点：该查询是惟一可以以
cursor stability 隔离级别运行的查询。DB2 能够逐个地为查询指定隔离级别，这里就使用了这一功能。
清单 20. 库存水平查询
1
SELECT COUNT( S_I_ID )
INTO :low_stock
2
FROM (
SELECT DISTINCT S_I_ID
3
FROM ORDER_LINE , STOCK , DISTRICT
4
WHERE D_W_ID = :w_id
5
AND D_ID = :d_id
6
AND OL_O_ID < d_next_o_id
7
AND OL_O_ID >= ( d_next_o_id - 20 )
8
AND OL_W_ID = D_W_ID
9
AND OL_D_ID = D_ID
10
AND S_I_ID = OL_I_ID
11
AND S_W_ID = OL_W_ID
12
AND S_QUANTITY < :threshold
13 )
AS OLS
14
WITH CS
|
清单 21. 库存水平查询访问计划
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
13.204
1.02222
|
3.75467e-005
TBSCAN
( 3)
13.2039
1.02222
|
3.75467e-005
SORT
( 4)
13.2033
1.02222
|
3.75467e-005
NLJOIN
( 5)
13.2023
1.02222
/--------------------+--------------------\
0.00782222 0.0048
NLJOIN FETCH
( 6) ( 11)
13.0011 0.201169
1.00782 0.0144
/-----------+-----------\ /----+---\
1 0.00782222 0.0144 9
FETCH FETCH IXSCAN TABLE: SRIELAU
( 7) ( 9) ( 12) STOCK
12.872 0.129119 0.0157274
1 0.00782222 0
/----+---\ /----+---\ |
1 26 0.00782222 44 9
IXSCAN TABLE: SRIELAU IXSCAN TABLE: SRIELAU INDEX: SYSIBM
( 8) DISTRICT ( 10) ORDER_LINE SQL0410231029421
0.0175755 0.0282312
0 0
| |
26 44
INDEX: SYSIBM INDEX: SYSIBM
SQL0410231029415 SQL0410231030088
|
结束语
在本文中，Rielau 简要地介绍了 TPC-C 基准的模式及其事务。为了在 DB2 已达到的极限级别上执行这个基准，需要更多的东西，但对 SQL 的简洁的使用处于首要地位。高效的 SQL 产生高效的查询计划，高效的查询计划又意味着只需要执行必不可少的代码路径。只读取必不可少的行。Rielau 认为 DB2 在 TPC-C 基准中使用的 SQL 已经非常接近最优。要进一步精化的东西非常少。
总而言之，以下是 TPC-C 这个实现暴露出的有趣的 SQL 特性：
-
SQL 表函数的使用使您可以将过程性逻辑放入到查询的 FROM 子句中。通过
关联（correlation），SQL 表函数允许以一种更高效的方式实现迭代，而不是使用游标。
-
SQL 表函数中的
MODIFIES SQL DATA 使您甚至可以将 INSERT、UPDATE、DELETE 和 MERGE （
数据更改操作）放入到关联连接的内表中。
- 在
FROM 子句中对
数据更新操作 的使用允许对生成的列（例如 ID）的检索，以及对要被删除或更新的数据的检索。
- 通过使用
作为
数据更改操作 的目标的查询
，可以删除或更新由复杂的 SQL（包括 ORDER BY）确定的行。对这一特性的一个常见应用就是
POP 队列语义
的实现。
-
ORDER BY 结合 FETCH FIRST 可以有效地用于选择最大或最小行，包括对不是聚合函数本身一部分的列的检索。
-
当按条件选择行，而不是简单地选择最大或最小行时，可以考虑
OLAP 函数。
- 公共表表达式（WITH 子句）允许
数据更新操作 的高效
管道。
虽然 TPC-C 基准非常简单，但是它在 OLTP 处理的很多方面仍然非常有效。
注意，虽然 DB2 不是第一种引入从 INSERT、UPDATE 和 DELETE 返回数据的手段的产品，但它是第一种将此概念集成到 SQL 本身当中的产品，它为结果的即时关系处理提供了支持，而无需使用临时表和过程语言结构。
致谢
我编写此文也许不需要太多的帮助，但本文仍是基于整个组织的成就。
我首先要感谢 IBM Almaden Research Center 的 Bobbie Cochrane、Hamid Pirahesh 和 Richard Sidle。如果没有他们在 Starburst 的工作，就不存在 FROM 子句中的
内联 SQL PL 和
数据更改操作。
我还想感谢我的团队，是我们的团队交付了嵌套
数据更改操作 —— 这是过去几年来最令人激动的 SQL 扩展之一。
最后，我想感谢过去 18 个月为使 TPC-C 结果成为现实作出贡献的每一个人。
当 IBM 想要交付他们最快的机器上最快的结果时，只有一个去处：
对门！
参考资料
关于作者 | | | Serge Rielau 是一名高级软件开发人员，在 IBM 多伦多实验室从事 DB2 UDB SQL 编译器的开发。他在 DB2 的 SQL 编译器开发小组工作 7 年了，是 SQL 语言专家。他开发了很多项目，其中包括
内联 SQL PL 和
SQL 函数。他还设计了通常所谓的“SELECT FROM INSERT、UPDATE 和 DELETE”。目前，Serge 花很多时间帮助客户从其他 DBMS 迁移到 DB2，他还是 comp.databases.ibm-db2 新闻组的积极分子。
|
对本文的评价
| |
