通过DB2 TPC-C基准实现探索SQL
2009-12-23 15:00:09 来源:WEB开发网这个组合的计划在结构上与单独的 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
更多精彩
赞助商链接