使用split对分区表再分区

个人分类：原创

表justin本来按月分区，但是进入7月份，数据量暴涨，每天都有百万条之多，应开发要求，将其7月份及其以后的分区都改为按天分区

目前该表最大分区到8月1号

首先需要对7月份现有分区执行split操作，然后添加8月份以后的分区

执行split操作语法如下

alter table justin split partition p1107 at (to_date('2011-07-02','yyyy-mm-dd')) into (partition p110701,partition p1107);

该语句会使p1107和p110701上的索引全部失效,且生成大量redo

生成脚本

declare

v_date date;

v_cnt number := 0;

v_cur varchar2(5);

v_mon varchar2(5);--number := 0;

v_day varchar2(5);--number := 0;

begin

v_date := to_date('2011-07-01','yyyy-mm-dd');

for i in 1..30 loop

v_cur := to_char(v_date,'dd');

v_date := v_date + 1;

v_mon := to_char(v_date,'mm');

v_day := to_char(v_date,'dd');

dbms_output.put_line('alter table justin split partition p1107 at (to_date(''2011-'||v_mon||'-'||v_day||''',''yyyy-mm-dd'')) into (partition p11'||v_mon||v_cur||',partition p1107);');

dbms_output.put_line('exec dbms_lock.sleep(10);');

end loop;

end;

生成的脚本如下

alter table justin split partition p1107 at (to_date('2011-07-30','yyyy-mm-dd')) into (partition p110729,partition p1107);

exec dbms_lock.sleep(10);

alter table justin split partition p1107 at (to_date('2011-07-31','yyyy-mm-dd')) into (partition p110730,partition p1107);

exec dbms_lock.sleep(10);

执行完毕后 需要重建索引

declare

begin

for i in (select index_name,table_name from user_indexes where status ='UNUSABLE') loop

execute immediate 'alter index '||i.index_name||' rebuild online';

end loop;

for j in (select u.index_name,u.partition_name,ui.table_name from user_ind_partitions u,user_indexes ui where u.status ='UNUSABLE' and u.index_name = ui.index_name) loop

execute immediate 'alter index '||j.index_name|| ' rebuild partition '||j.partition_name||' online';

end loop;

end;

该表到8月1号过期，为其新增一年的分区

declare

v_date date;

v_cnt number := 0;

v_year varchar2(2);

v_curday varchar2(2);

v_mon varchar2(2);--number := 0;

v_day varchar2(2);--number := 0;

begin

v_date := to_date('2011-08-01','yyyy-mm-dd');

for i in 1..365 loop

v_year := to_char(v_date,'yy');

v_curday := to_char(v_date,'dd');

v_date := v_date + 1;

v_mon := to_char(v_date,'mm');

v_day := to_char(v_date,'dd');

dbms_output.put_line('alter table justin add partition P'||v_year||v_mon||v_curday||' values less than (to_date(''20'||v_year||'-'||v_mon||'-'||v_day||''',''yyyy-mm-dd''));');-- into (partition p11'||v_mon||v_curday||',partition p1107);');

end loop;

end;

输出结果如下

alter table justin add partition P110801 values less than (to_date('2011-08-02','yyyy-mm-dd'));

alter table justin add partition P110802 values less t

han (to_date('2011-08-03','yyyy-mm-dd'));

alter table justin add partition P110803 values less than (to_date('2011-08-04','yyyy-mm-dd'));