oracle大表分区

 oracle大表分区
--表分区概念　
对数据量非常大的表按照某一规则分区，分区规则有以下几种：
1.范围分区：指定一个范围，如时间范围等；
2.哈希分区：指定分块的数量；
3.列表分区：指定某些具体的值（Oracle 9i以上支持）；
4.组合分区：即范围分区结合哈希分区／列表分区，不能多层嵌套．

实际练习：

--分区表-----------------------------------------------------------------------------------------------------
--创建空分区表
create table w_test_iptvbill(id number(18), ppvname varchar2(255), starttime date)
partition by range(starttime)(
partition w_test_part1 values less than(to_date('1980-01-01','yyyy-mm-dd')) tablespace DVBCETUS_SUP,
partition w_test_part2 values less than(to_date('1990-01-01','yyyy-mm-dd')) tablespace DVBCETUS_SUP,
partition w_test_part3 values less than(to_date('2000-01-01','yyyy-mm-dd')) tablespace DVBCETUS_SUP,
partition w_test_part4 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace DVBCETUS_SUP,
partition w_test_part5 values less than(maxvalue) tablespace DVBCETUS_SUP
)

 

--创建复制分区表
create table tmp_whf_iptvbill partition by range(starttime)(
partition part_1 values less than (to_date('20071108','yyyymmdd')) tablespace dvbcetus_sup,
partition part_2 values less than (to_date('20071116','yyyymmdd')) tablespace dvbcetus_sup,
partition part_3 values less than (to_date('20071124','yyyymmdd')) tablespace dvbcetus_sup,
partition part_4 values less than (to_date('20071201','yyyymmdd')) tablespace dvbcetus_sup
) as
select * from ow_iptv_bill ib
where ib.starttime < to_date('20071201','yyyymmdd')
and ib.starttime >= to_date('20071101','yyyymmdd')

更复杂一点的：

create table ow_iptv_bill_total partition by range(starttime)(
    partition iptvbill_total_part1 values less than(to_date('200505','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part2 values less than(to_date('200508','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part3 values less than(to_date('200510','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part4 values less than(to_date('200512','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part5 values less than(to_date('200602','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part6 values less than(to_date('200604','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part7 values less than(to_date('200606','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part8 values less than(to_date('200607','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part9 values less than(to_date('200608','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part10 values less than(to_date('200609','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part11 values less than(to_date('200610','yyyymm')) tablespace iptvbill ,
    partition iptvbill_total_part12 values less than(to_date('200611','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part13 values less than(to_date('200612','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part14 values less than(to_date('200701','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part15 values less than(to_date('200702','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part16 values less than(to_date('200703','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part17 values less than(to_date('200704','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part18 values less than(to_date('200705','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part19 values less than(to_date('200706','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part20 values less than(to_date('200707','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part21 values less than(to_date('200708','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part22 values less than(to_date('200709','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part23 values less than(to_date('200710','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part24 values less than(to_date('200711','yyyymm')) tablespace iptvbill,
    partition iptvbill_total_part25 values less than(to_date('200712','yyyymm')) tablespace iptvbill
)
pctfree 10
pctused 40
initrans 1
maxtrans 255
 storage (
 initial 64k
 next 100m
 minextents 1
 maxextents 300
 pctincrease 0)
 as
select * from (
select * from OW_IPTV_BILL
union all
select * from OW_IPTV_BILL0510
union all
select * from OW_IPTV_BILL0511
union all
select * from OW_IPTV_BILL0512
union all
select * from OW_IPTV_BILL0601
union all
select * from OW_IPTV_BILL0602
union all
select * from OW_IPTV_BILL0603
union all
select * from OW_IPTV_BILL0607
union all
select * from OW_IPTV_BILL0608
union all
select * from OW_IPTV_BILL0609
union all
select * from OW_IPTV_BILL06Q1
union all
select * from OW_IPTV_BILL06Q2
union all
select * from OW_IPTV_BILL0711);

 

索引也可以分区，索引分区分两类：
1、局部分区（local partition，也有称为本地分区的），这种分区是与分区表的分区范围一致。
2、全局分区（global partition），全局分区与分区表上的分区毫不相干。全局分区只有范围分区。
--创建分区索引
create global index idx_iptvbill_id on w_test_iptvbill(starttime)
partition by range(starttime)(
partition idx_iptvbill_id1 values less than(to_date('1980-01-01','yyyy-mm-dd')) tablespace INDX,
partition idx_iptvbill_id2 values less than(to_date('1990-01-01','yyyy-mm-dd')) tablespace INDX,
partition idx_iptvbill_id3 values less than(to_date('2000-01-01','yyyy-mm-dd')) tablespace INDX,
partition idx_iptvbill_id4 values less than(to_date('2010-01-01','yyyy-mm-dd')) tablespace INDX,
partition idx_iptvbill_id5 values less than(maxvalue) tablespace INDX      --此处必须指定maxvalue
)