WEB开发网
开发学院数据库DB2 通过DB2 TPC-C基准实现探索SQL 阅读

通过DB2 TPC-C基准实现探索SQL

 2009-12-23 15:00:09 来源:WEB开发网   
核心提示:在介绍 5 个事务之前,先看看各个表的 DDL,通过DB2 TPC-C基准实现探索SQL(2),这些 DDL 都很明了,无需解释

在介绍 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   );

上一页  1 2 3 4 5 6 7  下一页

Tags:通过 DB TPC

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接