在oracle中,将一个非分区表转换为分区表 (对split partition 和exchange partition使用)
/*
功能:
将一个非分区表转换为分区表
基本思路:
tbl_nopart是(数据量上百万条,列比较多)一个非分区表,此时创建一个与tbl_nopart同结构的分区表tbl_part,维护数据,
删除tbl_nopart表,将tbl_part更名为tbl_nopart
*/
--具体步骤:
--1、查询非分区表数据
select to_char(rpt_date, 'yyyymm'), count(*) from tbl_nopart group by to_char(rpt_date, 'yyyymm') order by to_char(RPT_DATE,
'yyyymm');
--2、创建分区表(结构和非分区表tbl_nopart相同)
create table tbl_part
(
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 tbl_part exchange partition rest with table tbl_nopart;
--tbl_nopart中大约50万条记录, 占用600m左右空间, 交换大约用了不到30秒钟, tbl_part和tbl_nopart所在的表空间也没有明显增长.
--4、查询分区表数据
select to_char(RPT_DATE, 'mm-yyyy'), count(*) from tbl_nopart group by to_char(RPT_DATE, 'mm-yyyy');
--辅助脚本
/*
select 'alter table tbl_part split partition rest at (to_date('''
|| to_char(RPT_DATE, 'yyyymm') ||''',''yyyymm'')) into (partition p'
|| to_char(RPT_DATE, 'yyyymm')
|| ', partition rest);'
from tbl_part
group by to_char(RPT_DATE, 'yyyymm')
order by to_char(RPT_DATE, 'yyyymm')
*/
--5、对rest分区进行拆分
alter table tbl_part split partition rest at (to_date('200608','yyyymm')) into (partition p200608, partition rest);
alter table tbl_part split partition rest at (to_date('200609','yyyymm')) into (partition p200609, partition rest);
alter table tbl_part split partition rest at (to_date('200610','yyyymm')) into (partition p200610, partition rest);
alter table tbl_part split partition rest at (to_date('200611','yyyymm')) into (partition p200611, partition rest);
alter table tbl_part split partition rest at (to_date('200612','yyyymm')) into (partition p200612, partition rest);
--6、删除非分区表
drop table tbl_nopart
--7、将分区表重命名为原非分区表名
alter table tbl_part rename to tbl_nopart
________________________________
--补充
如果已存在分区表,要将某未分区表(与已分区表结构完全相同)加入到分区中:
alter table tbl_nopart exchange partition p200608 with table tbl_part_1;
alter table tbl_nopart exchange partition p200609 with table tbl_part_2;
如果tbl_part_2不符合分区规则,则会报错,则需要指定without validation 来禁止检查
alter table tbl_nopart exchange partition p200609 with table tbl_part_2 without validation;
当然,如果你确认tbl_part_1表中的数据是符合分区规则的,那指定without validation 之后,不会对tbl_part_1进行全表扫描,
则会缩短exchange时间