索引oracle例子,oracle分区表&索引(例子)

------------------------首先创建分区表-------------------------------------------------

create table store_sales

(store_id number(6),invoice_number number,

time_id date,invoice_sale_amt number(10,2))

partition by range(time_id) subpartition by hash(invoice_number)

(partition sales_q1_2003

values less than (to_date('2003-01-05','YYYY-MM-DD')),

partition sales_q2_2003

values less than(to_date('2003-01-10','YYYY-MM-DD')),

partition sales_q3_2003

values less than (to_date('2003-01-15','YYYY-MM-DD'))

(subpartition ch_c,subpartition ch_i,

subpartition ch_p,subpartition ch_s,subpartition ch_t),

partition sales_q4_2003

values less than (to_date('2004-01-03','YYYY-MM-DD'))

SUBPARTITIONS 8,

partition sales_overflow

values less than (maxvalue)subpartitions 4);

----------------------------------接着创建索引-----------------------------------

create index sales_ix on store_sales(time_id,store_id)storage(initial 1m maxextents unlimited) local(partition q1_2003,partition q2_2003,partition q3_2003(subpartition pq3200301,subpartition pq3200302,subpartition pq3200303,subpartition pq3200304,subpartition pq3200305),partition q4_2003(subpartition pq4200301 tablespace system,subpartition pq4200302 tablespace system,subpartition pq4200303 tablespace system,subpartition pq4200304 tablespace system,subpartition pq4200305 tablespace system,subpartition pq4200306 tablespace system,subpartition pq4200307 tablespace system,subpartition pq4200308 tablespace system),partition sales_overflow(subpartition pqoflw01 tablespace system,subpartition pqoflw02 tablespace system,subpartition pqoflw03 tablespace system,subpartition pqoflw04 tablespace system))/