Oracle分區表的分區互動技術實現資料快速轉移

有一個需求，將某業務表的某個時間點之前的記錄轉移到它的曆史表中。如果當前業務表不是基於這 個業務時間點的分區表設定，那隻能insert再delete操作。這種轉移資料的方法非常非常低基礎。經常 在初級的資料庫管理員和開發人員的程式中出現。不是說這個方法不好，對於轉移的記錄數量在幾十 幾百條，而轉移頻率高，轉移時間點隨機的情況而言，這個方法還是挺管用的。但如果轉移的資料量一 次數以百萬計的話，這種方法就顯得低效了。

因此，在Oracle資料庫開發中，對於這種大資料的轉移可以使用分區表交換技術實現。即使你一次轉 移的資料量幾億甚至幾十億也沒有關係，轉移時間依然是毫秒級的。這個方法大體流程是這樣：

首先，你需要將當前表修改為分區表，找到分區欄位很關鍵；其次，這個分區表的索引都建立成本地 索引，全域索引就不要了，原因後面介紹；再次，建立一個對應的臨時非分區表，表結構和這個一樣； 最後使用alter table table_name exchange partition  Partition_name with table table_name_exchange;操作，將表分區所擁有資料的實際實體儲存體空間段相互交換，這是指標級的操作 。

這樣就完成了這個表分區資料的快速轉移。

就這個操作流程，做一個測試。

(miki西遊 @mikixiyou 原文連結: http://mikixiyou.iteye.com/blog/1773659)

第一步，準備環境

建立一張測試表SALE，它的分區欄位是DOTIME，按照季度進行分區。

CREATE TABLE SALE  (    DOTIME          DATE                          DEFAULT sysdate,    BILLID          VARCHAR2(20 BYTE)             NOT NULL,    FROMARREAR      NUMBER(16,4)                  DEFAULT 0  )  PARTITION BY RANGE (DOTIME)  (     PARTITION PY11Q3 VALUES LESS THAN (TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))      LOGGING,     PARTITION PY11Q4 VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))      LOGGING,     PARTITION PY12Q1 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))      LOGGING,     PARTITION PY12Q2 VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))      LOGGING,     PARTITION P_MAX VALUES LESS THAN (MAXVALUE)      LOGGING  )  ;

再建立一張交換表，表的欄位結構和分區表完全一致。

create table SALE_exchange  (    DOTIME          DATE                          DEFAULT sysdate,    BILLID          VARCHAR2(20 BYTE)             NOT NULL,    FROMARREAR      NUMBER(16,4)                  DEFAULT 0  );

注意，分區表上的主鍵所屬的全域唯一索引不要了，改成SALE (BILLID，DOTIME)上的本地索引，這 樣也能保證資料的一致性。原來的主鍵欄位billid必須放在前面，防止原來原來基於billid直接查詢操 作的效能下降太多。

create unique index PK_SALE on SALE (BILLID，DOTIME) local;

本地分區索引建立完畢。

第二步，

檢查一下資料記錄情況。假設我們要將PY11Q3分區中的記錄轉移走。

select count(*) from SALE partition(PY11Q3);

select  count(*)  from SALE_exchange;