DROP TABLE O_orders;
CREATE TABLE O_orders
(
	o_id            integer not null,
	o_c_id          integer,
	o_ol_cnt        integer,
	o_discount      numeric(4,2),
	o_total         numeric(12,2),
	o_status        integer,
	o_entry_date    timestamp,
	o_ship_date     date
)
PARTITION by HASH(o_c_id)
PARTITIONS 256
STORE IN (O_ords_space)
INITRANS 30
pctfree 75
pctused 20
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8);

DROP TABLE O_orderline;
CREATE TABLE O_orderline
(
	ol_id		integer not null,
	ol_o_id		integer not null,
	ol_i_id		char(20),
	ol_qty		integer,
	ol_total_value	numeric(12,2),
	ol_msrp		numeric(12,2),
	ol_status	integer,
	ol_ship_date	date
)
TABLESPACE O_ordl_space
PARTITION BY HASH (ol_o_id)
PARTITIONS 512
INITRANS 30
PCTFREE 50
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8);


DROP cluster O_item_clus including tables;
create cluster O_item_clus ( i_id char(20) )
single table
hashkeys 3000000
size 150
tablespace O_item_space;


DROP TABLE O_item;
CREATE TABLE O_item
(
	i_id                    char(20) not null,
	i_name                  char(35),
	i_desc                  varchar(100),
	i_price                 numeric(12,2),
	i_discount              numeric(6,4),
	i_category              integer not null
)
CLUSTER O_item_clus(i_id);


CREATE UNIQUE INDEX O_ords_idx ON O_orders (o_id)
TABLESPACE O_ords_space
INITRANS 30
STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8)
GLOBAL PARTITION BY HASH (o_id) PARTITIONS 256;


CREATE INDEX O_oc_idx ON O_orders (o_c_id)
TABLESPACE O_ords_space
INITRANS 30
STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8)
LOCAL;



CREATE UNIQUE INDEX O_ordl_idx ON O_orderline (ol_o_id, ol_id)
TABLESPACE O_ordl_space
INITRANS 30
PCTFREE 50
STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8)
LOCAL;


CREATE UNIQUE INDEX O_i_idx ON O_item (i_id)
INITRANS 10
TABLESPACE O_item_space
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);

CREATE INDEX O_icat_idx ON O_item (i_category) INITRANS 10 tablespace o_ITEM_SPACE;
