Partitioned Tables   «Prev 

Merging Partitions

Use the
ALTER TABLE ... MERGE PARTITION 
statement to merge the contents of two partitions into one partition. The two original partitions are dropped, as are any corresponding local indexes. You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite *-hash partitioned table.
You cannot merge partitions for a reference-partitioned table. Instead, a merge operation on a parent table will cascade to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the merge operation on the master table to merge partitions or subpartitions

Merging Partitions in Oracle

We will start with the same partitioned table.

The first step is to dump the data from the partitions that will be merged.

The next step is to drop the partitions that will be merged. This action will also drop the partitions of the local index.

The next step is to create a new partition. This action will also create the corresponding index partition for the local index.

Once the partition is created, you reload the dumped data into the table, which will also create the index entries.