Oracle11g維護分割槽（四）——Exchanging Partitions

Hoegh發表於2015-07-26
Oracle
 

交換分割槽

你可以透過交換資料段將一個分割槽(或子分割槽)轉化為一個非分割槽表，也可以將一個非分割槽錶轉化為一個分割槽表的分割槽(或子分割槽)。你也可以將一個雜湊分割槽錶轉化為一個複合雜湊分割槽表的一個分割槽，或者將一個複合雜湊分割槽表的一個分割槽轉化為一個雜湊分割槽表。類似的，你可以將一個【範圍|列表】分割槽錶轉化為一個複合【範圍|列表】分割槽表的一個分割槽，或者將一個複合【範圍|列表】分割槽表的一個分割槽轉化為一個【範圍|列表】分割槽表。

當你有一個需要將非分割槽錶轉化成一個分割槽表的分割槽的應用時，交換表分割槽是非常有用的。例如，在資料倉儲環境下，交換分割槽有利於將新增資料快速載入到一個現存分割槽表。一般來說，OLTP和資料倉儲都受益於從一個分割槽表中交換出舊的分割槽資料。該資料從分割槽表中清除，而無需實際刪除，並可以在以後分別歸檔。

當你交換分割槽時，日誌屬性將被保留。你可以指定本地索引是否也進行交換(INCLUDING INDEXES子句)，並且可以指定行是否以合適的對映進行驗證(WITH VALIDATION子句)。

注意：

當你給交換分割槽操作指定WITHOUT VALIDATION子句時，這通常是一個非常快速的操作，因為它只涉及到更新資料字典。但是，如果交換操作涉及到的表或者分割槽表啟用了主鍵或者唯一約束，那麼執行交換操作時會預設執行WITH VALIDATION選項來維持約束的完整性。

為了避免這種情況下驗證操作的開銷，在執行交換分割槽操作之前為每個約束執行以下語句：

點選(此處)摺疊或開啟

  1. ALTER TABLE table_name

  3.      DISABLE CONSTRAINT constraint_name KEEP INDEX;


然後，在交換分割槽操作完成後，重新啟用約束。

如果你指定了WITHOUT VALIDATION，那麼你必須確保要交換的資料屬於你執行交換操作的分割槽。

除非你指定了UPDATE INDEXES，否則資料庫會將執行交換分割槽所屬表的全域性索引或者所有全域性索引分割槽標識為UNUSABLE。執行交換分割槽所屬表的全域性索引或者所有全域性索引分割槽仍然無效(你不能在索引組織表上使用UPDATE INDEXES，可以使用UPDATE GLOBAL INDEXES來代替)。

交換一個範圍、雜湊或列表分割槽

如果要拿一個非分割槽表和範圍分割槽、雜湊分割槽或者列表分割槽表的一個分割槽進行交換，可以使用ALTER TABLE ... EXCHANGE PARTITION語句；反之亦然。下面是將一個分割槽轉化為一個非分割槽表的例子。在這個例子中，表stocks可以是範圍分割槽、雜湊分割槽或者列表分割槽表。

點選(此處)摺疊或開啟

  1. ALTER TABLE stocks

  3.     EXCHANGE PARTITION p3 WITH TABLE stock_table_3;


交換間隔分割槽表的一個分割槽

你可以在一個間隔分割槽表中交換間隔分割槽。但是，你必須確保在交換分割槽之前該間隔分割槽已經建立。你可以讓資料庫透過鎖定間隔分割槽來建立分割槽。

下面的例子顯示了interval_sales分割槽表的一個分割槽交換，該間隔分割槽表以月為間隔，就像2004年1月1日。這個例子顯示瞭如何透過交換分割槽向該間隔分割槽表載入新增2007年6月份的資料。我們假設interval_sales表只有本地索引，並且表interval_sales_june_2007上等價的索引已經被建立。

點選(此處)摺疊或開啟

  1. LOCK TABLE interval_sales

  3. PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))

  5. IN SHARE MODE;


  8. ALTER TABLE interval_sales

  10. EXCHANGE PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))

  12. WITH TABLE interval_sales_jun_2007

  14. INCLUDING INDEXES;


注意FOR語法的使用，是用來標識系統生成的一個分割槽。該分割槽名稱可用於透過查詢*_TAB_PARTITIONS資料字典檢視來查詢系統生成分割槽的名稱。

交換引用分割槽表的一個分割槽

