Oracle 11g: 合并分區表-merge partition

Merging Partitions

使用ALTER TABLE .. MERGE PARTITION語句將兩個分區的內容合并到另外一個分區，兩個來源資料分割和關聯的local index都會被drop
　　不能用於 hash分區表或 hash subpartitions of a composite *-hash partitioned table
　　不能用於合并引用分區表(reference-partitioned table)
合并定界分割
　　允許合并兩個臨近範圍的分區到另外分區，不相鄰分區無法合并。合并結果分區繼承兩個來源資料分割的最大邊界。
demo:

-- Create a Table with four partitions each on its own tablespace-- Partitioned by range on the data column.CREATE TABLE four_seasons(         one DATE,        two VARCHAR2(60),        three NUMBER)PARTITION  BY RANGE ( one ) (PARTITION quarter_one    VALUES LESS THAN ( TO_DATE('01-apr-1998','dd-mon-yyyy'))   TABLESPACE quarter_one,PARTITION quarter_two    VALUES LESS THAN ( TO_DATE('01-jul-1998','dd-mon-yyyy'))   TABLESPACE quarter_two,PARTITION quarter_three   VALUES LESS THAN ( TO_DATE('01-oct-1998','dd-mon-yyyy'))   TABLESPACE quarter_three,PARTITION quarter_four   VALUES LESS THAN ( TO_DATE('01-jan-1999','dd-mon-yyyy'))   TABLESPACE quarter_four);-- Create local PREFIXED index on Four_Seasons-- Prefixed because the leftmost columns of the index match the-- Partitioning key CREATE INDEX i_four_seasons_l ON four_seasons ( one,two ) LOCAL ( PARTITION i_quarter_one TABLESPACE i_quarter_one,PARTITION i_quarter_two TABLESPACE i_quarter_two,PARTITION i_quarter_three TABLESPACE i_quarter_three,PARTITION i_quarter_four TABLESPACE i_quarter_four);

下一步，合并分區.

-- Merge the first two partitions --ALTER TABLE four_seasons MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_twoUPDATE INDEXES;

如果不顯式聲明 UPDATE INDEXES 語句,就必須給受影響的分區 rebuild the local index.

-- Rebuild index for quarter_two, which has been marked unusable -- because it has not had all of the data from Q1 added to it.-- Rebuilding the index will correct this.--ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE LOCAL INDEXES;

合并Interval Partitions 

同RANGE一樣，必須兩個相鄰分區才能合并
合并 List partitions則沒有限制