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