Partitioned Tables   «Prev  Next»

Lesson 10Splitting and merging partitions
ObjectiveSplit and merge partitions.

Splitting 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.

Splitting Partitions

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.

Merging Partitions Carousel

The following Slide Show illustrates the process of merging partitions:

  1. We will start with the same partitioned table.
  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.
  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.
Merging Partitions
The next lesson is the module conclusion.