oracle分区交换（exchange）技术

需求：一个几亿条数据的分区表，要把一些老数据分出来，放一个新建的表上。按时间划分，指定时间以前的放在老数据表，指定时间以后的数据保留在原表，然后把备份老数据表空间truncate掉。按照分区操作分区交换是最好的解决办法。 分区交换技术可以实现数据快速转移，所以在数据加载提速，历史数据清理等方面特别有用。分区交换技术实际上只修改了数据字典中的数据物理段位置，而不是实际的移动数据，所以速度很快。 创建分区表： create table t_exchange ( sno number not null primary key, oitime date DEFAULT sysdate, word  varchar2(100) ) PARTITION BY range(sno) ( PARTITION p1 VALUES LESS THAN (10000) tablespace TBS_MING,  PARTITION p2 VALUES LESS THAN (20000) tablespace TBS_MING,  PARTITION p3 VALUES LESS THAN (maxvalue) tablespace TBS_MING ) tablespace TBS_MING; 历史分区表： create table t_exchange_his ( sno number not null primary key, oitime date DEFAULT sysdate, word  varchar2(100) ) PARTITION BY range(sno) ( PARTITION p1 VALUES LESS THAN (10000) tablespace TBS_MING,  PARTITION p2 VALUES LESS THAN (20000) tablespace TBS_MING,  PARTITION p3 VALUES LESS THAN (maxvalue) tablespace TBS_MING ) tablespace TBS_MING; 插入数据： begin    for i in 1..30000 loop      insert into t_exchange values(i,sysdate,lpad('mingshuo',100,'x'));    end loop; dbms_output.put_line('success!'); commit; end;  / 创建一些不同类型的索引，观察分区交换是不是会对索引产生影响： 源表： 创建全局分区索引： create index t_exchange_idx_01 on t_exchange(sno,oitime,word) GLOBAL  partition by  range(sno) (  partition p01 values less than(10000) tablespace TBS_MING,  partition p02 values less than(20000) tablespace TBS_MING,  partition p03 values less than(MAXVALUE) tablespace TBS_MING ); 创建本地非前缀索引： create index t_exchange_idx_02 on t_exchange(word) local; 创建唯一索引： create unique index t_exchange_idx_03 on t_exchange(sno,oitime); 历史表： 创建全局分区索引： create index t_exchange_his_idx_01 on t_exchange_his(sno,oitime,word) GLOBAL  partition by  range(sno) (  partition p01 values less than(10000) tablespace TBS_MING,  partition p02 values less than(20000) tablespace TBS_MING,  partition p03 values less than(MAXVALUE) tablespace TBS_MING ); 创建本地非前缀分区索引： create index t_exchange__his_idx_02 on t_exchange_his(word) local; 源表与历史表的区别在于没有创建唯一索引。 创建中间表： create table t_exchange_tmp as select * from t_exchange where 1=2; SQL> !ora ddl ming table t_exchange_tmp \n=============Fri Jun 22 19:31:13 CST 2018===================\n Session altered. DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('T_EXCHANGE_TMP'),UPPER('MING')) -------------------------------------------------------------------------------  CREATE TABLE "MING"."T_EXCHANGE_TMP"   (    "SNO" NUMBER NOT NULL ENABLE,        "OITIME" DATE,        "WORD" VARCHAR2(100)   ) SEGMENT CREATION DEFERRED  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  TABLESPACE "TBS_MING" 插入数据之前开启10046事件跟踪： EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(50, 419, true); 将数据装载到中间表： alter table t_exchange exchange partition p1 with table t_exchange_tmp; 此时的数据： SQL> select count(*) from t_exchange_tmp;  COUNT(*) ----------      9999 SQL> select count(*) from t_exchange partition(p1);  COUNT(*) ----------         0 将数据加载到历史表： alter table t_exchange_his exchange partition p1 with table t_exchange_tmp; SQL> alter table t_exchange_his exchange partition p1 with table t_exchange_tmp; alter table t_exchange_his exchange partition p1 with table t_exchange_tmp * ERROR at line 1: ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION 创建唯一索引： create unique index t_exchange_his_idx_03 on t_exchange_his(sno,oitime); 再次加载数据，还是同样地错。 观察一下历史表和中间表的定义，unique约束只能是sno的主键约束了，这个说法不对，只是说明可能是主键约束的缘故，中间表增加主键约束： alter table t_exchange_tmp add primary key (sno); 再次加载数据： SQL> alter table t_exchange_his exchange partition p1 with table t_exchange_tmp; Table altered. 成功！ 关闭10046事件： EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(50, 419, false); 数据已经成功从中间表到了历史表： SQL> select count(*) from t_exchange_tmp;  COUNT(*) ----------         0 SQL> select count(*) from t_exchange_his partition(p1);  COUNT(*) ----------      9999 检查索引：   SQL> !ora unusable \n=============Fri Jun 22 19:55:13 CST 2018===================\n Session altered. UNUSABLE_INDEXES ------------------------------------------------------------------------- ALTER INDEX MING.SYS_C0012382 REBUILD ONLINE;             --中间表主键索引 ALTER INDEX MING.T_EXCHANGE_HIS_IDX_03 REBUILD ONLINE;    --历史表的唯一索引 ALTER INDEX MING.SYS_C0012380 REBUILD ONLINE;             --历史表主键索引 ALTER INDEX MING.T_EXCHANGE_IDX_03 REBUILD ONLINE;        --源表的唯一索引 ALTER INDEX MING.SYS_C0012378 REBUILD ONLINE;             --源表主键索引 ALTER INDEX MING.T_EXCHANGE__HIS_IDX_02 REBUILD PARTITION P1 ONLINE;   --历史表的本地非前缀分区索引 ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P03 ONLINE;   --历史表的全局分区索引 ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P02 ONLINE; ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P01 ONLINE;       --源表的全局分区索引 ALTER INDEX MING.T_EXCHANGE_IDX_02 REBUILD PARTITION P1 ONLINE;        --源表的本地非前缀分区索引 ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P01 ONLINE; ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P03 ONLINE; ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P02 ONLINE; 13 rows selected.       总结： 1.从10046中可以看到exchange的过程会以独占模式（exclusive）锁住两张表，不过执行速度很快，也不用担心阻塞业务dml语句。 2.10046中还考虑ogg和dataguard的影响，都是一些对系统表的修改。 3.装载到历史表的时候，需要两端的表上的约束都要一致。其实也很好理解，这个过程修改的数据字典中的物理位置指向，那么必然要满足约束的要求，否则加载过去的数据违反了表上的主键约束或者唯一约束，那就没有意义了。 4.所有的索引都失效了，可见这种方法的弊端是虽然数据加载快速，但是索引需要重建，这个就比较致命了。如果表很大的话，分区的可用性会变差，日常交易性能衰退，恢复需要的时间长。