Consolidate Subpartitions via ALTER TABLE MERGE SUBPARTITION - PolarDB

ALTER TABLE...MERGE SUBPARTITION merges two subpartitions into a single new subpartition that contains all data from both original subpartitions.

Limitations

Supported only on LIST and RANGE partitioned tables. HASH partitioned tables are not supported.
To run this statement, you must be the owner of the table or have administrative permissions on the table.
If indexes exist on the table, the indexes will be created on the new partition.

Syntax

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

Parameters

Parameter	Description
`table_name`	The name of the partitioned table. You can include a schema name.
`partition_name`	The names of the two subpartitions to merge, separated by a comma.
`new_part`	The name of the new subpartition. The name must be unique across all partitions and subpartitions in the table.
`tablespace_name`	The tablespace for the new subpartition. If omitted, the subpartition is created in the default tablespace.

Example

The following example creates a sales table partitioned by LIST on country, with RANGE subpartitions on date. It then splits a subpartition and merges the resulting subpartitions back into one.

Step 1: Create the table and insert data

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;

Step 2: Split a subpartition

Split americas_2012 at 2012-Jun-01 into two RANGE subpartitions:

ALTER TABLE sales
  SPLIT SUBPARTITION americas_2012
  AT('2012-Jun-01')
  INTO
  (
    SUBPARTITION americas_p1_2012,
    SUBPARTITION americas_p2_2012
  );

Step 3: Merge the subpartitions

Merge americas_p1_2012 and americas_p2_2012 into a new subpartition new_americas_2012:

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

The resulting subpartition new_americas_2012 contains all rows from both original subpartitions.