用oracle交换分区快速实现非分区表数据迁移到分区表数据

      某项目数据日益增长，查询统计越来越慢，因此想将现在数据量大的几张表做成分区表，但现在数据量已经比较大了。之前有做过类似的事情，上次的做法是先将表改名，然后创建分区表，再把改名后表的数据导入到分区表，数据量还没有现在这个项目大，从一大早一直忙到很晚才完成。

 

      于是现在考虑有没有办法能快速的处理用户的这个需求，以前那种做法效率实在太低了，停机时间过长。经过同事提醒，发现交换分区可以实现。于是认真的读了君三思写的《全面学习分区表及分区索引》中的交换分区一节，并在测试环境中做了测试，测试结果比较满意，证明这个是完全可行的，特此记录一下测试过程。

 

      --创建存储数据的分区表空间及索引表空间，打算将之前的数据全放在一个分区，从下个月开始，每月建一个分区。

 

      --建立用于实现交换分区的表，这个表要和原表结构一致，如我建的用于实现交换分区的表如下（将2008年12月及之前的数据全放到分区p_2008_12中、我测试环境中只有2008年12月及之前的数据）

 

create table TD_COMMON_NEW
(
  CARDID        VARCHAR2(9) not null,
  CARDTYPE      VARCHAR2(2),
  TRADETYPE     VARCHAR2(2),
  TRADEADDRESS  VARCHAR2(10) default '0000000000',
  TRADEDATE     DATE not null,
  TERMINALID    VARCHAR2(9) not null,
  OPERATOR      VARCHAR2(6),
  TRADEMONEY    NUMBER(10) default 0,
  TRADEVALUE    NUMBER(10) default 0,
  CURRENTVALUE  NUMBER(10) default 0,
  FOREGIFT      NUMBER(10) default 0,
  CHARGE        NUMBER(10) default 0,
  CARDSN        NUMBER(10) default 0 not null,
  TERMINALSN    NUMBER(10) default 0,
  TERMINALSUM   NUMBER(10) default 0,
  TAC           VARCHAR2(8) default '00000000',
  RECEIVEDATE   DATE default sysdate not null,
  TRADESN       NUMBER(10) default 0,
  RECEIVESN     NUMBER(10) default 0,
  STARTDATE     DATE,
  STARTADDRESS  VARCHAR2(10),
  STARTTERMINAL VARCHAR2(9),
  DATARESOURCE  VARCHAR2(1) default '0' not null,
  TRADETYPEEX   VARCHAR2(4),
  DESTINATION   VARCHAR2(10),
  SAMID         VARCHAR2(20),
  SAMSN         NUMBER(10),
  APPTYPE       CHAR(1),
  CARDIDM       VARCHAR2(16)
)
Partition by range(TRADEDATE)
(partition p_2008_12 values less than (to_date('200901','yyyymm'))
Tablespace afc_data_0812
);

     

----------------------------------------------------------------------------------------------
--添加主键
----------------------------------------------------------------------------------------------

alter table TD_COMMON_NEW
  add constraint PK_TD_COMMON_PART primary key (CARDID,TRADEDATE,CARDSN,TERMINALID)
  using index
local
(
  partition p_2008_12   Tablespace afc_indx_0812
);

--------------------------------------------------------------------------
--按原来表的索引重新建立局部分区索引（按TRADEDATE建局部分区索引）
--------------------------------------------------------------------------

create index IN_TD_COMMON_TRADEDATE_PART on TD_COMMON_NEW(TRADEDATE)
local
(
  partition p_2008_12   Tablespace afc_indx_0812
);

--------------------------------------------------------------------------
--按原来表的索引重新建立局部分区索引（按RECEIVEDATE建局部分区索引）
--------------------------------------------------------------------------

create index IN_TD_COMMON_RECEIVEDATE_PART on TD_COMMON_NEW(RECEIVEDATE)
local
( 
  partition p_2008_12   Tablespace afc_indx_0812
);

--------------------------------------------------------------------------
--按原来表的索引重新建立局部分区索引（按RECEIVESN建局部分区索引）
--------------------------------------------------------------------------

create index IN_TD_COMMON_RECEIVESN_PART on TD_COMMON_NEW (RECEIVESN)
local
( 
  partition p_2008_12   Tablespace afc_indx_0812
);

--交换分区
ALTER TABLE TD_COMMON_NEW EXCHANGE PARTITION p_2008_12 WITH TABLE TD_COMMON INCLUDING  INDEXES  without validation;

--可以查看分区后的效果(发现分区还在afc_data表空间)
select segment_name , partition_name , segment_type , tablespace_name from dba_extents where segment_name = 'TD_COMMON_NEW'

select  table_owner,table_name,tablespace_name,partition_name from dba_tab_partitions where table_name='TD_COMMON_NEW' ORDER BY PARTITION_NAME;

--检查索引分区
select * from dba_ind_partitions where index_owner = 'AFCLINE1' and index_name ='PK_TD_COMMON_PART' ORDER BY PARTITION_NAME;

select * from dba_ind_partitions where index_owner = 'AFCLINE1' and index_name ='IN_TD_COMMON_TRADEDATE_PART' ORDER BY PARTITION_NAME;

select * from dba_ind_partitions where index_owner = 'AFCLINE1' and index_name ='IN_TD_COMMON_RECEIVEDATE_PART' ORDER BY PARTITION_NAME;

select * from dba_ind_partitions where index_owner = 'AFCLINE1' and index_name ='IN_TD_COMMON_RECEIVESN_PART' ORDER BY PARTITION_NAME;

--将分区和表空间对应一下
ALTER TABLE TD_COMMON_NEW MOVE PARTITION p_2008_12 TABLESPACE AFC_DATA_0812;

--重建分区索引到新的分区表空间
ALTER INDEX PK_TD_COMMON_PART  REBUILD PARTITION P_2008_12  TABLESPACE AFC_INDX_0812;

ALTER INDEX IN_TD_COMMON_TRADEDATE_PART  REBUILD PARTITION P_2008_12  TABLESPACE AFC_INDX_0812;

ALTER INDEX IN_TD_COMMON_RECEIVEDATE_PART  REBUILD PARTITION P_2008_12  TABLESPACE AFC_INDX_0812;

ALTER INDEX IN_TD_COMMON_RECEIVESN_PART  REBUILD PARTITION P_2008_12  TABLESPACE AFC_INDX_0812;

--重命名表
RENAME TD_COMMON TO TD_COMMON_OLD;

RENAME TD_COMMON_NEW TO TD_COMMON;

 

--如果是本月进行的操作，想本月的数据单独放在一个分区的话，可对分区分裂（之前的语法就应当是全存放在p_old 分区而不是p_2008_12中了）

ALTER TABLE TD_COMMON_NEW SPLIT partition p_old
AT (TO_DATE('2008-12-01','YYYY-MM-DD')) INTO (partition p_old, partition p_2008_12) ;