oracle分區(qū)交換（exchange）技術(shù)

需求：一個(gè)幾億條數(shù)據(jù)的分區(qū)表，要把一些老數(shù)據(jù)分出來，放一個(gè)新建的表上。按時(shí)間劃分，指定時(shí)間以前的放在老數(shù)據(jù)表，指定時(shí)間以后的數(shù)據(jù)保留在原表，然后把備份老數(shù)據(jù)表空間truncate掉。按照分區(qū)操作分區(qū)交換是最好的解決辦法。

分區(qū)交換技術(shù)可以實(shí)現(xiàn)數(shù)據(jù)快速轉(zhuǎn)移，所以在數(shù)據(jù)加載提速，歷史數(shù)據(jù)清理等方面特別有用。分區(qū)交換技術(shù)實(shí)際上只修改了數(shù)據(jù)字典中的數(shù)據(jù)物理段位置，而不是實(shí)際的移動(dòng)數(shù)據(jù)，所以速度很快。

創(chuàng)建分區(qū)表：
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;

歷史分區(qū)表：
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;

插入數(shù)據(jù)：
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; 
/

創(chuàng)建一些不同類型的索引，觀察分區(qū)交換是不是會(huì)對(duì)索引產(chǎn)生影響：
源表：
創(chuàng)建全局分區(qū)索引：
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
);

創(chuàng)建本地非前綴索引：
create index t_exchange_idx_02 on t_exchange(word) local;

創(chuàng)建唯一索引：
create unique index t_exchange_idx_03 on t_exchange(sno,oitime);

歷史表：
創(chuàng)建全局分區(qū)索引：
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
);

創(chuàng)建本地非前綴分區(qū)索引：
create index t_exchange__his_idx_02 on t_exchange_his(word) local;

源表與歷史表的區(qū)別在于沒有創(chuàng)建唯一索引。
創(chuàng)建中間表：
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"
插入數(shù)據(jù)之前開啟10046事件跟蹤：
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(50, 419, true);
將數(shù)據(jù)裝載到中間表：
alter table t_exchange exchange partition p1 with table t_exchange_tmp; 此時(shí)的數(shù)據(jù)：
SQL> select count(*) from t_exchange_tmp;

 COUNT(*)
----------
     9999

SQL> select count(*) from t_exchange partition(p1);

 COUNT(*)
----------
        0 將數(shù)據(jù)加載到歷史表：
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

創(chuàng)建唯一索引：
create unique index t_exchange_his_idx_03 on t_exchange_his(sno,oitime); 再次加載數(shù)據(jù)，還是同樣地錯(cuò)。
觀察一下歷史表和中間表的定義，unique約束只能是sno的主鍵約束了，這個(gè)說法不對(duì)，只是說明可能是主鍵約束的緣故，中間表增加主鍵約束：
alter table t_exchange_tmp add primary key (sno);

再次加載數(shù)據(jù)：
SQL> alter table t_exchange_his exchange partition p1 with table t_exchange_tmp;

Table altered.
成功！ 關(guān)閉10046事件：
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(50, 419, false); 數(shù)據(jù)已經(jīng)成功從中間表到了歷史表：
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;   --歷史表的本地非前綴分區(qū)索引
ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P03 ONLINE;   --歷史表的全局分區(qū)索引
ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P02 ONLINE;
ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P01 ONLINE;       --源表的全局分區(qū)索引
ALTER INDEX MING.T_EXCHANGE_IDX_02 REBUILD PARTITION P1 ONLINE;        --源表的本地非前綴分區(qū)索引
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.  
 
 
總結(jié)：
1.從10046中可以看到exchange的過程會(huì)以獨(dú)占模式（exclusive）鎖住兩張表，不過執(zhí)行速度很快，也不用擔(dān)心阻塞業(yè)務(wù)dml語(yǔ)句。
2.10046中還考慮ogg和dataguard的影響，都是一些對(duì)系統(tǒng)表的修改。
3.裝載到歷史表的時(shí)候，需要兩端的表上的約束都要一致。其實(shí)也很好理解，這個(gè)過程修改的數(shù)據(jù)字典中的物理位置指向，那么必然要滿足約束的要求，否則加載過去的數(shù)據(jù)違反了表上的主鍵約束或者唯一約束，那就沒有意義了。
4.所有的索引都失效了，可見這種方法的弊端是雖然數(shù)據(jù)加載快速，但是索引需要重建，這個(gè)就比較致命了。如果表很大的話，分區(qū)的可用性會(huì)變差，日常交易性能衰退，恢復(fù)需要的時(shí)間長(zhǎng)。