MODIFY…PARTITION…TO MERGE
The MODIFY…PARTITION…TO MERGE WITH PARTITION= statement merges two or more partitions into fewer partitions.
This modify operation drops all secondary indexes and rebuilds persistent indexes.
Syntax:
MODIFY tablename PARTITION partition-name [,partition-name] TO MERGE WITH PARTITION=(dimension)
where:
tablename
Specifies a range-partitioned table.
partition_name
Specifies a logical partition name. Partition names in a list must be in a single dimension.
Note: Logical partition names can be found in table iilpartitions.partition_name in iidbdb.
dimension
Specifies the new partitions to replace the specified partitions. For the syntax of PARTITION= specification, see
Partitioning Schemes.
The distribution type (dimension rule) must be RANGE.
Multiple partitions can be specified in the PARTITION clause; the ranges in the new partition definition, however, must completely cover the ranges in the logical partitions being modified.
For example, given the following table:
CREATE TABLE t (aa INT, bb INT) WITH PARTITION = (
RANGE ON bb
PARTITION "bb1a" VALUES <= 15,
PARTITION "bb1b" VALUES <= 20,
PARTITION "bb2a" VALUES <= 35,
PARTITION "bb2b" VALUES <= 40,
PARTITION "bb3a" VALUES <= 45,
PARTITION "bb3b" VALUES > 45
)
the following statement merges logical partitions bb2a and bb2b of table t into one partition where all values in column bb are less than or equal to 40:
MODIFY t PARTITION bb2a,bb2b TO MERGE WITH PARTITION=(RANGE ON bb PARTITION new VALUES <=40)
Only the logical partitions listed will be processed. The list of partitions to merge must be contiguous. The replacement partition definition must exactly match the range defined by those logical partitions. The MODIFY will change the number of partitions and how the data is divided among the new partitions.
If the partitioning definition is multi-dimensional (that is, defined with subpartitions), you can merge partitions in any dimension. All logical partitions specified, however, must be in a single dimension.
If you use a WITH LOCATION clause, the partition (and its sub-partitions) are created in the locations specified in the WITH LOCATION clause. If you do not use a WITH LOCATION clause, the partition (and its sub-partitions) are created in the table locations.
Last modified date: 11/09/2022