PAYMENT事务
PAYMENT事务有两种版本，
通过DB2 TPCC基准实现探索SQL(2)
。对于那些提供了客户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，
电脑资料
《通过DB2 TPCC基准实现探索SQL(2)》(https://www.unjs.com)。检索客户的全名和帐户余额。
通过选择该客户的最高订单 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 本身当中的产品，它为结果的即时关系处理提供了支持，而无需使用临时表和过程语言结构。