Wednesday, August 24, 2011

Swingbench Steps and Scripts for - DSS schema creation

Steps and Scripts for Swingbench- DSS schema creation

"swingbench" is a popular tool for generating stress on oracle database. Here are the steps to configure the schema that could simulate DSS type stress.
Assumption is reader has downloaded the tool "datagenerator" and using the same generated dat and ctl files for SQL*Loader.

create bigfile tablespace shdata datafile '/mnt_sapt/ora_data/swing_data/db1/shdata.dbf'
size 10000M
autoextend on next 64M maxsize unlimited
extent management local uniform size 1M
segment space management auto;


create bigfile tablespace shindexes datafile '/mnt_sapt/ora_data/swing_data/db1/shindexes.dbf'
size 5000M
autoextend on next 64M maxsize unlimited
extent management local uniform size 1M
segment space management auto;


CREATE USER sh IDENTIFIED BY sh;

ALTER USER sh DEFAULT TABLESPACE shdata QUOTA UNLIMITED ON shdata;

ALTER USER sh TEMPORARY TABLESPACE TEMP;

GRANT CONNECT TO sh;

GRANT RESOURCE TO sh;

GRANT CREATE VIEW to sh;

GRANT CREATE MATERIALIZED VIEW  TO sh;

GRANT QUERY REWRITE TO sh;

GRANT ALTER SESSION TO sh;

GRANT EXECUTE ON dbms_lock TO sh;

===========================



=========================


drop table CHANNELS purge;

drop table COUNTRIES purge;

drop table CUSTOMERS purge;

drop table PROMOTIONS purge;

drop table PRODUCTS purge;

drop table SUPPLEMENTARY_DEMOGRAPHICS purge;

