oracle分区交换的总结

一、分区交换介绍

分区交换技术可以实现数据快速转移，所以在数据加载提速，历史数据清理等方面特别有用。分区交换技术实际上只修改了数据字典中的数据物理段位置，而不是实际的移动数据，所以速度很快。

二、注意事项

1、要交换的分区表中不能含有自增列，否则可能无法交换分区

2、exchange的过程会以独占模式（exclusive）锁住两张表，不过执行速度很快，也不用担心阻塞业务[dml语句

3、还考虑ogg和dataguard的影响，都是一些对系统表的修改

4、分区交换上时的普通表及分区表上的字段结构及索引结构都需要一致，建议分区表上的索引都是本地索引，全局索引会失效，大表重建索引耗时比较长，务必要注意分区交换后的索引状态，是否可用，是否需要重建，建议在上线前进行严格测试。

5、两个range分区不能进行分区交换,两个list分区也不能交换，需要采用普通表作为中间表进行交换

6、两个组合分区的表不能直接进行分区交换，如range-hash分区，先置换到hash分区的表（中间表也必须是分区表）里，再用hash分区表跟range-hash交换，实现将range-hash分区表的指定分区置换到range-hash分区的表里

三、普通表与普通分区表进行分区交换示例介绍

drop table RANGE_HASH_TEST purge;
drop table range_exch purge;

create table RANGE_HASH_TEST
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26)，
vdate date default sysdate
)
PARTITION BY RANGE (TRN_DT)
( PARTITION Acc_Jan_2017 VALUES LESS THAN (TO_DATE('01-02-2017','dd-mm-yyyy')));

alter table RANGE_HASH_TEST add constraint PK_RANGE_HASH_TEST primary key(SEQ_NO,TRN_DT,REF_NO) 
using index  local ; --主键本地索引

create table range_exch
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26)，
vdate date default sysdate
);

INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),1,'123',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),2,'12',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),3,'113',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),4,'121',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),5,'124',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),6,'125',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),7,'126',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),8,'127',sysdate);
INSERT INTO range_exch VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),9,'128',sysdate);
commit;

alter table range_exch add constraint PK_RANGE_EXCH primary key(SEQ_NO,TRN_DT,REF_NO);

--进行分区交换
ALTER TABLE RANGE_HASH_TEST EXCHANGE PARTITION ACC_JAN_2017 WITH TABLE range_exch including indexes;--加上including indexes不需要重建索引
--检查索引状态
select STATUS from dba_ind_partitions where index_name='PK_RANGE_HASH_TEST' and INDEX_owner='SCOTT';
select STATUS from dba_indexes where index_name='PK_RANGE_EXCH' and owner='SCOTT';

rang分区跟rang分区交换，其实就是上面步骤执行了两次，存在一个中间普通表而已，在此不展示了。

四、组合分区对组合分区进行分区交换

第二部分提到组合分区跟组合分区交换时，不能直接交换，需要使用到分区的中间表，下面进行展示：

drop table RANGE_HASH_TEST purge;
drop table range_exch purge;
drop table RANGE_HASH_TEST_HIST purge;
--组合分区表RANGE_HASH_TEST
create table RANGE_HASH_TEST
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26)，
vdate date default sysdate
)
PARTITION BY RANGE (TRN_DT)
SUBPARTITION BY HASH (REF_NO) SUBPARTITIONS 4
( PARTITION ACC_JAN_2017 VALUES LESS THAN (TO_DATE('01-02-2017','dd-mm-yyyy')));

INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),1,'123',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),2,'12',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),3,'113',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),4,'121',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),5,'124',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),6,'125',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),7,'126',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),8,'127',sysdate);
INSERT INTO RANGE_HASH_TEST VALUES(TO_DATE('01-01-2017','DD-MM-YYYY'),9,'128',sysdate);
commit;

alter table RANGE_HASH_TEST add constraint PK_RANGE_HASH_TEST primary key(SEQ_NO,TRN_DT,REF_NO) 
using index  local ; 
--分区交换中间表
create table range_exch
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26),
vdate date default sysdate
)
PARTITION BY HASH (REF_NO) PARTITIONS 4;

INSERT INTO range_exch VALUES(TO_DATE('15-01-2017','DD-MM-YYYY'),2,'321',sysdate);

alter table range_exch add constraint PK_range_exch primary key(SEQ_NO,TRN_DT,REF_NO) 
using index  local ; 

--分区交换历史表，需要将RANGE_HASH_TEST的分区交换到历史表里
create table RANGE_HASH_TEST_HIST
(
TRN_DT DATE,
SEQ_NO NUMBER,
REF_NO VARCHAR2(26),
vdate date default sysdate
)
PARTITION BY RANGE (TRN_DT)
SUBPARTITION BY HASH (REF_NO) SUBPARTITIONS 4
( PARTITION Acc_Jan_2017 VALUES LESS THAN (TO_DATE('01-2-2017','dd-mm-yyyy')));

alter table RANGE_HASH_TEST_HIST add constraint PK_RANGE_HASH_TEST_HIST primary key(SEQ_NO,TRN_DT,REF_NO) 
using index  local ; 

--进行两次分区交换
ALTER TABLE RANGE_HASH_TEST EXCHANGE PARTITION ACC_JAN_2017 WITH TABLE range_exch including indexes;--加上including indexes不需要重建索引
ALTER TABLE RANGE_HASH_TEST_HIST EXCHANGE PARTITION ACC_JAN_2017 WITH TABLE range_exch including indexes;


select STATUS from dba_ind_partitions where index_name='PK_RANGE_HASH_TEST'and INDEX_owner='SCOTT'
select STATUS from dba_ind_partitions where index_name='PK_RANGE_HASH_TEST_HIST'and INDEX_owner='SCOTT';