通过DB2 TPC-C基准实现探索SQL
2009-12-23 15:00:09 来源:WEB开发网在介绍 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 );
更多精彩
赞助商链接