drop table SALES purge;

  CREATE TABLE SALES
   (PROD_ID NUMBER NOT NULL ENABLE,
    CUST_ID NUMBER NOT NULL ENABLE,
    TIME_ID DATE NOT NULL ENABLE,
    CHANNEL_ID NUMBER NOT NULL ENABLE,
    PROMO_ID NUMBER NOT NULL ENABLE,
    QUANTITY_SOLD NUMBER(10,2) NOT NULL ENABLE,
    AMOUNT_SOLD NUMBER(10,2) NOT NULL ENABLE
   )
   PARTITION BY RANGE (TIME_ID)
  (PARTITION SALES_1995  VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_1996  VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_H1_1997  VALUES LESS THAN (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_H2_1997  VALUES LESS THAN (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_1998  VALUES LESS THAN (TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_1998  VALUES LESS THAN (TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_1998  VALUES LESS THAN (TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_1998  VALUES LESS THAN (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_1999  VALUES LESS THAN (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_1999  VALUES LESS THAN (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_1999  VALUES LESS THAN (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_1999  VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2000  VALUES LESS THAN (TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2000  VALUES LESS THAN (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2000  VALUES LESS THAN (TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2000  VALUES LESS THAN (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2001  VALUES LESS THAN (TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2001  VALUES LESS THAN (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2001  VALUES LESS THAN (TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2001  VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2002  VALUES LESS THAN (TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2002  VALUES LESS THAN (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2002  VALUES LESS THAN (TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2002  VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2003  VALUES LESS THAN (TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2003  VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2003  VALUES LESS THAN (TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2003  VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2004  VALUES LESS THAN (TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2004  VALUES LESS THAN (TO_DATE(' 2004-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2004  VALUES LESS THAN (TO_DATE(' 2004-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2004  VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2005  VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2005  VALUES LESS THAN (TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2005  VALUES LESS THAN (TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2005  VALUES LESS THAN (TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2006  VALUES LESS THAN (TO_DATE(' 2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2006  VALUES LESS THAN (TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2006  VALUES LESS THAN (TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2006  VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2007  VALUES LESS THAN (TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2007  VALUES LESS THAN (TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2007  VALUES LESS THAN (TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2007  VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2008  VALUES LESS THAN (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2008  VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2008  VALUES LESS THAN (TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2008  VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q1_2009  VALUES LESS THAN (TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q2_2009  VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q3_2009  VALUES LESS THAN (TO_DATE(' 2009-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ,
  PARTITION SALES_Q4_2009  VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  );





  CREATE TABLE CHANNELS
   (    CHANNEL_ID NUMBER NOT NULL ENABLE,
    CHANNEL_DESC VARCHAR2(20 BYTE) NOT NULL ENABLE,
    CHANNEL_CLASS VARCHAR2(20 BYTE) NOT NULL ENABLE,
    CHANNEL_CLASS_ID NUMBER NOT NULL ENABLE,
    CHANNEL_TOTAL VARCHAR2(13 BYTE) NOT NULL ENABLE,
    CHANNEL_TOTAL_ID NUMBER NOT NULL ENABLE
   ) ;


  CREATE TABLE COUNTRIES
   (    COUNTRY_ID NUMBER NOT NULL ENABLE,
    COUNTRY_ISO_CODE CHAR(2 BYTE) NOT NULL ENABLE,
    COUNTRY_NAME VARCHAR2(40 BYTE) NOT NULL ENABLE,
    COUNTRY_SUBREGION VARCHAR2(30 BYTE) NOT NULL ENABLE,
    COUNTRY_SUBREGION_ID NUMBER NOT NULL ENABLE,
    COUNTRY_REGION VARCHAR2(20 BYTE) NOT NULL ENABLE,
    COUNTRY_REGION_ID NUMBER NOT NULL ENABLE,
    COUNTRY_TOTAL VARCHAR2(11 BYTE) NOT NULL ENABLE,
    COUNTRY_TOTAL_ID NUMBER NOT NULL ENABLE,
    COUNTRY_NAME_HIST VARCHAR2(40 BYTE)
   ) ;


  CREATE TABLE CUSTOMERS
   (    CUST_ID NUMBER NOT NULL ENABLE,
    CUST_FIRST_NAME VARCHAR2(20 BYTE) NOT NULL ENABLE,
    CUST_LAST_NAME VARCHAR2(40 BYTE) NOT NULL ENABLE,
    CUST_GENDER CHAR(1 BYTE) NOT NULL ENABLE,
    CUST_YEAR_OF_BIRTH NUMBER(4,0) NOT NULL ENABLE,
    CUST_MARITAL_STATUS VARCHAR2(20 BYTE),
    CUST_STREET_ADDRESS VARCHAR2(40 BYTE) NOT NULL ENABLE,
    CUST_POSTAL_CODE VARCHAR2(10 BYTE) NOT NULL ENABLE,
    CUST_CITY VARCHAR2(30 BYTE) NOT NULL ENABLE,
    CUST_CITY_ID NUMBER NOT NULL ENABLE,
    CUST_STATE_PROVINCE VARCHAR2(40 BYTE) NOT NULL ENABLE,
    CUST_STATE_PROVINCE_ID NUMBER NOT NULL ENABLE,
    COUNTRY_ID NUMBER NOT NULL ENABLE,
    CUST_MAIN_PHONE_NUMBER VARCHAR2(25 BYTE) NOT NULL ENABLE,
    CUST_INCOME_LEVEL VARCHAR2(30 BYTE),
    CUST_CREDIT_LIMIT NUMBER,
    CUST_EMAIL VARCHAR2(30 BYTE),
    CUST_TOTAL VARCHAR2(14 BYTE) NOT NULL ENABLE,
    CUST_TOTAL_ID NUMBER NOT NULL ENABLE,
    CUST_SRC_ID NUMBER,
    CUST_EFF_FROM DATE,
    CUST_EFF_TO DATE,
    CUST_VALID VARCHAR2(1 BYTE)   
   ) ;


  CREATE TABLE PROMOTIONS
   (    PROMO_ID NUMBER(6,0) NOT NULL ENABLE,
    PROMO_NAME VARCHAR2(30 BYTE) NOT NULL ENABLE,
    PROMO_SUBCATEGORY VARCHAR2(30 BYTE) NOT NULL ENABLE,
    PROMO_SUBCATEGORY_ID NUMBER NOT NULL ENABLE,
    PROMO_CATEGORY VARCHAR2(30 BYTE) NOT NULL ENABLE,
    PROMO_CATEGORY_ID NUMBER NOT NULL ENABLE,
    PROMO_COST NUMBER(10,2) NOT NULL ENABLE,
    PROMO_BEGIN_DATE DATE NOT NULL ENABLE,
    PROMO_END_DATE DATE NOT NULL ENABLE,
    PROMO_TOTAL VARCHAR2(15 BYTE) NOT NULL ENABLE,
    PROMO_TOTAL_ID NUMBER NOT NULL ENABLE
   ) ;



  CREATE TABLE PRODUCTS
   (    PROD_ID NUMBER(6,0) NOT NULL ENABLE,
    PROD_NAME VARCHAR2(50 BYTE) NOT NULL ENABLE,
    PROD_DESC VARCHAR2(4000 BYTE) NOT NULL ENABLE,
    PROD_SUBCATEGORY VARCHAR2(50 BYTE) NOT NULL ENABLE,
    PROD_SUBCATEGORY_ID NUMBER NOT NULL ENABLE,
    PROD_SUBCATEGORY_DESC VARCHAR2(2000 BYTE) NOT NULL ENABLE,
    PROD_CATEGORY VARCHAR2(50 BYTE) NOT NULL ENABLE,
    PROD_CATEGORY_ID NUMBER NOT NULL ENABLE,
    PROD_CATEGORY_DESC VARCHAR2(2000 BYTE) NOT NULL ENABLE,
    PROD_WEIGHT_CLASS NUMBER(3,0) NOT NULL ENABLE,
    PROD_UNIT_OF_MEASURE VARCHAR2(20 BYTE),
    PROD_PACK_SIZE VARCHAR2(30 BYTE) NOT NULL ENABLE,
    SUPPLIER_ID NUMBER(6,0) NOT NULL ENABLE,
    PROD_STATUS VARCHAR2(20 BYTE) NOT NULL ENABLE,
    PROD_LIST_PRICE NUMBER(8,2) NOT NULL ENABLE,
    PROD_MIN_PRICE NUMBER(8,2) NOT NULL ENABLE,
    PROD_TOTAL VARCHAR2(13 BYTE) NOT NULL ENABLE,
    PROD_TOTAL_ID NUMBER NOT NULL ENABLE,
    PROD_SRC_ID NUMBER,
    PROD_EFF_FROM DATE,
    PROD_EFF_TO DATE,
    PROD_VALID VARCHAR2(1 BYTE)
   ) ;



  CREATE TABLE SUPPLEMENTARY_DEMOGRAPHICS
   (    CUST_ID NUMBER NOT NULL ENABLE,
    EDUCATION VARCHAR2(21 BYTE),
    OCCUPATION VARCHAR2(21 BYTE),
    HOUSEHOLD_SIZE VARCHAR2(21 BYTE),
    YRS_RESIDENCE NUMBER,
    AFFINITY_CARD NUMBER(10,0),
    BULK_PACK_DISKETTES NUMBER(10,0),
    FLAT_PANEL_MONITOR NUMBER(10,0),
    HOME_THEATER_PACKAGE NUMBER(10,0),
    BOOKKEEPING_APPLICATION NUMBER(10,0),
    PRINTER_SUPPLIES NUMBER(10,0),
    Y_BOX_GAMES NUMBER(10,0),
    OS_DOC_SET_KANJI NUMBER(10,0),
    COMMENTS VARCHAR2(4000 BYTE)
   ) ;


CREATE TABLE times AS
SELECT udate time_id,
  TO_CHAR(udate,'Day') day_name,
  TO_CHAR(udate,'DD') day_number_in_month,
  TO_CHAR(udate,'DDD') day_number_in_year,
  TO_CHAR(udate,'YYYY' ) calendar_year,
  TO_CHAR(udate,'Q' ) calendar_quarter_number,
  TO_CHAR(udate,'MM' ) calendar_month_number,
  TO_CHAR(udate,'WW' ) calendar_week_number,
  TO_CHAR(udate,'YYYY-MM') calendar_month_desc,
  TO_CHAR(udate,'YYYY-Q') calendar_quarter_desc
FROM
  (SELECT to_date('31/12/1994','DD/MM/YYYY')+rownum udate
  FROM all_objects
  WHERE to_date('31/12/1994','DD/MM/YYYY')+rownum <= to_date( '31/12/2009','DD/MM/YYYY')
  )
 
 -- End; 


============

SQL> select  TABLE_NAME from cat;

TABLE_NAME
------------------------------
CHANNELS - done
COUNTRIES - done
CUSTOMERS - done
CUST_SEQ - n/a
PRODUCTS - done
PROMOTIONS - done
SALES -  done
SEQUENCE1 - n/a
SIMPLETABLE
SUPPLEMENTARY_DEMOGRAPHICS
SUPP_SEQ n/a

sqllder sh/sh control=SIMPLETABLE.ctl

sqlldr sh/sh control=PROMOTIONS.ctl

sqlldr sh/sh control=CHANNELS.ctl

sqlldr sh/sh control=COUNTRIES.ctl

sqlldr sh/sh control=CUSTOMERS.ctl

sqlldr sh/sh control=PRODUCTS.ctl

sqlldr sh/sh control=SALES.ctl



sqlldr sh/sh control=CUSTOMERS_0.ctl
sqlldr sh/sh control=CUSTOMERS_333336.ctl 
sqlldr sh/sh control=CUSTOMERS_541671.ctl 
sqlldr sh/sh control=CUSTOMERS_791673.ctl
sqlldr sh/sh control=CUSTOMERS_125001.ctl 
sqlldr sh/sh control=CUSTOMERS_375003.ctl 
sqlldr sh/sh control=CUSTOMERS_583338.ctl 
sqlldr sh/sh control=CUSTOMERS_83334.ctl
sqlldr sh/sh control=CUSTOMERS_166668.ctl 
sqlldr sh/sh control=CUSTOMERS_41667.ctl  
sqlldr sh/sh control=CUSTOMERS_625005.ctl 
sqlldr sh/sh control=CUSTOMERS_833340.ctl
sqlldr sh/sh control=CUSTOMERS_208335.ctl 
sqlldr sh/sh control=CUSTOMERS_416670.ctl 
sqlldr sh/sh control=CUSTOMERS_666672.ctl 
sqlldr sh/sh control=CUSTOMERS_875007.ctl
sqlldr sh/sh control=CUSTOMERS_250002.ctl 
sqlldr sh/sh control=CUSTOMERS_458337.ctl 
sqlldr sh/sh control=CUSTOMERS_708339.ctl 
sqlldr sh/sh control=CUSTOMERS_916674.ctl
sqlldr sh/sh control=CUSTOMERS_291669.ctl 
sqlldr sh/sh control=CUSTOMERS_500004.ctl 
sqlldr sh/sh control=CUSTOMERS_750006.ctl 
sqlldr sh/sh control=CUSTOMERS_958341.ctl




sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_0.ctl      
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_541671.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_125001.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_583338.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_166668.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_625005.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_208335.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_666672.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_250002.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_708339.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_291669.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_750006.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_333336.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_791673.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_375003.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_83334.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_41667.ctl  
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_833340.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_416670.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_875007.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_458337.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_916674.ctl
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_500004.ctl 
sqlldr sh/sh control=SUPPLEMENTARY_DEMOGRAPHICS_958341.ctl


sqlldr sh/sh control=SALES_1010777400000.ctl 
sqlldr sh/sh control=SALES_1173474000000.ctl 
sqlldr sh/sh control=SALES_862871400000.ctl
sqlldr sh/sh control=SALES_1025568000000.ctl 
sqlldr sh/sh control=SALES_1188264600000.ctl 
sqlldr sh/sh control=SALES_877662000000.ctl
sqlldr sh/sh control=SALES_1040358600000.ctl 
sqlldr sh/sh control=SALES_1203055200000.ctl 
sqlldr sh/sh control=SALES_892452600000.ctl
sqlldr sh/sh control=SALES_1055149200000.ctl 
sqlldr sh/sh control=SALES_1217845800000.ctl 
sqlldr sh/sh control=SALES_907243200000.ctl
sqlldr sh/sh control=SALES_1069939800000.ctl 
sqlldr sh/sh control=SALES_1232636400000.ctl 
sqlldr sh/sh control=SALES_922033800000.ctl
sqlldr sh/sh control=SALES_1084730400000.ctl 
sqlldr sh/sh control=SALES_1247427000000.ctl 
sqlldr sh/sh control=SALES_936824400000.ctl
sqlldr sh/sh control=SALES_1099521000000.ctl 
sqlldr sh/sh control=SALES_788918400000.ctl  
sqlldr sh/sh control=SALES_951615000000.ctl
sqlldr sh/sh control=SALES_1114311600000.ctl 
sqlldr sh/sh control=SALES_803709000000.ctl  
sqlldr sh/sh control=SALES_966405600000.ctl
sqlldr sh/sh control=SALES_1129102200000.ctl 
sqlldr sh/sh control=SALES_818499600000.ctl  
sqlldr sh/sh control=SALES_981196200000.ctl
sqlldr sh/sh control=SALES_1143892800000.ctl 
sqlldr sh/sh control=SALES_833290200000.ctl  
sqlldr sh/sh control=SALES_995986800000.ctl
sqlldr sh/sh control=SALES_1158683400000.ctl 
sqlldr sh/sh control=SALES_848080800000.ctl

-- Table constraints
--
-- Generated by datagenerator. www.dominicgiles.com
--
alter table COUNTRIES add constraint COUNTRIES_pk primary key(COUNTRY_ID);
;
;
alter table CUSTOMERS add constraint CUSTOMERS_pk primary key(CUST_ID);
alter table CHANNELS add constraint CHANNELS_pk primary key(CHANNEL_ID);
alter table PRODUCTS add constraint PRODUCTS_pk primary key(PROD_ID);
alter table PROMOTIONS add constraint PROMOTIONS_pk primary key(PROMO_ID);
~

==========

REM SH CUSTOMERS_MARITAL_BIX

CREATE BITMAP INDEX CUSTOMERS_MARITAL_BIX ON CUSTOMERS (CUST_MARITAL_STATUS) TABLESPACE shindexes;

REM SH CUSTOMERS_YOB_BIX

CREATE BITMAP INDEX CUSTOMERS_YOB_BIX ON CUSTOMERS (CUST_YEAR_OF_BIRTH) TABLESPACE shindexes;

REM SH CUSTOMERS_GENDER_BIX

CREATE BITMAP INDEX CUSTOMERS_GENDER_BIX ON CUSTOMERS (CUST_GENDER) TABLESPACE shindexes;


REM SH PRODUCTS_PROD_STATUS_BIX

CREATE BITMAP INDEX PRODUCTS_PROD_STATUS_BIX ON PRODUCTS (PROD_STATUS) TABLESPACE shindexes;


REM SH PRODUCTS_PROD_SUBCAT_IX

CREATE INDEX PRODUCTS_PROD_SUBCAT_IX ON PRODUCTS (PROD_SUBCATEGORY) TABLESPACE shindexes;

REM SH PRODUCTS_PROD_CAT_IX

CREATE INDEX PRODUCTS_PROD_CAT_IX ON PRODUCTS (PROD_CATEGORY) TABLESPACE shindexes;



REM SH SALES_PROD_BIX

CCREATE BITMAP INDEX SALES_PROD_BIX ON SALES (PROD_ID) TABLESPACE shindexes  LOCAL;

REM SH SALES_CUST_BIX

CREATE BITMAP INDEX SALES_CUST_BIX ON SALES (CUST_ID) TABLESPACE shindexes  LOCAL;

REM SH SALES_TIME_BIX

-- CREATE BITMAP INDEX SALES_TIME_BIX ON SALES (TIME_ID) TABLESPACE shindexes  LOCAL;

REM SH SALES_CHANNEL_BIX

CREATE BITMAP INDEX SALES_CHANNEL_BIX ON SALES (CHANNEL_ID) TABLESPACE shindexes  LOCAL;

REM SH SALES_PROMO_BIX

CREATE BITMAP INDEX SALES_PROMO_BIX ON SALES (PROMO_ID) TABLESPACE shindexes  LOCAL;

-- End;


-- End;
Say, your host name = testserver
IP= 172.21.19.226
PORT=1521
Database name / SID= db1

Hence the charbench syntax goes as:

./charbench -c sample/shconfig.xml -cs //172.21.19.226:1521/db1 -dt thin -cpuloc testserver -uc 20 -min 0 -max 100 -a -v users,tpm,tps,cpu

No comments:

Post a Comment