/*
功能:
将一个非分区表转换为分区表
基本思路:
SST_DAY_TOTAL是(数据量上百万条,列比较多)一个非分区表,此时创建一个与SST_DAY_TOTAL同结构的分区表zhaozhenlong_partition,维护数据,
删除SST_DAY_TOTAL表,将zhaozhenlong_partition更名为SST_DAY_TOTA,删除zhaozhenlong_partition
*/
--具体步骤:
--1、查询非分区表数据
select to_char(rpt_date, 'yyyymm'), count(*) from SST_DAY_TOTAL group by to_char(rpt_date, 'yyyymm') order by to_char(RPT_DATE, 'yyyymm');
--2、创建分区表(结构和非分区表SST_DAY_TOTAL相同)
create table zhaozhenlong_partition
(
GAS_ID VARCHAR2(12) not null,
RPT_DATE DATE not null,
INV_NO VARCHAR2(12),
......
)
tablespace sdlg
partition by range(rpt_date)(
partition rest values less than (maxvalue))
--3、交换数据(数据从非分区表到分区表)
alter table zhaozhenlong_partition exchange partition rest with table SST_DAY_TOTAL;
--4、查询分区表数据
select to_char(RPT_DATE, 'mm-yyyy'), count(*) from SST_DAY_TOTAL group by to_char(RPT_DATE, 'mm-yyyy');
--辅助脚本
/*
select 'alter table zhaozhenlong_partition split partition rest at (to_date('''
|| to_char(RPT_DATE, 'yyyymm') ||''',''yyyymm'')) into (partition p'
|| to_char(RPT_DATE, 'yyyymm')
|| ', partition rest);'
from zhaozhenlong_partition
group by to_char(RPT_DATE, 'yyyymm')
order by to_char(RPT_DATE, 'yyyymm')
*/
--5、对rest分区进行拆分
alter table zhaozhenlong_partition split partition rest at (to_date('200608','yyyymm')) into (partition p200608, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200609','yyyymm')) into (partition p200609, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200610','yyyymm')) into (partition p200610, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200611','yyyymm')) into (partition p200611, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200612','yyyymm')) into (partition p200612, partition rest);
--6、删除非分区表
drop table SST_DAY_TOTAL
--7、将分区表重命名为原非分区表名
alter table zhaozhenlong_partition rename to SST_DAY_TOTAL
--8、删除中间处理过程的分区表
drop table zhaozhenlong_partition
________________________________
--补充
如果已存在分区表,要将某未分区表(与已分区表结构完全相同)加入到分区中:
alter table SST_DAY_TOTAL exchange partition p200608 with table zhaozhenlong_1;
alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2;
如果zhaozhenlong_2不符合分区规则,则会报错,则需要指定without validation 来禁止检查
alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2 without validation;
当然,如果你确认zhaozhenlong_1表中的数据是符合分区规则的,那指定without validation 之后,不会对zhaozhenlong_1进行全表扫描,
则会缩短exchange时间
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-417534/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-417534/