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;

第三步，轉移資料

alter table SALE exchange partition  PY11Q3 with table SALE_exchange;

一個指令，将分區段和表段的空間指針互相替換，就實作了這個分區表的業務資料的轉移工作。

第四步，檢查索引狀态

分區表這點不好，如果分區發生改變，如exchange,move,split等，那麼該分區上的本地索引分區就會失效，同時整個分區表上的全局索引也會失效。是以我最開始說全局索引需要撤銷了，就是這個道理。試想，如果這時還有全局索引存在，那它失效了，這就将影響到分區表的其他業務操作。

不管怎樣，都需要檢查一下索引狀态。

select index_name, partition_name, status
  from user_ind_partitions
 where status = 'UNUSABLE'
union all
select index_name, '' as partition_name, status
  from user_indexes
 where status = 'UNUSABLE';
           

這裡，本地分區索引的索引分區肯定失效了。原來exchange表上的索引也會失效。它們都需要重建。

分區索引的重建：

alter index PK_SALE rebuild partition PY11Q3;

alter index PK_SALE_EXCHANGE rebuild;

經過這樣的操作之後，曆史資料是從目前業務表移出來了，但它在一個臨時的孤立的表中。在現實業務中，這樣的資料是需要移動曆史表中。當然，如果你有轉換操作，你可以繼續使用insert 方法轉移。如果沒有，你還可以使用分區交換技術，将這個資料交換到曆史表中。

簡而言之，Oracle分區表技術在大資料量表的操作中建議經常使用，它的很多特性有助于我們開發出高效的應用程式。