ALTER TABLE MERGE PARTITION - PolarDB for Oracle - PolarDB

The ALTER TABLE… MERGE PARTITION statement merges two partitions into one, combining data from both.

Note

You must own the table or have administrative privileges to run this statement.
This statement supports only LIST and RANGE partitioned tables. HASH partitioned tables are not supported.
If indexes exist on the table, they are automatically created on the new partition.

Syntax

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

Parameters

Parameter	Description
`table_name`	Name of the partitioned table. You can include a schema qualifier.
`partition_name`	Names of the two partitions to merge, separated by a comma (,).
`new_part`	Name of the new partition. Must be unique across all partitions and subpartitions.
`tablespace_name`	Tablespace for the new partition. If omitted, the partition uses the default tablespace.

Examples

Create a partitioned table named sales and insert test data:

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');

Split the americas partition into us and canada:

ALTER TABLE sales SPLIT PARTITION americas 
  VALUES ('US')
  INTO (PARTITION us, PARTITION canada);

Merge us and canada into new_americas:

ALTER TABLE sales MERGE PARTITIONS us, canada
  INTO PARTITION new_americas;