PolarDB:Coalesce partitions

Overview

During a coalescence, the data in the involved hash partitions is redistributed. Each coalescence affects the data distribution of two partitions.

Syntax

-- drop partition
ALTER TABLE <table_name> COALESCE PARTITION <partition_name>;
-- drop a subpartition
ALTER TABLE <table_name> COALESCE SUBPARTITION <subpartition_name>;

Example

Coalescing a partitioned table merges two partitions of a partitioned table into one.

Create a partitioned table

CREATE TABLE hash_partitions_sales (deptno NUMBER, deptname VARCHAR(32))
     PARTITION BY HASH(deptno)
       (PARTITION p1 , PARTITION p2 ,
        PARTITION p3 , PARTITION p4 , PARTITION p5, PARTITION p6, PARTITION p7, PARTITION p8);

Coalesce partitions

ALTER table hash_partitions_sales coalesce PARTITION;

View the results

SELECT * FROM all_part_tables ;

As is shown in the following results, the number of partitions is reduced form 8 to 7.

 owner | schema |      table_name       | partitioning_type | partition_count | partitioning_key_count | def_tablespace_name | def_logging 
-------+--------+-----------------------+-------------------+-----------------+------------------------+---------------------+-------------
 P     | PUBLIC | HASH_PARTITIONS_SALES | HASH              |               7 |                      1 | DEFAULT             | YES
(1 row)