Merge partitions

PolarDB for PostgreSQL (Compatible with Oracle) supports merging partitions or subpartitions. This topic describes the syntax and provides examples.

Syntax

Use the ALTER TABLE … MERGE PARTITION command to merge two partitions into one that contains all the data of the partitions.

ALTER TABLE table_name MERGE PARTITIONS
		partition_name , partition_name 
		INTO PARTITION  new_part [TABLESPACE tablespace_name] ;

Note

This command is supported only for list- and range-partitioned tables. Hash-partitioned tables are not supported.
A table can have a limitless number of partitions.
When executing an ALTER TABLE ... MERGE PARTITION statement, the system creates a new partition that contains the data of the original partitions. The TABLESPACE clause specifies the tablespace in which the partition resides. If no tablespace is specified, the subpartition resides in the default tablespace. If the table already has indexes, the indexes will also be created on the new partition.
You can execute the ALTER TABLE ... MERGE PARTITION statement only if you are the owner of the table or have the permissions of a superuser or administrator.

Merge subpartitions

Use the ALTER TABLE … MERGE SUBPARTITION command to merge two subpartitions into one that contains all the data of the subpartitions.

ALTER TABLE table_name MERGE SUBPARTITIONS
		partition_name , partition_name 
		INTO SUBPARTITION  new_part [TABLESPACE tablespace_name] ;

Note

This command is supported only for list- and range-subpartitions. Hash-subpartitions are not supported.
A table can have a limitless number of partitions.
When executing an ALTER TABLE ... MERGE SUBPARTITION statement, the system creates a new subpartition that contains the data of the original subpartitions. The TABLESPACE clause specifies the tablespace in which the subpartition resides. If no tablespace is specified, the subpartition resides in the default tablespace. If the table already has indexes, the indexes will also be created on the new subpartition.
You can execute the ALTER TABLE ... MERGE SUBPARTITION statement only if you are the owner of the table or have the permissions of a superuser or administrator.

Examples

Merge partitions

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);
INSERT INTO sales VALUES
  (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
  (20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
  (40, '9519b', 'US', '12-Apr-2012', '145000'),
  (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
  (40, '4577b', 'US', '11-Nov-2012', '25000'),
  (30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
  (30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
  (30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
  (40, '3788a', 'US', '12-May-2012', '4950'),
  (10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
  (10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
  (10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
  (20, '3788b', 'INDIA', '21-Sept-2012', '5090'),
  (40, '4788a', 'US', '23-Sept-2012', '4950'),
  (40, '4788b', 'US', '09-Oct-2012', '15000'),
  (20, '4519a', 'INDIA', '18-Oct-2012', '650000'),
  (20, '4519b', 'INDIA', '2-Dec-2012', '5090');
  
ALTER TABLE sales SPLIT PARTITION americas 
  VALUES ('US')
	INTO (PARTITION us, PARTITION canada);
 
ALTER TABLE sales MERGE PARTITIONS us, canada
	INTO PARTITION americas;

Merge subpartitions

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
  SUBPARTITION BY RANGE(date)
(
  PARTITION europe VALUES('FRANCE', 'ITALY')
    (
      SUBPARTITION europe_2011 
        VALUES LESS THAN('2012-Jan-01'),
      SUBPARTITION europe_2012 
        VALUES LESS THAN('2013-Jan-01')
    ),
  PARTITION asia VALUES('INDIA', 'PAKISTAN')
    (
      SUBPARTITION asia_2011 
        VALUES LESS THAN('2012-Jan-01'),
      SUBPARTITION asia_2012 
        VALUES LESS THAN('2013-Jan-01')
    ),
  PARTITION americas VALUES('US', 'CANADA')
    (
      SUBPARTITION americas_2011 
        VALUES LESS THAN('2012-Jan-01'),
      SUBPARTITION americas_2012 
        VALUES LESS THAN('2013-Jan-01')
    )
);
SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS where table_name = 'SALES';
INSERT INTO sales VALUES
  (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
  (20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
  (40, '9519b', 'US', '12-Apr-2012', '145000'),
  (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
  (40, '4577b', 'US', '11-Nov-2012', '25000'),
  (30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
  (30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
  (30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
  (40, '3788a', 'US', '12-May-2012', '4950'),
  (10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
  (10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
  (10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
  (20, '3788b', 'INDIA', '21-Sept-2012', '5090'),
  (40, '4788a', 'US', '23-Sept-2012', '4950'),
  (40, '4788b', 'US', '09-Oct-2012', '15000'),
  (20, '4519a', 'INDIA', '18-Oct-2012', '650000'),
  (20, '4519b', 'INDIA', '2-Dec-2012', '5090');
SELECT tableoid::regclass, * FROM sales;
  
ALTER TABLE sales 
  SPLIT SUBPARTITION americas_2012 
  AT('2012-Jun-01')
  INTO
  (
    SUBPARTITION americas_p1_2012, 
    SUBPARTITION americas_p2_2012
  );
  


ALTER TABLE sales 
  MERGE SUBPARTITIONS americas_p1_2012 ,americas_p2_2012
  INTO  SUBPARTITION americas_2012;