Merging Range Partitions

You are allowed to merge the contents of two adjacent range partitions into one partition. Nonadjacent range partitions cannot be merged. The resulting partition inherits the higher upper bound of the two merged partitions.

One reason for merging range partitions is to keep historical data online in larger partitions. For example, you can have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.

Example 4-29 shows an example of merging range partitions.

If you omit the UPDATE INDEXES clause from the preceding statement, then you must rebuild the local index for the affected partition.

-- 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 corrects this.
--
ALTER TABLE four_seasons MODIFY PARTITION 
quarter_two REBUILD UNUSABLE LOCAL INDEXES;

Example 4-29 Merging range partitions

-- First, create a partitioned 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
);

-- Next, merge the first two partitions 
ALTER TABLE four_seasons 
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;