How to Merge Multiple Partitions in Oracle 12c

The following are the partitioning related enhancements introduced in Oracle 12c:

• Online partition operations
• Reference partitioning enhancements
• Multi-partition maintenance operations
• Adding multiple partitions
• Truncating multiple partitions
• Merging multiple partitions
• Splitting into multiple partitions
• Dropping multiple partitions

Merging multiple partitions

In Oracle 11g, you can merge the content of two partitions only in single DDL. However, in Oracle 12c, you can merge multiple partitions into one in a single DDL statement. After merging the content of multiple partitions into a single partition, you can drop the original partitions that are merged.

The post demonstrates Oracle 12c new feature that allows merging multiple partitions or subpartitions into a single partition or single subpartition.

Merging Range partitions/subpartitions

When merging multiple range partitions, the partitions must be adjacent and specified in the ascending order of their partition bound values. The new partition inherits the partition upper bound of the highest of the original partitions.

CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod_1 VALUES LESS THAN (100),
PARTITION prod_2 VALUES LESS THAN (200),
PARTITION prod_3 VALUES LESS THAN (300),
PARTITION prod_4 VALUES LESS THAN (400),
PARTITION prod_5 VALUES LESS THAN (500),
PARTITION prod_6 VALUES LESS THAN (600),
PARTITION prod_other VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTO partition old;

or if using a subpartitioned table:

ALTER TABLE prod_list MERGE SUBPARTITIONS prod_1,prod_2,prod_3 INTO subpartition old;

In the above example, you merge the three partitions of the prod_list Range-partitioned table. These three partitions that correspond to the earliest product ids are merged into a single partition containing the oldest products no longer being sold. You can specify the lowest and the highest partitions to be merged when merging multiple range partitions with the ‘TO‘ syntax. All partitions between the specified partitions, including those specified, are merged into the target partition.

ALTER TABLE prod_list MERGE PARTITIONS prod_1 TO prod_3 INTO partition old;

Merging List or System partitions/subpartitions

List and System partitions that you want to merge do not need to be adjacent, because no ordering of the partitions is assumed. Additionally, you cannot use a range of partitions with ‘TO’ syntax for list and system partitions and subpartitions. When merging multiple List partitions, the resulting partition value list is the union of the set of partition value list of all of the partitions to be merged.

A DEFAULT List partition merged with other list partitions results in a DEFAULT partition.

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_CA VALUES('California'),
PARTITION sales_NY VALUES ('New York'),
PARTITION sales_NJ VALUES ('New Jersey'),
PARTITION sales_CT VALUES ('Connecticut'),
PARTITION sales_PA VALUES ('Pennsylvania'),
PARTITION sales_IL VALUES('Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list MERGE PARTITIONS sales_NY, sales_NJ, sales_CT INTO PARTITION TRI_STATE;

and for subpartitioned tables:

ALTER TABLE sales_list MERGE SUBPARTITIONS sales_NY, sales_NJ, sales_CT INTO SUBPARTITION TRI_STATE;