Use alter table ... merge partition to combine the data from two or more merge-compatible (that is, available for the merge) partitions into a single partition. Whether partitions are merge compatible depends on how they are partitioned:
For list-partitioned tables, any two partitions are merge-compatible
For range-partitioned tables, partitions must be adjacent to be merge-compatible
The syntax is:
alter table table_name
merge partition {partition_name [{, partition_name}…]}
into destination_partition_name [on segment_name]
where:
partition_name – the source partitions you are merging. All source partitions must be on the same segment.
destination_partition_name – a new or existing partition. If destination_partition_name is an existing partition, it cannot be any of the source partitions you are merging.
The partition condition for the merged destination partition is derived from the partition conditions of all the source data partitions being merged, so the destination partition includes all the data residing in the source data partitions being merged. For example, for a list-partitioned table, the new partition condition for the merged partition is the union of all the values that form the source data partition conditions.
See Reference Manual: Commands.
You must enable select into/bulkcopy to issue alter table ... merge partition.
alter table ... merge partition is fully logged. Use the transaction dump to recover from a server failure.
This example creates the sales table and then merges its partitions to consolidate the data:
create table sales(salesmanid int, salesdate datetime, salesregion varchar(10))
partition by range(salesdate)
( Q1 values <= ('31 Mar 2007'),
Q2 values <= ('30 Jun 2007'),
Q3 values <= ('30 Sep 2007'),
Q4 values <= ('31 Dec 2007'))
create index ind_region on sales(salesregion)
alter table sales
merge partition Q3
into Q4
alter table sales
merge partition Q1, Q2, Q3, Q4
into Y2007
