使用exchage将普通表重定义为分区表并备份历史数据

环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

 

最近发现之前有个没有分区的大数据量表(150G,记录行数1.2亿),统计过程非常的耗时,通过业务分析,该表可以按照日期分区,而且用户需要对历史数据做备份,当时我想使用在线重定义去实现,针对大数据量的表,通过之前的经验发现在线重定义也是比较耗时间的，权衡之后,后来决定采用exchange的方法处理,处理过程如下(过程中的表和数据都是模拟的,这里只是告诉大家一个处理过程的方法).

 

1.创建普通表

create table tb_hxl_no_part_t
(
  statedate date,
  user_count number
);

2.写入数据

insert into tb_hxl_no_part_t values(date'2010-01-01',1);
insert into tb_hxl_no_part_t values(date'2010-02-01',1);
insert into tb_hxl_no_part_t values(date'2010-03-01',1);
insert into tb_hxl_no_part_t values(date'2010-04-01',1);
insert into tb_hxl_no_part_t values(date'2010-05-01',1);
insert into tb_hxl_no_part_t values(date'2010-06-01',1);
insert into tb_hxl_no_part_t values(date'2010-07-01',1);
insert into tb_hxl_no_part_t values(date'2010-08-01',1);
insert into tb_hxl_no_part_t values(date'2010-09-01',1);
insert into tb_hxl_no_part_t values(date'2010-10-01',1);
insert into tb_hxl_no_part_t values(date'2010-11-01',1);
insert into tb_hxl_no_part_t values(date'2010-12-01',1);

insert into tb_hxl_no_part_t values(date'2011-01-01',1);
insert into tb_hxl_no_part_t values(date'2011-02-01',1);
insert into tb_hxl_no_part_t values(date'2011-03-01',1);
insert into tb_hxl_no_part_t values(date'2011-04-01',1);
insert into tb_hxl_no_part_t values(date'2011-05-01',1);
insert into tb_hxl_no_part_t values(date'2011-06-01',1);
insert into tb_hxl_no_part_t values(date'2011-07-01',1);
insert into tb_hxl_no_part_t values(date'2011-08-01',1);
insert into tb_hxl_no_part_t values(date'2011-09-01',1);
insert into tb_hxl_no_part_t values(date'2011-10-01',1);
insert into tb_hxl_no_part_t values(date'2011-11-01',1);
insert into tb_hxl_no_part_t values(date'2011-12-01',1);

insert into tb_hxl_no_part_t values(date'2012-01-01',1);
insert into tb_hxl_no_part_t values(date'2012-02-01',1);
insert into tb_hxl_no_part_t values(date'2012-03-01',1);

insert into tb_hxl_no_part_t values(date'2012-04-01',1);

commit;

3.创建分区表(将2012-04-01以前的数据放入一个分区,用来备份)

create table tb_hxl_part_t
(
  statedate date,
  count number
)
partition by range (statedate)
(
  partition p_20120401 values less than (to_date('2012-04-02','YYYY-MM-DD')),
  partition p_20120402 values less than (to_date('2012-04-03','YYYY-MM-DD')),
  partition p_20120403 values less than (to_date('2012-04-04','YYYY-MM-DD')),
  partition p_20120404 values less than (to_date('2012-04-05','YYYY-MM-DD')),
  partition p_20120405 values less than (to_date('2012-04-06','YYYY-MM-DD'))
);

4.交换分区

SQL>alter table tb_hxl_part_t exchange partition p_20120401 with table tb_hxl_no_part_t;

5.非分区表的数据已经交换到分区表

SQL> select count(1) from tb_hxl_part_t partition(p_20120401);

  COUNT(1)
----------
        28

 

6.可以对分区p_20120401进行备份

 

create table tb_hxl_part_bak_20120401
as
select * from tb_hxl_part_t t
where 1=2;

 

SQL>alter table tb_hxl_part_t exchange partition p_20120401 with table tb_hxl_part_bak_20120401;

 

SQL> select count(1) from tb_hxl_part_t partition (p_20120401);

  COUNT(1)
----------
         0

SQL> select count(1) from tb_hxl_part_bak_20120401;

  COUNT(1)
----------
        28

 

可以发现分区表tb_hxl_part_t分区p_20120401的数据已经备份到tb_hxl_part_bak_20120401

7.更变表名

 

alter table tb_hxl_no_part_t  rename to tb_hxl_no_part_t_bak;

alter table tb_hxl_part_t rename to tb_hxl_no_part_t;

 

这样既实现了对普通表的数据备份同时实现了普通表到为分区表的转换(比在线重定义节省了很多时间). 

 

-- The End --