Merging Partitions
Use the alter table.. merge partition statement to MERGE the content of the two partitions into the other PARTITION. Both the source partitions and the associated local indexes will be dropped.
Cannot be used for hash partition tables or hash subpartitions of a composite *-hash partitioned table
It cannot be used to merge and reference partition tables (reference-partitioned table)
Merge range partitions
Two Adjacent partitions can be merged to another partition. Non-adjacent partitions cannot be merged. The merged result partition inherits the maximum boundary of the two source partitions.
Demo:
-- Create a Table with four partitions each on its own tablespace
-- Partitioned by range on the data column.
Create table four_seasons
(
One DATE,
Two VARCHAR2 (60 ),
Three NUMBER
)
Partition by range (one)
(
PARTITION quarter_one
Values less than (TO_DATE ('01-apr-1998 ', 'dd-mon-yyyy '))
TABLESPACE quarter_one,
PARTITION quarter_two
Values less than (TO_DATE ('01-jul-1998 ', 'dd-mon-yyyy '))
TABLESPACE quarter_two,
PARTITION quarter_three
Values less than (TO_DATE ('01-oct-1998 ', 'dd-mon-yyyy '))
TABLESPACE quarter_three,
PARTITION quarter_four
Values less than (TO_DATE ('01-jan-1999 ', 'dd-mon-yyyy '))
TABLESPACE quarter_four
);
-- Create local PREFIXED index on Four_Seasons
-- Prefixed because the leftmost columns of the index match
-- Partitioning key create index I _four_seasons_l ON four_seasons (one, two)
LOCAL (
PARTITION I _quarter_one TABLESPACE I _quarter_one,
PARTITION I _quarter_two TABLESPACE I _quarter_two,
PARTITION I _quarter_three TABLESPACE I _quarter_three,
PARTITION I _quarter_four TABLESPACE I _quarter_four
);
Next, merge partitions.
-- Merge the first two partitions
--
Alter table four_seasons
Merge partitions quarter_one, quarter_two into partition quarter_two
Update indexes;
If the update indexes statement is not explicitly declared, the affected partition must be rebuilt with the local index.
-- Rebuild index for quarter_two, which has been marked unusable
-- Because it has not had all of the data from Q1 added to it.
-- Rebuilding the index will correct this.
--
Alter table four_seasons MODIFY PARTITION
Quarter_two rebuild unusable local indexes;
Merge Interval Partitions
Like RANGE, two adjacent partitions must be merged.
Merging List partitions is not limited.