Partitioned Tables   «Prev  Next»

Lesson 10Splitting and merging partitions
ObjectiveSplit and merge partitions.

Splitting and Merging Oracle Partitions

You create partitioned tables in order to increase performance and availability and reduce maintenance time. However, changing conditions or an incorrect initial design may require you to alter the way the partitions are divided. If this occurs, one option is to either split one partition into two, or merge two partitions into one.
You can split a partition into two separate partitions with the command: 
ALTER TABLE table_name SPLIT PARTITION partition_name
   AT ( '40-001' )
  INTO ( PARTITION new_partition_name1
         PARTITION new_partition_name2)

This command will also split the associated local index partition. All affected index partitions, both local and global, will be marked as unusable and will have to be rebuilt.

Merging Partitions

Merging partitions is not as simple as the other partitioning modifications discussed so far. There is not a single command that will merge partitions. Instead, you must go through a four-step process:
  1. Dump the data from the two partitions to be merged into a dump file
  2. Drop the partitions you want to merge from the table
  3. Add the new partitions you want to create to the table
  4. Reload the data from the dump file

As with the modifications above, any local indexes will be automatically dropped and recreated, and any affected global index partitions or any nonpartitioned indexes will be marked as unusable.
The following series of images illustrate the process of merging partitions:

Merging Partitions in Oracle

1) Start with the same partitioned table
1) We will start with the same partitioned table.

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

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

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

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

Merging Oracle 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
The next lesson is the module conclusion.

SEMrush Software