# 19. TPC C Benchmark Kit

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
TPC C Benchmark Kit
Building the Test Database
Using the Test Program
Tuning Parameters and Number of Users
Omissions, Exceptions from the Definition
Sample Configuration
Other Factors
TPC C Procedures
DDL Statements
Stored Procedures

## 19.8. DDL Statements

    CREATE TABLE WAREHOUSE (
        W_ID		INTEGER,
        W_NAME		CHARACTER (10),
        W_STREET_1		CHARACTER (20),
        W_STREET_2		CHARACTER (20),
        W_CITY		CHARACTER (20),
        W_STATE		CHARACTER (2),
        W_ZIP		CHARACTER (9),
        W_TAX		NUMERIC,
        W_YTD		NUMERIC,
        PRIMARY KEY (W_ID)
    );

    CREATE TABLE DISTRICT (
        D_ID		INTEGER,
        D_W_ID		INTEGER,
        D_NAME		CHARACTER (10),
        D_STREET_1		CHARACTER (20),
        D_STREET_2		CHARACTER (20),
        D_CITY		CHARACTER (20),
        D_STATE		CHARACTER (2),
        D_ZIP		CHARACTER (9),
        D_TAX		NUMERIC,
        D_YTD		NUMERIC,
        D_NEXT_O_ID		INTEGER,
        PRIMARY KEY (D_W_ID, D_ID)
    );

    CREATE TABLE CUSTOMER (
        C_ID		INTEGER,
        C_D_ID		INTEGER,
        C_W_ID		INTEGER,
        C_FIRST		CHARACTER (16),
        C_MIDDLE		CHARACTER (2),
        C_LAST		VARCHAR,
        C_STREET_1		CHARACTER (20),
        C_STREET_2		CHARACTER (20),
        C_CITY		CHARACTER (20),
        C_STATE		CHARACTER (2),
        C_ZIP		CHARACTER (9),
        C_PHONE		CHARACTER (16),
        C_SINCE		VARCHAR,
        C_CREDIT		CHARACTER (2),
        C_CREDIT_LIM	NUMERIC,
        C_DISCOUNT		NUMERIC,
        C_BALANCE		NUMERIC,
        C_YTD_PAYMENT	NUMERIC,
        C_CNT_PAYMENT	NUMERIC,
        C_CNT_DELIVERY	NUMERIC,
        C_DATA_1		CHARACTER (250),
        C_DATA_2		CHARACTER (250),
        PRIMARY KEY (C_W_ID, C_D_ID, C_ID)
    );

    CREATE INDEX C_BY_LAST ON CUSTOMER (C_W_ID, C_D_ID, C_LAST, C_FIRST);

    CREATE TABLE HISTORY (
        H_C_ID		INTEGER,
        H_C_D_ID		INTEGER,
        H_C_W_ID		INTEGER,
        H_D_ID		INTEGER,
        H_W_ID		INTEGER,
        H_DATE		DATE,
        H_AMOUNT		NUMERIC,
        H_DATA		CHARACTER (24),
        PRIMARY KEY (H_DATE, H_C_ID)
    );

    CREATE TABLE NEW_ORDER (
        NO_O_ID		INTEGER,
        NO_D_ID	 	INTEGER,
        NO_W_ID		INTEGER,
        PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID)
    );

    CREATE TABLE ORDERS (
        O_ID		INTEGER,
        O_D_ID		INTEGER,
        O_W_ID		INTEGER,
        O_C_ID		INTEGER,
        O_ENTRY_D		DATE,
        O_CARRIER_ID	INTEGER,
        O_OL_CNT		INTEGER,
        O_ALL_LOCAL		NUMERIC,
        PRIMARY KEY (O_W_ID, O_D_ID, O_ID)
    );

    CREATE INDEX O_BY_C_ID ON ORDERS (O_W_ID, O_D_ID, O_C_ID, O_ID)

    CREATE TABLE ORDER_LINE (
        OL_O_ID		INTEGER,
        OL_D_ID		INTEGER,
        OL_W_ID		INTEGER,
        OL_NUMBER		INTEGER,
        OL_I_ID		INTEGER,
        OL_SUPPLY_W_ID	INTEGER,
        OL_DELIVERY_D	DATE,
        OL_QUANTITY		NUMERIC,
        OL_AMOUNT		NUMERIC,
        OL_DIST_INFO	CHARACTER (24),
        PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)
    );

    CREATE TABLE ITEM (
        I_ID		INTEGER,
        I_IM_ID		INTEGER,
        I_NAME		CHARACTER (24),
        I_PRICE		NUMERIC,
        I_DATA		CHARACTER (50),
        PRIMARY KEY (I_ID)
    );

    CREATE TABLE STOCK (
        S_I_ID		INTEGER,
        S_W_ID		INTEGER,
        S_QUANTITY		NUMERIC,
        S_DIST_01		CHARACTER (24),
        S_DIST_02		CHARACTER (24),
        S_DIST_03		CHARACTER (24),
        S_DIST_04		CHARACTER (24),
        S_DIST_05		CHARACTER (24),
        S_DIST_06		CHARACTER (24),
        S_DIST_07		CHARACTER (24),
        S_DIST_08		CHARACTER (24),
        S_DIST_09		CHARACTER (24),
        S_DIST_10		CHARACTER (24),
        S_YTD		NUMERIC,
        S_CNT_ORDER		NUMERIC,
        S_CNT_REMOTE	NUMERIC,
        S_DATA		CHARACTER (50),
        PRIMARY KEY (S_I_ID, S_W_ID)
    );

Previous TPC C Procedures | Chapter Contents | Next Stored Procedures