How to Split a Partition Into Multiple Partitions in Oracle 12c

The following are the partitioning related enhancements introduced in Oracle 12c:

• Online partition operations
• Reference partitioning enhancements
• Multi-partition maintenance operations
• Adding multiple partitions
• Truncating multiple partitions
• Merging multiple partitions
• Splitting into multiple partitions
• Dropping multiple partitions

Splitting Partition into multiple partitions

You can split a partition into multiple partitions. In previous releases, you were able to split partitions into two partitions only in a single DDL. But in Oracle 12c, you can now split a partition into multiple partitions.

Splitting into Multiple Partitions Rules

You can redistribute the contents of one table partition into multiple partitions with the SPLIT PARTITION clause of the ALTER TABLE statement. When splitting multiple partitions, the segment associated with the current partition is discarded. Each new partition obtains a new segment and inherits all of the unspecified physical attributes from the current source partition.

You can use this new extended split syntax to specify a list of new partition descriptions similar to the create partitioned table SQL statements, instead of using the AT or VALUES clauses. Additionally, the Range or List values clause for the last new partition description is derived based on the high bound of the source partition and the bound values specified for the first (N-1) new partitions resulting from the split.

When splitting a DEFAULT List partition or a MAXVALUE Range partition into multiple partitions, the first (N-1) new partitions are created using the literal value lists or high bound values specified, while the Nth new partition resulting from the split have the DEFAULT value or MAXVALUE. The SPLIT_TABLE_SUBPARTITION clause is extended similarly to allow splitting of a Range or List subpartition into N new subpartitions.

This post demonstrates the new feature in 12c that makes possible to split an Oracle table partition or subpartition into multiple partitions/subpartitions with one statement.

Splitting RANGE partitions

The first example demonstrates splitting a range partition prod100 into multiple partitions, namely prod25, prod50, prod74, and prod100. Partition prod100 derives the high bound of the original partition prod100.

CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod100 VALUES LESS THAN (100),
PARTITION prod200 VALUES LESS THAN (200),
PARTITION prod300 VALUES LESS THAN (300),
PARTITION prodother VALUES LESS THAN (MAXVALUE)
);

alter table prod_list split partition prod100 into 
(partition prod25 values less than (25),
partition prod50 values less than (50),
partition prod75 values less than (75),
partition prod100);

Same example using subpartitions, assuming prod100 is a subpartition:

alter table prod_list split subpartition prod100 into 
(subpartition prod25 values less than (25),
subpartition prod50 values less than (50),
subpartition prod75 values less than (75),
subpartition prod100);

Here is an example using a date range table range_sales and we split the sales_q1_2012 partition into five partitions corresponding to the quarters of the next year. In this example, the partition sales_mar_2012 implicitly becomes the high bound of the split partition.

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));

ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO
   (PARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')),
   PARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')),
   PARTITION sales_mar_2012);

Same example using subpartitions, assuming sales_q1_2012 is a subpartition.

ALTER TABLE range_sales SPLIT SUBPARTITION sales_q1_2012 INTO
(SUBPARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')),
SUBPARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')),
SUBPARTITION sales_mar_2012);

To split a Range partition into N partitions, (N-1) values of the partitioning key column must be specified within the range of the partition at which to split the partition. The new non-inclusive upper bound values specified must be in ascending order. The high bound of Nth new partition is assigned the value of the high bound of the partition being split. The names and physical attributes of the N new partitions resulting from the split can be optionally specified.

Splitting LIST partitions

In this example, the sample table customers partitioned by List, splits the partition sales_east into three partitions: NY, FL, and VA.

CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list SPLIT PARTITION sales_east INTO
  (PARTITION NY values ('New York'),
   PARTITION FL values ('Florida'),
   PARTITION rest_sales_east);

Same example using subpartitions, assuming sales_east is a subpartition:

ALTER TABLE sales_list SPLIT SUBPARTITION sales_east INTO
   (SUBPARTITION NY values ('New York'),
    SUBPARTITION FL values ('Florida'),
    SUBPARTITION rest_sales_east);

To split a List partition into N partitions, (N-1) lists of literal values must be specified, each of which defines the first (N-1) partitions into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into the Nth new partition whose value list contains the remaining literal values from the original partition. No two value lists can contain the same partition value. The (N-1) value lists that are specified cannot contain all of the partition values of the current partition because the Nth new partition would be empty. Also, the new (N-1) value lists cannot contain any partition values that do not exist for the current partition.

Restrictions on Splitting a Partition into multiple Partitions

– You cannot specify this clause for a hash partition.
– You cannot specify the parallel_clause for index-organized tables.
– If the table is an index-organized table, or if a local domain index is defined on the table, then you can split the partition into only two new partitions.