你可以在一個引用分割槽表中交換分割槽，但是你必須確保在父表的相應分割槽中你引用的資料是可用的。

例4-30顯示了範圍分割槽表orders和引用分割槽表order_items的一個分割槽交換載入場景。注意order_items_dec_2006表的資料只包含order_date在2006月12月份的訂單的詳細資料。

點選(此處)摺疊或開啟

  1. 例4-30 給交換分割槽表交換一個分割槽

  3. ALTER TABLE orders

  5. EXCHANGE PARTITION p_2006_dec

  7. WITH TABLE orders_dec_2006

  9. UPDATE GLOBAL INDEXES;


  12. ALTER TABLE order_items_dec_2006

  14. ADD CONSTRAINT order_items_dec_2006_fk

  16. FOREIGN KEY (order_id)

  18. REFERENCES orders(order_id) ;


  21. ALTER TABLE order_items

  23. EXCHANGE PARTITION p_2006_dec

  25. WITH TABLE order_items_dec_2006;


需要注意的是，你必須在父表的交換分割槽上使用UPDATE GLOBAL INDEXES或者UPDATE INDEXES，這樣可以確保主鍵索引保持可用。另外，也要注意你必須在order_items_dec_2006表上建立或者啟用外來鍵約束，以便在引用分割槽表上的分割槽交換可以執行成功。

交換表的一個分割槽和虛擬列

你可以在包含虛擬列的情況下進行交換分割槽。為了使包含虛擬列的分割槽表的分割槽交換成功，你必須建立一個表，該表需要匹配分表中一個分割槽的所有非虛擬列的定義。你並不需要包括虛擬列定義，除非虛擬列上定義的有約束或者索引。

在這種情況下，你必須包含虛擬列定義，用來匹配分割槽表的約束和索引定義。該方案也適用於基於虛擬列的分割槽表。

交換一個雜湊分割槽表和一個複合雜湊分割槽

在這個例子中，你交換的是一個雜湊分割槽表(包含所有分割槽)和一個複合雜湊分割槽表的分割槽(包含所有子分割槽)。下面的例子說明了一個範圍雜湊分割槽表的定義。

首先，建立一個雜湊分割槽表：

點選(此處)摺疊或開啟

  1. CREATE TABLE t1 (i NUMBER, j NUMBER)

  3.      PARTITION BY HASH(i)

  5.        (PARTITION p1, PARTITION p2);


填充該表後，建立一個範圍雜湊分割槽表，如下：

點選(此處)摺疊或開啟

  1. CREATE TABLE t2 (i NUMBER, j NUMBER)

  3.      PARTITION BY RANGE(j)

  5.      SUBPARTITION BY HASH(i)

  7.         (PARTITION p1 VALUES LESS THAN (10)

  9.             SUBPARTITION t2_pls1

  11.             SUBPARTITION t2_pls2,

  13.          PARTITION p2 VALUES LESS THAN (20)

  15.             SUBPARTITION t2_p2s1

  17.             SUBPARTITION t2_p2s2));


其中，t1表的分割槽鍵等於t2表的子分割槽鍵，這一點很重要。

要將t1表的資料遷移到t2表，並且驗證行資料，使用以下語句：

點選(此處)摺疊或開啟

  1. ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1

  3.      WITH VALIDATION;


交換複合雜湊分割槽表的一個子分割槽

使用ALTER TABLE ... EXCHANGE SUBPARTITION語句將一個複合雜湊分割槽表的一個雜湊分割槽轉化為一個非分割槽表，反之亦然。下面的例子將sales表的子分割槽q3_1999_s1轉化為非分割槽表q3_1999。本地索引分割槽與分割槽q3_1999上相關的索引進行交換。

點選(此處)摺疊或開啟

  1. ALTER TABLE sales EXCHANGE SUBPARTITION q3_1999_s1

  3.       WITH TABLE q3_1999 INCLUDING INDEXES;



 

交換一個列表分割槽表和一個複合列表分割槽

ALTER TABLE ... EXCHANGE PARTITION語句的含義和上面“交換一個雜湊分割槽表和一個複合雜湊分割槽”章節的描述是一樣的。下面的例子顯示了一個列表-列表分割槽表的分割槽交換方案。

