在oracle中，将一个非分区表转换为分区表 (split partition 和exchange partition)
将一个非分区表转换为分区表
思路：
HZMH_Bak_SALE是(数据量上百万条，列比较多)一个非分区表，此时创建一个与HZMH_Bak_SALE同结构的分区表luda_partition，维护数据，
删除HZMH_Bak_SALE表，将luda_partition更名为ludatou_pat，删除luda_partition
步骤：
查询非分区表数据
select to_char(DATE, ‘yyyymm’), count(*) from HZMH_Bak_SALE group by to_char(DATE, ‘yyyymm’) order by to_char(DATE, ‘yyyymm’);
创建分区表（结构和非分区表HZMH_Bak_SALE相同）
create table luda_partition
(
PIG_ID VARCHAR2(12) not null,
DATE DATE not null,
NUM_NO VARCHAR2(12),
……
)
tablespace ludatou
partition by range(DATE)(
partition rest values less than (maxvalue))
交换数据（数据从非分区表到分区表）
alter table luda_partition exchange partition rest with table HZMH_Bak_SALE;
查询分区表数据
select to_char(DATE, ‘mm-yyyy’), count(*) from HZMH_Bak_SALE group by to_char(DATE, ‘mm-yyyy’);
–辅助脚本
select ‘alter table luda_partition split partition rest at (to_date(”’
|| to_char(DATE, ‘yyyymm’) ||”’,”yyyymm”)) into (partition p’
|| to_char(DATE, ‘yyyymm’)
|| ‘, partition rest);’
from luda_partition
group by to_char(DATE, ‘yyyymm’)
order by to_char(DATE, ‘yyyymm’)
–5、对rest分区进行拆分
alter table luda_partition split partition rest at (to_date(‘201008′,’yyyymm’)) into (partition p201008, partition rest);
alter table luda_partition split partition rest at (to_date(‘201009′,’yyyymm’)) into (partition p201009, partition rest);
alter table luda_partition split partition rest at (to_date(‘201010′,’yyyymm’)) into (partition p201010, partition rest);
alter table luda_partition split partition rest at (to_date(‘201011′,’yyyymm’)) into (partition p201011, partition rest);
alter table luda_partition split partition rest at (to_date(‘201012′,’yyyymm’)) into (partition p201012, partition rest);
–6、删除非分区表
drop table HZMH_Bak_SALE
–7、将分区表重命名为原非分区表名
alter table luda_partition rename to HZMH_Bak_SALE
–8、删除中间处理过程的分区表
drop table luda_partition
如果已存在分区表，要将未分区表（与已分区表结构完全相同）加入到分区中：
alter table HZMH_Bak_SALE exchange partition p201008 with table luda_test;
alter table HZMH_Bak_SALE exchange partition p201009 with table luda_test_t;
如果luda_test_t不符合分区规则，需要指定without validation 来禁止检查
alter table HZMH_Bak_SALE exchange partition p201009 with table luda_test_t without validation;
如果luda_test表中的数据是符合分区规则的，那指定without validation 之后，不会对luda_test进行全表扫描，
则会缩短exchange时间
参考手册：
Administrator Guide
