DROP CLUSTER M_parts_clus including tables;
CREATE CLUSTER M_parts_clus ( p_id  char(20) )
single table
hashkeys 26400000
size 200
TABLESPACE M_parts_space;

DROP TABLE M_parts;
CREATE TABLE M_parts
(
	p_id			char(20) not null,
	p_name			char(35),
	p_desc			varchar(100),
	p_rev			char(6),
	p_planner		integer,
	p_type			integer,
	p_ind			integer,
	p_lomark		integer,
	p_himark		integer
)
CLUSTER M_parts_clus ( p_id );


DROP CLUSTER M_bom_clus including tables;
CREATE CLUSTER M_bom_clus ( b_assembly_id char(20) )
single table
hashkeys 24000000 
size 150
TABLESPACE M_bom_space;

DROP TABLE M_bom;
CREATE TABLE M_bom
(
	b_comp_id		char(20) not null,
	b_assembly_id		char(20) not null,
	b_line_no		integer,
	b_qty			integer,
	b_ops			integer,	
	b_eng_change		char(10),	
	b_ops_desc		varchar(100)
)
CLUSTER M_bom_clus (b_assembly_id );


DROP TABLE M_workorder;
CREATE TABLE M_workorder
(
	wo_number		integer not null,
	wo_o_id			integer,
	wo_ol_id		integer,
	wo_status		integer,
	wo_assembly_id	char(20),
	wo_orig_qty		integer,
	wo_comp_qty		integer,
	wo_due_date		date,
	wo_start_date		timestamp
)
PARTITION BY HASH (wo_number)
PARTITIONS 1024
STORE IN (M_wo_space)
INITRANS 30
PCTFREE 80
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8 buffer_pool recycle);


DROP TABLE M_largeorder;
CREATE TABLE M_largeorder
(
	lo_id			integer not null,
	lo_o_id			integer,
	lo_ol_id		integer,
	lo_assembly_id	char(20),
	lo_qty			integer,
	lo_due_date		date,
        lo_category             integer
)
PARTITION BY HASH(lo_o_id)
PARTITIONS 256
STORE IN(M_LO_SPACE)
INITRANS 30
PCTFREE 50
PCTUSED 50
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 );

DROP TABLE M_inventory;
DROP CLUSTER M_inv_clus including tables;
CREATE CLUSTER M_inv_clus ( in_p_id  char(20) )
single table
hashkeys 26400000
size 220
TABLESPACE M_inv_space
storage (buffer_pool default);

CREATE TABLE M_inventory
(
        in_p_id                 char(20) not null,
        in_qty                  integer,
        in_ordered              integer,
        in_location             char(20),
        in_acc_code             integer,
        in_act_date             date
)
CLUSTER M_inv_clus ( in_p_id );

drop index  M_parts_idx;
drop index  M_bom_idx;
drop index  M_wo_idx;
drop index  M_lo_idx;
drop index  M_OL_O_idx;
drop index  M_inv_idx;

CREATE UNIQUE INDEX M_parts_idx ON M_parts (p_id)
TABLESPACE M_parts_space
INITRANS 30
STORAGE (INITIAL 10K NEXT 1K PCTINCREASE 0);


CREATE UNIQUE INDEX M_bom_idx ON M_bom (b_assembly_id, b_comp_id, b_line_no)
TABLESPACE M_bom_space
INITRANS 30
STORAGE (INITIAL 10K NEXT 1K PCTINCREASE 0);

CREATE UNIQUE INDEX M_wo_idx ON M_workorder (wo_number) LOCAL
TABLESPACE M_wo_idx_space
initrans 30
pctfree 90
STORAGE (INITIAL 1m NEXT 1m  pctincrease 0  buffer_pool recycle) parallel 8;
alter index m_wo_idx noparallel;

CREATE UNIQUE INDEX M_lo_idx ON M_largeorder (lo_id)
TABLESPACE M_lo_space
INITRANS 30
global partition by hash(lo_id) partitions 256;

CREATE UNIQUE INDEX M_OL_O_idx ON M_largeorder (lo_o_id, lo_ol_id)
TABLESPACE M_lo_space
INITRANS 30 local;

CREATE UNIQUE INDEX M_inv_idx ON M_inventory (in_p_id)
TABLESPACE M_inv_idx_space
STORAGE (BUFFER_POOL default)
pctfree 50
INITRANS 30 ;