點選(此處)摺疊或開啟

  1. CREATE TABLE customers_apac

  3. ( id NUMBER

  5. , name VARCHAR2(50)

  7. , email VARCHAR2(100)

  9. , region VARCHAR2(4)

  11. , credit_rating VARCHAR2(1)

  13. )

  15. PARTITION BY LIST (credit_rating)

  17. ( PARTITION poor VALUES ('P')

  19. , PARTITION mediocre VALUES ('C')

  21. , PARTITION good VALUES ('G')

  23. , PARTITION excellent VALUES ('E')

  25. );


用亞太地區客戶填充該表，然後建立一個列表-列表分割槽表：

點選(此處)摺疊或開啟

  1. CREATE TABLE customers

  3. ( id NUMBER

  5. , name VARCHAR2(50)

  7. , email VARCHAR2(100)

  9. , region VARCHAR2(4)

  11. , credit_rating VARCHAR2(1)

  13. )

  15. PARTITION BY LIST (region)

  17. SUBPARTITION BY LIST (credit_rating)

  19. SUBPARTITION TEMPLATE

  21. ( SUBPARTITION poor VALUES ('P')

  23. , SUBPARTITION mediocre VALUES ('C')

  25. , SUBPARTITION good VALUES ('G')

  27. , SUBPARTITION excellent VALUES ('E')

  29. )

  31. (PARTITION americas VALUES ('AMER')

  33. , PARTITION emea VALUES ('EMEA')

  35. , PARTITION apac VALUES ('APAC')

  37. );
其中，customers_apac表的分割槽鍵等於customers表的子分割槽鍵，這一點很重要。


接下來，交換apac分割槽：

點選(此處)摺疊或開啟

  1. ALTER TABLE customers

  3. EXCHANGE PARTITION apac

  5. WITH TABLE customers_apac

  7. WITH VALIDATION;


 

交換複合列表分割槽表的一個子分割槽

ALTER TABLE ... EXCHANGE SUBPARTITION語句的含義和上面“交換複合雜湊分割槽表的一個子分割槽”章節的描述是一樣的。

交換一個範圍分割槽表和一個複合範圍分割槽

ALTER TABLE ... EXCHANGE PARTITION語句的含義和上面“交換一個雜湊分割槽表和一個複合雜湊分割槽”章節的描述是一樣的。下面的例子涉及到表orders，該表以order_date作為間隔分割槽鍵，並以列order_total作為範圍子分割槽鍵值。該例子顯示如何將一個單月間隔分割槽和一個範圍分割槽表進行交換。

點選(此處)摺疊或開啟

  1. CREATE TABLE orders_mar_2007

  3. ( id NUMBER

  5. , cust_id NUMBER

  7. , order_date DATE

  9. , order_total NUMBER

  11. )

  13. PARTITION BY RANGE (order_total)

  15. ( PARTITION p_small VALUES LESS THAN (1000)

  17. , PARTITION p_medium VALUES LESS THAN (10000)

  19. , PARTITION p_large VALUES LESS THAN (100000)

  21. , PARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)

  23. );


用2007年3月份的訂單資料填充該表，然後建立一個間隔-範圍複合分割槽表：

點選(此處)摺疊或開啟

  1. CREATE TABLE orders

  3. ( id NUMBER

  5. , cust_id NUMBER

  7. , order_date DATE

  9. , order_total NUMBER

  11. )

  13. PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

  15.   SUBPARTITION BY RANGE (order_total)

  17.   SUBPARTITION TEMPLATE

  19.   ( SUBPARTITION p_small VALUES LESS THAN (1000)

  21.   , SUBPARTITION p_medium VALUES LESS THAN (10000)

  23.   , SUBPARTITION p_large VALUES LESS THAN (100000)

  25.   , SUBPARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)

  27.   )

  29. (PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-

  31. MON-yyyy')));
其中，orders_mar_2007表的分割槽鍵等於orders表的子分割槽鍵，這一點很重要。


接下來，交換分割槽。需要注意的是，由於要交換一個間隔分割槽，因此首先要鎖定該分割槽以確保該分割槽被建立。

點選(此處)摺疊或開啟

  1. LOCK TABLE orders PARTITION FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))

  3. IN SHARE MODE;


  6. ALTER TABLE orders

  8. EXCHANGE PARTITION

  10. FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))

  12. WITH TABLE orders_mar_2007

  14. WITH VALIDATION;


交換複合範圍分割槽表的一個子分割槽

ALTER TABLE ... EXCHANGE SUBPARTITION語句的含義和上面“交換複合雜湊分割槽表的一個子分割槽”章節的描述是一樣的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-1752163/，如需轉載，請註明出處，否則將追究法律責任。

相關文章