DROP TABLE C_customerinventory;
CREATE TABLE C_customerinventory
(ci_id                integer not null,
 ci_itemid    char(20),
 ci_quantity  integer,
 ci_customerid        integer,
 ci_value             numeric(12,2)
)
PARTITION by HASH (ci_customerid)
PARTITIONS 256
STORE IN (C_custinv_space)
INITRANS 20
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8);


DROP TABLE C_customer;
CREATE TABLE C_customer
(
	c_id		integer not null,
	c_first		char(16),
	c_last		char(16),
	c_street1	char(20),	
	c_street2	char(20),	
	c_city		char(20),	
	c_state		char(2),	
	c_country	char(10),	
	c_zip		char(9),	
	c_phone		char(16),
	c_contact	char(25),	
	c_since		date,
	c_balance	numeric(12,2),
	c_credit	char(2),
	c_credit_limit	numeric(12,2),
	c_ytd_payment	numeric(12,2)
)
PARTITION by HASH (c_id)
PARTITIONS 64
STORE IN (C_cust_space)
INITRANS 20
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8);


DROP TABLE C_supplier;
CREATE TABLE C_supplier
(
	supp_id			integer not null,
	supp_name		char(16),
	supp_street1	char(20),	
	supp_street2	char(20),	
	supp_city		char(20),	
	supp_state		char(2),	
	supp_country	char(10),	
	supp_zip		char(9),	
	supp_phone		char(16),
	supp_contact	char(25)
)
TABLESPACE C_supp_space
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
;

DROP TABLE C_site;
CREATE TABLE C_site
(
	site_id			integer not null,
	site_name		char(16),
	site_street1	char(20),	
	site_street2	char(20),	
	site_city		char(20),	
	site_state		char(2),	
	site_country	char(10),	
	site_zip		char(9)
)
TABLESPACE C_site_space
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);


DROP TABLE C_parts;
CREATE TABLE C_parts
(
	p_id			char(20) not null,
	p_name			char(35),
	p_desc			varchar(100),
	p_rev			char(6),
	p_unit			char(10),
	p_cost			numeric(12,2),
	p_price			numeric(12,2),
	p_planner		integer,
	p_type			integer,
	p_ind			integer,
        p_lomark                integer,
        p_himark                integer
)
TABLESPACE C_parts_space
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

CREATE UNIQUE INDEX C_ci_idx ON C_customerinventory(ci_id)
TABLESPACE C_custinv_space
INITRANS 100
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) GLOBAL PARTITION BY HASH(ci_id)  PARTITIONS 512 PARALLEL 80;
ALTER INDEX C_ci_idx NOPARALLEL;

CREATE INDEX C_ci_custid_idx ON C_customerinventory (ci_customerid)
TABLESPACE C_custinv_space
INITRANS 100
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) LOCAL PARALLEL 79;
ALTER INDEX C_ci_custid_idx NOPARALLEL;


CREATE UNIQUE INDEX C_c_idx ON C_customer (c_id)
TABLESPACE C_cust_space
INITRANS 40
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) LOCAL;

CREATE UNIQUE INDEX C_supp_idx ON C_supplier (supp_id)
TABLESPACE C_supp_space
INITRANS 10
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

CREATE UNIQUE INDEX C_site_idx ON C_site (site_id)
TABLESPACE C_site_space
INITRANS 10
STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 0);

CREATE UNIQUE INDEX C_p_idx ON C_parts (p_id)
TABLESPACE C_parts_space
INITRANS 10
STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 0);

