TPC-DS Benchmark How To Guide

Commands:

Create Oracle user TPCDS

 SQL> CREATE USER "TPCDS" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK GRANT CREATE TABLE TO "TPCDS" GRANT "CONNECT" TO "TPCDS";

Get DSGen and unpack

• mkdir /tmp/dsgen
• cd /tmp/dsgen
• wget https://www.inf.unibz.it/wiki/_media/public/dsgen_v1.1.0.zip
• unzip dsgen_v1.1.0.zip

Compile/build binaries

• cp tools/Makefile.suite tools/Makefile
• cat tools/Makefile | grep ^OS

| * Result should be: OS | = | LINUX |

• cd tools
• make

Generate load data

• mkdir /tmp/tpc
• ./dsdgen -scale 1 -dir /tmp/tpc

Import tables into Oracle

• su - oracle
• cd /tmp/dsgen/tools
• sqlplus tpcds/* @tpcds.sql
• sqlplus tpcds/* @tpcds_source.sql

Load data

• su - oracle
• mkdir tpc-ds
• cd tpc-ds

Prepare control files:

• vi call_center.ctl

call_center.ctl

load data infile '/tmp/tpc/call_center.dat' into table call_center fields terminated by '|' ( CC_CALL_CENTER_SK, CC_CALL_CENTER_ID, CC_REC_START_DATE date "YYYY-MM-DD", CC_ REC_END_DATE date "YYYY-MM-DD", CC_CLOSED_DATE_SK, CC_OPEN_DATE_SK, CC_NAME, CC_ CLASS, CC_EMPLOYEES, CC_SQ_FT, CC_HOURS, CC_MANAGER, CC_MKT_ID, CC_MKT_CLASS, CC _MKT_DESC, CC_MARKET_MANAGER, CC_DIVISION, CC_DIVISION_NAME, CC_COMPANY, CC_COMP ANY_NAME, CC_STREET_NUMBER, CC_STREET_NAME, CC_STREET_TYPE, CC_SUITE_NUMBER, CC_ CITY, CC_COUNTY, CC_STATE, CC_ZIP, CC_COUNTRY, CC_GMT_OFFSET, CC_TAX_PERCENTAGE )

1.  
2. load data
3. infile '/tmp/tpc/call_center.dat'
4. into table call_center
5. fields terminated by '|'
6. ( CC_CALL_CENTER_SK, CC_CALL_CENTER_ID, CC_REC_START_DATE date "YYYY-MM-DD", CC_
7. REC_END_DATE date "YYYY-MM-DD", CC_CLOSED_DATE_SK, CC_OPEN_DATE_SK, CC_NAME, CC_
8. CLASS, CC_EMPLOYEES, CC_SQ_FT, CC_HOURS, CC_MANAGER, CC_MKT_ID, CC_MKT_CLASS, CC
9. _MKT_DESC, CC_MARKET_MANAGER, CC_DIVISION, CC_DIVISION_NAME, CC_COMPANY, CC_COMP
10. ANY_NAME, CC_STREET_NUMBER, CC_STREET_NAME, CC_STREET_TYPE, CC_SUITE_NUMBER, CC_
11. CITY, CC_COUNTY, CC_STATE, CC_ZIP, CC_COUNTRY, CC_GMT_OFFSET, CC_TAX_PERCENTAGE
12. )
13.  

• vi catalog_page.ctl

catalog_page.ctl

load data infile '/tmp/tpc/catalog_page.dat' into table catalog_page fields terminated by '|' ( CP_CATALOG_PAGE_SK, CP_CATALOG_PAGE_ID, CP_START_DATE_SK, CP_END_DATE_SK, CP_D EPARTMENT, CP_CATALOG_NUMBER, CP_CATALOG_PAGE_NUMBER, CP_DESCRIPTION, CP_TYPE )

1.  
2. load data
3. infile '/tmp/tpc/catalog_page.dat'
4. into table catalog_page
5. fields terminated by '|'
6. ( CP_CATALOG_PAGE_SK, CP_CATALOG_PAGE_ID, CP_START_DATE_SK, CP_END_DATE_SK, CP_D
7. EPARTMENT, CP_CATALOG_NUMBER, CP_CATALOG_PAGE_NUMBER, CP_DESCRIPTION,
8. CP_TYPE )
9.  

• vi catalog_returns.ctl

catalog_page.ctl

load data infile '/tmp/tpc/catalog_returns.dat' into table catalog_returns fields terminated by '|' ( CR_RETURNED_DATE_SK, CR_RETURNED_TIME_SK, CR_ITEM_SK, CR_REFUNDED_CUSTOMER_SK, CR_REFUNDED_CDEMO_SK, CR_REFUNDED_HDEMO_SK, CR_REFUNDED_ADDR_SK, CR_RETURNING_CUSTOMER_SK, CR_RETURNING_CDEMO_SK, CR_RETURNING_HDEMO_SK, CR_RETURNING_ADDR_SK, CR_CALL_CENTER_SK, CR_CATALOG_PAGE_SK, CR_SHIP_MODE_SK, CR_WAREHOUSE_SK, CR_REASON_SK, CR_ORDER_NUMBER, CR_RETURN_QUANTITY, CR_RETURN_AMOUNT, CR_RETURN_TAX, CR_RETURN_AMT_INC_TAX, CR_FEE, CR_RETURN_SHIP_COST, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT, CR_NET_LOSS )

1.  
2. load data
3. infile '/tmp/tpc/catalog_returns.dat'
4. into table catalog_returns
5. fields terminated by '|'
6. ( CR_RETURNED_DATE_SK, CR_RETURNED_TIME_SK, CR_ITEM_SK, CR_REFUNDED_CUSTOMER_SK, CR_REFUNDED_CDEMO_SK, CR_REFUNDED_HDEMO_SK, CR_REFUNDED_ADDR_SK, CR_RETURNING_CUSTOMER_SK, CR_RETURNING_CDEMO_SK, CR_RETURNING_HDEMO_SK, CR_RETURNING_ADDR_SK, CR_CALL_CENTER_SK, CR_CATALOG_PAGE_SK, CR_SHIP_MODE_SK, CR_WAREHOUSE_SK, CR_REASON_SK, CR_ORDER_NUMBER, CR_RETURN_QUANTITY, CR_RETURN_AMOUNT, CR_RETURN_TAX, CR_RETURN_AMT_INC_TAX, CR_FEE, CR_RETURN_SHIP_COST, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT, CR_NET_LOSS )
7.  

• vi catalog_sales.ctl

catalog_sales.ctl

load data infile '/tmp/tpc/catalog_sales.dat' into table catalog_sales fields terminated by '|' ( CS_SOLD_DATE_SK, CS_SOLD_TIME_SK, CS_SHIP_DATE_SK, CS_BILL_CUSTOMER_SK, CS_BILL_CDEMO_SK, CS_BILL_HDEMO_SK, CS_BILL_ADDR_SK, CS_SHIP_CUSTOMER_SK, CS_SHIP_CDEMO_SK, CS_SHIP_HDEMO_SK, CS_SHIP_ADDR_SK, CS_CALL_CENTER_SK, CS_CATALOG_PAGE_SK, CS_SHIP_MODE_SK, CS_WAREHOUSE_SK, CS_ITEM_SK, CS_PROMO_SK, CS_ORDER_NUMBER, CS_QUANTITY, CS_WHOLESALE_COST, CS_LIST_PRICE, CS_SALES_PRICE, CS_EXT_DISCOUNT_AMT, CS_EXT_SALES_PRICE, CS_EXT_WHOLESALE_COST, CS_EXT_LIST_PRICE, CS_EXT_TAX, CS_COUPON_AMT, CS_EXT_SHIP_COST, CS_NET_PAID, CS_NET_PAID_INC_TAX, CS_NET_PAID_INC_SHIP, CS_NET_PAID_INC_SHIP_TAX, CS_NET_PROFIT )

1.  
2. load data
3. infile '/tmp/tpc/catalog_sales.dat'
4. into table catalog_sales
5. fields terminated by '|'
6. ( CS_SOLD_DATE_SK, CS_SOLD_TIME_SK, CS_SHIP_DATE_SK, CS_BILL_CUSTOMER_SK, CS_BILL_CDEMO_SK, CS_BILL_HDEMO_SK, CS_BILL_ADDR_SK, CS_SHIP_CUSTOMER_SK, CS_SHIP_CDEMO_SK, CS_SHIP_HDEMO_SK, CS_SHIP_ADDR_SK, CS_CALL_CENTER_SK, CS_CATALOG_PAGE_SK, CS_SHIP_MODE_SK, CS_WAREHOUSE_SK, CS_ITEM_SK, CS_PROMO_SK, CS_ORDER_NUMBER, CS_QUANTITY, CS_WHOLESALE_COST, CS_LIST_PRICE, CS_SALES_PRICE, CS_EXT_DISCOUNT_AMT, CS_EXT_SALES_PRICE, CS_EXT_WHOLESALE_COST, CS_EXT_LIST_PRICE, CS_EXT_TAX, CS_COUPON_AMT, CS_EXT_SHIP_COST, CS_NET_PAID, CS_NET_PAID_INC_TAX, CS_NET_PAID_INC_SHIP, CS_NET_PAID_INC_SHIP_TAX, CS_NET_PROFIT )
7.  

• vi customer_address.ctl

customer_address.ctl

load data infile '/tmp/tpc/customer_address.dat' into table customer_address fields terminated by '|' ( CA_ADDRESS_SK,CA_ADDRESS_ID,CA_STREET_NUMBER,CA_STREET_NAME,CA_STREET_TYPE,CA_SUITE_NUMBER,CA_CITY,CA_COUNTY,CA_STATE,CA_ZIP,CA_COUNTRY,CA_GMT_OFFSET,CA_LOCATION_TYPE )

1.  
2. load data
3. infile '/tmp/tpc/customer_address.dat'
4. into table customer_address
5. fields terminated by '|'
6. ( CA_ADDRESS_SK,CA_ADDRESS_ID,CA_STREET_NUMBER,CA_STREET_NAME,CA_STREET_TYPE,CA_SUITE_NUMBER,CA_CITY,CA_COUNTY,CA_STATE,CA_ZIP,CA_COUNTRY,CA_GMT_OFFSET,CA_LOCATION_TYPE )
7.  

• vi customer.ctl

customer.ctl

load data infile '/tmp/tpc/customer.dat' into table customer fields terminated by '|' ( C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK, C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE )

1.  
2. load data
3. infile '/tmp/tpc/customer.dat'
4. into table customer
5. fields terminated by '|'
6. ( C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK, C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE )
7.  

• vi customer_demographics.ctl

customer_demographics.ctl

load data infile '/tmp/tpc/customer_demographics.dat' into table customer_demographics fields terminated by '|' ( CD_DEMO_SK,CD_GENDER,CD_MARITAL_STATUS,CD_EDUCATION_STATUS,CD_PURCHASE_ESTIMATE,CD_CREDIT_RATING,CD_DEP_COUNT,CD_DEP_EMPLOYED_COUNT,CD_DEP_COLLEGE_COUNT )

1.  
2. load data
3. infile '/tmp/tpc/customer_demographics.dat'
4. into table customer_demographics
5. fields terminated by '|'
6. ( CD_DEMO_SK,CD_GENDER,CD_MARITAL_STATUS,CD_EDUCATION_STATUS,CD_PURCHASE_ESTIMATE,CD_CREDIT_RATING,CD_DEP_COUNT,CD_DEP_EMPLOYED_COUNT,CD_DEP_COLLEGE_COUNT )
7.  

• vi date_dim.ctl

date_dim.ctl

load data infile '/tmp/tpc/date_dim.dat' into table date_dim fields terminated by '|' ( D_DATE_SK,D_DATE_ID,D_DATE date "YYYY-MM-DD",D_MONTH_SEQ,D_WEEK_SEQ,D_QUARTER_SEQ,D_YEAR,D_DOW,D_MOY,D_DOM,D_QOY,D_FY_YEAR,D_FY_QUARTER_SEQ,D_FY_WEEK_SEQ,D_DAY_NAME,D_QUARTER_NAME,D_HOLIDAY,D_WEEKEND,D_FOLLOWING_HOLIDAY,D_FIRST_DOM,D_LAST_DOM,D_SAME_DAY_LY,D_SAME_DAY_LQ,D_CURRENT_DAY,D_CURRENT_WEEK,D_CURRENT_MONTH,D_CURRENT_QUARTER,D_CURRENT_YEAR )

1.  
2. load data
3. infile '/tmp/tpc/date_dim.dat'
4. into table date_dim
5. fields terminated by '|'
6. ( D_DATE_SK,D_DATE_ID,D_DATE date "YYYY-MM-DD",D_MONTH_SEQ,D_WEEK_SEQ,D_QUARTER_SEQ,D_YEAR,D_DOW,D_MOY,D_DOM,D_QOY,D_FY_YEAR,D_FY_QUARTER_SEQ,D_FY_WEEK_SEQ,D_DAY_NAME,D_QUARTER_NAME,D_HOLIDAY,D_WEEKEND,D_FOLLOWING_HOLIDAY,D_FIRST_DOM,D_LAST_DOM,D_SAME_DAY_LY,D_SAME_DAY_LQ,D_CURRENT_DAY,D_CURRENT_WEEK,D_CURRENT_MONTH,D_CURRENT_QUARTER,D_CURRENT_YEAR )
7.  

• vi dbgen_version.ctl

dbgen_version.ctl

load data infile '/tmp/tpc/dbgen_version.dat' into table dbgen_version fields terminated by '|' ( DV_VERSION, DV_CREATE_DATE, DV_CREATE_TIME, DV_CMDLINE_ARGS )

1.  
2. load data
3. infile '/tmp/tpc/dbgen_version.dat'
4. into table dbgen_version
5. fields terminated by '|'
6. ( DV_VERSION, DV_CREATE_DATE, DV_CREATE_TIME, DV_CMDLINE_ARGS )
7.  

• vi household_demographics.ctl

household_demographics.ctl

load data infile '/tmp/tpc/household_demographics.dat' into table household_demographics fields terminated by '|' ( HD_DEMO_SK, HD_INCOME_BAND_SK, HD_BUY_POTENTIAL, HD_DEP_COUNT, HD_VEHICLE_COUNT )

1.  
2. load data
3. infile '/tmp/tpc/household_demographics.dat'
4. into table household_demographics
5. fields terminated by '|'
6. ( HD_DEMO_SK, HD_INCOME_BAND_SK, HD_BUY_POTENTIAL, HD_DEP_COUNT, HD_VEHICLE_COUNT )
7.  

• vi income_band.ctl

income_band.ctl

load data infile '/tmp/tpc/income_band.dat' into table income_band fields terminated by '|' (IB_INCOME_BAND_SK, IB_LOWER_BOUND, IB_UPPER_BOUND )

1.  
2. load data
3. infile '/tmp/tpc/income_band.dat'
4. into table income_band
5. fields terminated by '|'
6. (IB_INCOME_BAND_SK, IB_LOWER_BOUND, IB_UPPER_BOUND )
7.  

• vi inventory.ctl

inventory.ctl

load data infile '/tmp/tpc/inventory.dat' into table inventory fields terminated by '|' ( INV_DATE_SK, INV_ITEM_SK, INV_WAREHOUSE_SK, INV_QUANTITY_ON_HAND ) load data infile '/tmp/tpc/item.dat' into table item fields terminated by '|' ( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )

1.  
2. load data
3. infile '/tmp/tpc/inventory.dat'
4. into table inventory
5. fields terminated by '|'
6. ( INV_DATE_SK, INV_ITEM_SK, INV_WAREHOUSE_SK, INV_QUANTITY_ON_HAND )
7. load data
8. infile '/tmp/tpc/item.dat'
9. into table item
10. fields terminated by '|'
11. ( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )
12.  

• vi item.ctl

item.ctl

load data infile '/tmp/tpc/item.dat' into table item fields terminated by '|' ( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )

1.  
2. load data
3. infile '/tmp/tpc/item.dat'
4. into table item
5. fields terminated by '|'
6. ( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )
7.  

• vi promotion.ctl

promotion.ctl

load data infile '/tmp/tpc/promotion.dat' into table promotion fields terminated by '|' ( P_PROMO_SK, P_PROMO_ID, P_START_DATE_SK, P_END_DATE_SK, P_ITEM_SK, P_COST, P_RESPONSE_TARGET, P_PROMO_NAME, P_CHANNEL_DMAIL, P_CHANNEL_EMAIL, P_CHANNEL_CATALOG, P_CHANNEL_TV, P_CHANNEL_RADIO, P_CHANNEL_PRESS, P_CHANNEL_EVENT, P_CHANNEL_DEMO, P_CHANNEL_DETAILS, P_PURPOSE, P_DISCOUNT_ACTIVE )

1.  
2. load data
3. infile '/tmp/tpc/promotion.dat'
4. into table promotion
5. fields terminated by '|'
6. ( P_PROMO_SK, P_PROMO_ID, P_START_DATE_SK, P_END_DATE_SK, P_ITEM_SK, P_COST, P_RESPONSE_TARGET, P_PROMO_NAME, P_CHANNEL_DMAIL, P_CHANNEL_EMAIL, P_CHANNEL_CATALOG, P_CHANNEL_TV, P_CHANNEL_RADIO, P_CHANNEL_PRESS, P_CHANNEL_EVENT, P_CHANNEL_DEMO, P_CHANNEL_DETAILS, P_PURPOSE, P_DISCOUNT_ACTIVE )
7.  

• vi reason.ctl

reason.ctl

load data infile '/tmp/tpc/reason.dat' into table reason fields terminated by '|' ( R_REASON_SK, R_REASON_ID, R_REASON_DESC )

1.  
2. load data
3. infile '/tmp/tpc/reason.dat'
4. into table reason
5. fields terminated by '|'
6. ( R_REASON_SK, R_REASON_ID, R_REASON_DESC )
7.  

• vi ship_mode.ctl

ship_mode.ctl

load data infile '/tmp/tpc/ship_mode.dat' into table ship_mode fields terminated by '|' ( SM_SHIP_MODE_SK,SM_SHIP_MODE_ID,SM_TYPE,SM_CODE,SM_CARRIER,SM_CONTRACT )

1.  
2. load data
3. infile '/tmp/tpc/ship_mode.dat'
4. into table ship_mode
5. fields terminated by '|'
6. ( SM_SHIP_MODE_SK,SM_SHIP_MODE_ID,SM_TYPE,SM_CODE,SM_CARRIER,SM_CONTRACT )
7.  

• vi store.ctl

store.ctl

load data infile '/tmp/tpc/store.dat' into table store fields terminated by '|' ( S_STORE_SK, S_STORE_ID, S_REC_START_DATE date "YYYY-MM-DD", S_REC_END_DATE date "YYYY-MM-DD", S_CLOSED_DATE_SK, S_STORE_NAME, S_NUMBER_EMPLOYEES, S_FLOOR_SPACE, S_HOURS, S_MANAGER, S_MARKET_ID, S_GEOGRAPHY_CLASS, S_MARKET_DESC, S_MARKET_MANAGER, S_DIVISION_ID, S_DIVISION_NAME, S_COMPANY_ID, S_COMPANY_NAME, S_STREET_NUMBER, S_STREET_NAME, S_STREET_TYPE, S_SUITE_NUMBER, S_CITY, S_COUNTY, S_STATE, S_ZIP, S_COUNTRY, S_GMT_OFFSET, S_TAX_PRECENTAGE )

1.  
2. load data
3. infile '/tmp/tpc/store.dat'
4. into table store
5. fields terminated by '|'
6. ( S_STORE_SK, S_STORE_ID, S_REC_START_DATE date "YYYY-MM-DD", S_REC_END_DATE date "YYYY-MM-DD", S_CLOSED_DATE_SK, S_STORE_NAME, S_NUMBER_EMPLOYEES, S_FLOOR_SPACE, S_HOURS, S_MANAGER, S_MARKET_ID, S_GEOGRAPHY_CLASS, S_MARKET_DESC, S_MARKET_MANAGER, S_DIVISION_ID, S_DIVISION_NAME, S_COMPANY_ID, S_COMPANY_NAME, S_STREET_NUMBER, S_STREET_NAME, S_STREET_TYPE, S_SUITE_NUMBER, S_CITY, S_COUNTY, S_STATE, S_ZIP, S_COUNTRY, S_GMT_OFFSET, S_TAX_PRECENTAGE )
7.  

• vi store_returns.ctl

store_returns.ctl

load data infile '/tmp/tpc/store_returns.dat' into table store_returns fields terminated by '|' ( SR_RETURNED_DATE_SK, SR_RETURN_TIME_SK, SR_ITEM_SK, SR_CUSTOMER_SK, SR_CDEMO_SK, SR_HDEMO_SK, SR_ADDR_SK, SR_STORE_SK, SR_REASON_SK, SR_TICKET_NUMBER, SR_RETURN_QUANTITY, SR_RETURN_AMT, SR_RETURN_TAX, SR_RETURN_AMT_INC_TAX, SR_FEE, SR_RETURN_SHIP_COST, SR_REFUNDED_CASH, SR_REVERSED_CHARGE, SR_STORE_CREDIT, SR_NET_LOSS )

1.  
2. load data
3. infile '/tmp/tpc/store_returns.dat'
4. into table store_returns
5. fields terminated by '|'
6. ( SR_RETURNED_DATE_SK, SR_RETURN_TIME_SK, SR_ITEM_SK, SR_CUSTOMER_SK, SR_CDEMO_SK, SR_HDEMO_SK, SR_ADDR_SK, SR_STORE_SK, SR_REASON_SK, SR_TICKET_NUMBER, SR_RETURN_QUANTITY, SR_RETURN_AMT, SR_RETURN_TAX, SR_RETURN_AMT_INC_TAX, SR_FEE, SR_RETURN_SHIP_COST, SR_REFUNDED_CASH, SR_REVERSED_CHARGE, SR_STORE_CREDIT, SR_NET_LOSS )
7.  

• vi store_sales.ctl

store_sales.ctl

load data infile '/tmp/tpc/store_sales.dat' into table store_sales fields terminated by '|' ( SS_SOLD_DATE_SK, SS_SOLD_TIME_SK, SS_ITEM_SK, SS_CUSTOMER_SK, SS_CDEMO_SK, SS_HDEMO_SK, SS_ADDR_SK, SS_STORE_SK, SS_PROMO_SK, SS_TICKET_NUMBER, SS_QUANTITY, SS_WHOLESALE_COST, SS_LIST_PRICE, SS_SALES_PRICE, SS_EXT_DISCOUNT_AMT, SS_EXT_SALES_PRICE, SS_EXT_WHOLESALE_COST, SS_EXT_LIST_PRICE, SS_EXT_TAX, SS_COUPON_AMT, SS_NET_PAID, SS_NET_PAID_INC_TAX, SS_NET_PROFIT )

1.  
2. load data
3. infile '/tmp/tpc/store_sales.dat'
4. into table store_sales
5. fields terminated by '|'
6. ( SS_SOLD_DATE_SK, SS_SOLD_TIME_SK, SS_ITEM_SK, SS_CUSTOMER_SK, SS_CDEMO_SK, SS_HDEMO_SK, SS_ADDR_SK, SS_STORE_SK, SS_PROMO_SK, SS_TICKET_NUMBER, SS_QUANTITY, SS_WHOLESALE_COST, SS_LIST_PRICE, SS_SALES_PRICE, SS_EXT_DISCOUNT_AMT, SS_EXT_SALES_PRICE, SS_EXT_WHOLESALE_COST, SS_EXT_LIST_PRICE, SS_EXT_TAX, SS_COUPON_AMT, SS_NET_PAID, SS_NET_PAID_INC_TAX, SS_NET_PROFIT )
7.  

• vi time_dim.ctl

time_dim.ctl

load data infile '/tmp/tpc/time_dim.dat' into table time_dim fields terminated by '|' ( T_TIME_SK,T_TIME_ID,T_TIME,T_HOUR,T_MINUTE,T_SECOND,T_AM_PM,T_SHIFT,T_SUB_SHIFT,T_MEAL_TIME )

1.  
2. load data
3. infile '/tmp/tpc/time_dim.dat'
4. into table time_dim
5. fields terminated by '|'
6. ( T_TIME_SK,T_TIME_ID,T_TIME,T_HOUR,T_MINUTE,T_SECOND,T_AM_PM,T_SHIFT,T_SUB_SHIFT,T_MEAL_TIME )
7.  

• vi warehouse.ctl

warehouse.ctl

load data infile '/tmp/tpc/warehouse.dat' into table warehouse fields terminated by '|' ( W_WAREHOUSE_SK,W_WAREHOUSE_ID,W_WAREHOUSE_NAME,W_WAREHOUSE_SQ_FT,W_STREET_NUMBER,W_STREET_NAME,W_STREET_TYPE,W_SUITE_NUMBER,W_CITY,W_COUNTY,W_STATE,W_ZIP,W_COUNTRY,W_GMT_OFFSET )

1.  
2. load data
3. infile '/tmp/tpc/warehouse.dat'
4. into table warehouse
5. fields terminated by '|'
6. ( W_WAREHOUSE_SK,W_WAREHOUSE_ID,W_WAREHOUSE_NAME,W_WAREHOUSE_SQ_FT,W_STREET_NUMBER,W_STREET_NAME,W_STREET_TYPE,W_SUITE_NUMBER,W_CITY,W_COUNTY,W_STATE,W_ZIP,W_COUNTRY,W_GMT_OFFSET )
7.  

• vi web_page.ctl

web_page.ctl

load data infile '/tmp/tpc/web_page.dat' into table web_page fields terminated by '|' ( WP_WEB_PAGE_SK, WP_WEB_PAGE_ID, WP_REC_START_DATE date "YYYY-MM-DD", WP_REC_END_DATE date "YYYY-MM-DD", WP_CREATION_DATE_SK, WP_ACCESS_DATE_SK, WP_AUTOGEN_FLAG, WP_CUSTOMER_SK, WP_URL, WP_TYPE, WP_CHAR_COUNT, WP_LINK_COUNT, WP_IMAGE_COUNT, WP_MAX_AD_COUNT )

1.  
2. load data
3. infile '/tmp/tpc/web_page.dat'
4. into table web_page
5. fields terminated by '|'
6. ( WP_WEB_PAGE_SK, WP_WEB_PAGE_ID, WP_REC_START_DATE date "YYYY-MM-DD", WP_REC_END_DATE date "YYYY-MM-DD", WP_CREATION_DATE_SK, WP_ACCESS_DATE_SK, WP_AUTOGEN_FLAG, WP_CUSTOMER_SK, WP_URL, WP_TYPE, WP_CHAR_COUNT, WP_LINK_COUNT, WP_IMAGE_COUNT, WP_MAX_AD_COUNT )
7.  

• vi web_returns.ctl

web_returns.ctl

load data infile '/tmp/tpc/web_returns.dat' into table web_returns fields terminated by '|' ( WR_RETURNED_DATE_SK, WR_RETURNED_TIME_SK, WR_ITEM_SK, WR_REFUNDED_CUSTOMER_SK, WR_REFUNDED_CDEMO_SK, WR_REFUNDED_HDEMO_SK, WR_REFUNDED_ADDR_SK, WR_RETURNING_CUSTOMER_SK, WR_RETURNING_CDEMO_SK, WR_RETURNING_HDEMO_SK, WR_RETURNING_ADDR_SK, WR_WEB_PAGE_SK, WR_REASON_SK, WR_ORDER_NUMBER, WR_RETURN_QUANTITY, WR_RETURN_AMT, WR_RETURN_TAX, WR_RETURN_AMT_INC_TAX, WR_FEE, WR_RETURN_SHIP_COST, WR_REFUNDED_CASH, WR_REVERSED_CHARGE, WR_ACCOUNT_CREDIT, WR_NET_LOSS )

1.  
2. load data
3. infile '/tmp/tpc/web_returns.dat'
4. into table web_returns
5. fields terminated by '|'
6. ( WR_RETURNED_DATE_SK, WR_RETURNED_TIME_SK, WR_ITEM_SK, WR_REFUNDED_CUSTOMER_SK, WR_REFUNDED_CDEMO_SK, WR_REFUNDED_HDEMO_SK, WR_REFUNDED_ADDR_SK, WR_RETURNING_CUSTOMER_SK, WR_RETURNING_CDEMO_SK, WR_RETURNING_HDEMO_SK, WR_RETURNING_ADDR_SK, WR_WEB_PAGE_SK, WR_REASON_SK, WR_ORDER_NUMBER, WR_RETURN_QUANTITY, WR_RETURN_AMT, WR_RETURN_TAX, WR_RETURN_AMT_INC_TAX, WR_FEE, WR_RETURN_SHIP_COST, WR_REFUNDED_CASH, WR_REVERSED_CHARGE, WR_ACCOUNT_CREDIT, WR_NET_LOSS )
7.  

• vi web_sales.ctl

web_sales.ctl

load data infile '/tmp/tpc/web_sales.dat' into table web_sales fields terminated by '|' ( WS_SOLD_DATE_SK, WS_SOLD_TIME_SK, WS_SHIP_DATE_SK, WS_ITEM_SK, WS_BILL_CUSTOMER_SK, WS_BILL_CDEMO_SK, WS_BILL_HDEMO_SK, WS_BILL_ADDR_SK, WS_SHIP_CUSTOMER_SK, WS_SHIP_CDEMO_SK, WS_SHIP_HDEMO_SK, WS_SHIP_ADDR_SK, WS_WEB_PAGE_SK, WS_WEB_SITE_SK, WS_SHIP_MODE_SK, WS_WAREHOUSE_SK, WS_PROMO_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST, WS_LIST_PRICE, WS_SALES_PRICE, WS_EXT_DISCOUNT_AMT, WS_EXT_SALES_PRICE, WS_EXT_WHOLESALE_COST, WS_EXT_LIST_PRICE, WS_EXT_TAX, WS_COUPON_AMT, WS_EXT_SHIP_COST, WS_NET_PAID, WS_NET_PAID_INC_TAX, WS_NET_PAID_INC_SHIP, WS_NET_PAID_INC_SHIP_TAX, WS_NET_PROFIT )

1.  
2. load data
3. infile '/tmp/tpc/web_sales.dat'
4. into table web_sales
5. fields terminated by '|'
6. ( WS_SOLD_DATE_SK, WS_SOLD_TIME_SK, WS_SHIP_DATE_SK, WS_ITEM_SK, WS_BILL_CUSTOMER_SK, WS_BILL_CDEMO_SK, WS_BILL_HDEMO_SK, WS_BILL_ADDR_SK, WS_SHIP_CUSTOMER_SK, WS_SHIP_CDEMO_SK, WS_SHIP_HDEMO_SK, WS_SHIP_ADDR_SK, WS_WEB_PAGE_SK, WS_WEB_SITE_SK, WS_SHIP_MODE_SK, WS_WAREHOUSE_SK, WS_PROMO_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST, WS_LIST_PRICE, WS_SALES_PRICE, WS_EXT_DISCOUNT_AMT, WS_EXT_SALES_PRICE, WS_EXT_WHOLESALE_COST, WS_EXT_LIST_PRICE, WS_EXT_TAX, WS_COUPON_AMT, WS_EXT_SHIP_COST, WS_NET_PAID, WS_NET_PAID_INC_TAX, WS_NET_PAID_INC_SHIP, WS_NET_PAID_INC_SHIP_TAX, WS_NET_PROFIT )
7.  

• vi web_site.ctl

web_site.ctl

load data infile '/tmp/tpc/web_site.dat' into table web_site fields terminated by '|' ( WEB_SITE_SK, WEB_SITE_ID, WEB_REC_START_DATE date "YYYY-MM-DD", WEB_REC_END_DATE date "YYYY-MM-DD", WEB_NAME, WE B_OPEN_DATE_SK, WEB_CLOSE_DATE_SK, WEB_CLASS, WEB_MANAGER, WEB_MKT_ID, WEB_MKT_CLASS, WEB_MKT_DESC, WEB_MARKET_MAN AGER, WEB_COMPANY_ID, WEB_COMPANY_NAME, WEB_STREET_NUMBER, WEB_STREET_NAME, WEB_STREET_TYPE, WEB_SUITE_NUMBER, WEB _CITY, WEB_COUNTY, WEB_STATE, WEB_ZIP, WEB_COUNTRY, WEB_GMT_OFFSET, WEB_TAX_PERCENTAGE )

1.  
2. load data
3. infile '/tmp/tpc/web_site.dat'
4. into table web_site
5. fields terminated by '|'
6. ( WEB_SITE_SK, WEB_SITE_ID, WEB_REC_START_DATE date "YYYY-MM-DD", WEB_REC_END_DATE date "YYYY-MM-DD", WEB_NAME, WE
7. B_OPEN_DATE_SK, WEB_CLOSE_DATE_SK, WEB_CLASS, WEB_MANAGER, WEB_MKT_ID, WEB_MKT_CLASS, WEB_MKT_DESC, WEB_MARKET_MAN
8. AGER, WEB_COMPANY_ID, WEB_COMPANY_NAME, WEB_STREET_NUMBER, WEB_STREET_NAME, WEB_STREET_TYPE, WEB_SUITE_NUMBER, WEB
9. _CITY, WEB_COUNTY, WEB_STATE, WEB_ZIP, WEB_COUNTRY, WEB_GMT_OFFSET, WEB_TAX_PERCENTAGE )
10.  

Import the data using Oracle Loader

• sqlldr tpcds/* control=call_center.ctl
• sqlldr tpcds/* control=catalog_page.ctl
• sqlldr tpcds/* control=catalog_returns.ctl
• sqlldr tpcds/* control=catalog_sales.ctl
• sqlldr tpcds/* control=customer_address.ctl
• sqlldr tpcds/* control=customer.ctl
• sqlldr tpcds/* control=customer_demographics.ctl
• sqlldr tpcds/* control=date_dim.ctl
• sqlldr tpcds/* control=dbgen_version.ctl
• sqlldr tpcds/* control=household_demographics.ctl
• sqlldr tpcds/* control=income_band.ctl
• sqlldr tpcds/* control=inventory.ctl
• sqlldr tpcds/* control=item.ctl
• sqlldr tpcds/* control=promotion.ctl
• sqlldr tpcds/* control=reason.ctl
• sqlldr tpcds/* control=ship_mode.ctl
• sqlldr tpcds/* control=store.ctl
• sqlldr tpcds/* control=store_returns.ctl
• sqlldr tpcds/* control=store_sales.ctl
• sqlldr tpcds/* control=time_dim.ctl
• sqlldr tpcds/* control=warehouse.ctl
• sqlldr tpcds/* control=web_page.ctl
• sqlldr tpcds/* control=web_returns.ctl
• sqlldr tpcds/* control=web_sales.ctl
• sqlldr tpcds/* control=web_site.ctl

http://www.tpc.org/tpcds/default.asp
http://agni.csa.iisc.ernet.in/CASL/tpcds
http://www.orafaq.com/wiki/SQL*Loader_FAQ