184 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
The database table used for the tests described in this redbook was ITEM_FACT. Its layout is
shown in Table B-1. The table contained 6,001,215 rows and occupied approximately 1.6 GB.
Indexes were created and dropped as needed to perform specific tests. A modified copy of
the table, ITEM_FACT_SHORT, was created with some VARCHAR columns. This was used
specifically to test performance of queries referencing variable length character columns.
The table sizes are:
Table PART_DIM has 200,000 records.
Table SUPPLIER_DIM has 10,000 records.
Table CUST_DIM has 150,000 records.
Table TIME_DIM has 1,450 records.
Table ITEM_FACT has 6,001,215 records.
Table B-1 ITEM_FACT: Database table layout
Column name Type Length
ORDERKEY DECIMAL 16,0
QUANTITY DECIMAL 15,2
EXTENDED PRICE DECIMAL 15,2
DISCOUNT DECIMAL 15,2
TAX DECIMAL 15,2
RETURNFLAG CHARACTER 1
LINESTATUS CHARACTER 1
SHIPMODE CHARACTER 10
SUPPLYCOST DECIMAL 15,2
ORDERPRIORITY CHARACTER 15
REVENUE_WO_TAX DECIMAL 15,2
REVENUE_W_TAX DECIMAL 15,2
PROFIT_WO_TAX DECIMAL 15,2
PROFIT_W-TAX DECIMAL 15,2