Splitting and Merging Partitions in Oracle 19c

In Oracle 19c, partitioning is a powerful feature that allows tables and indexes to be divided into smaller, more manageable pieces while still appearing as a single object to users. The processes for 1) splitting one partition into two partitions and 2) merging two partitions into one involve specific operations that modify the partitioning structure of a table or index.
1. Splitting One Partition into Two
Overview:

• Splitting a partition involves dividing an existing partition into two, based on a specific boundary value.

Steps:

1. Use the ALTER TABLE Command:
   • Specify the partition to be split and the new boundary values for the two resulting partitions.
   • Optionally, you can define different tablespaces for the resulting partitions.
2. Syntax:

         ALTER TABLE table_name
         SPLIT PARTITION partition_name
         AT (boundary_value)
         INTO (
           PARTITION new_partition_1 TABLESPACE tablespace_1,
           PARTITION new_partition_2 TABLESPACE tablespace_2
         );
       

3. Example: Suppose you have a table sales partitioned by range on the sales_date column, and you want to split the p1 partition into two partitions at the date 01-JAN-2023:

         ALTER TABLE sales
         SPLIT PARTITION p1
         AT (TO_DATE('01-JAN-2023', 'DD-MON-YYYY'))
         INTO (
           PARTITION p1_1 TABLESPACE ts1,
           PARTITION p1_2 TABLESPACE ts2
         );
       

4. Result:
   • The original p1 partition is divided into p1_1 and p1_2.
   • Data is redistributed based on the specified boundary value.

2. Merging Two Partitions into One
Overview:

• Merging partitions combines two adjacent partitions into one, removing the boundary between them.

Steps:

1. Use the ALTER TABLE Command:
   • Specify the two partitions to merge.
2. Syntax:

   ALTER TABLE table_name
   MERGE PARTITIONS partition_1, partition_2
   INTO PARTITION new_partition_name
   TABLESPACE tablespace_name;
       

3. Example: Suppose you have a table sales partitioned by range, and you want to merge the p1 and p2 partitions into a new partition called p_merged:

   ALTER TABLE sales
   MERGE PARTITIONS p1, p2
   INTO PARTITION p_merged
   TABLESPACE ts_merged;
       

4. Result:
   • The p1 and p2 partitions are combined into p_merged.
   • Data from both partitions is consolidated into a single partition.

Key Considerations:

1. Data Redistribution:
   • In both operations, Oracle redistributes or consolidates data as necessary. Ensure there is sufficient space in the target tablespace(s).
2. Partition Boundaries:
   • For splitting, define the boundary value correctly to ensure proper data distribution.
   • For merging, the partitions must be adjacent, i.e., share a common boundary.
3. Index Management:
   • If the table has local indexes, they are automatically managed during partition operations.
   • Global indexes may require maintenance or rebuilding after the operation.
4. Performance Implications:
   • Both operations can be resource-intensive, especially for large partitions. Consider performing them during maintenance windows.
5. Backup and Testing:
   • Always back up the table before making structural changes.
   • Test the operations in a non-production environment to ensure correctness.

These operations allow flexibility in managing partitioned tables and indexes, enabling efficient organization and querying of data in Oracle 19c.

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:

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.