Use COALESCE PARTITION to reduce the number of partitions in a hash-partitioned table by merging two partitions into one.
How data redistribution works
Each coalesce operation affects two partitions: the data in the involved hash partitions is redistributed across the remaining partitions.
Syntax
ALTER TABLE <table_name> COALESCE PARTITION;Example
The following example creates a hash-partitioned table with eight partitions and then coalesces one partition, reducing the total from eight to seven.
Create the 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 a partition
ALTER table hash_partitions_sales coalesce PARTITION;Verify the result
Query all_part_tables to confirm the partition count:
SELECT * FROM all_part_tables;The output shows that the partition count decreased from 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)