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
)
TABLESPACE O_ords_space
INITRANS 30
pctfree 75
pctused 20
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8);

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)
;


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)
global;


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 47
INITRANS 30
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 FREELIST GROUPS 14 FREELISTS 8);


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

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
)
TABLESPACE O_item_space
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);



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);
