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
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 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:
- Dump the data from the two partitions to be merged into a dump file
- Drop the partitions you want to merge from the table
- Add the new partitions you want to create to the table
- 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:
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.