The ALTER TABLE...SPLIT SUBPARTITION command adds a subpartition to an existing subpartitioned table.

Overview

You can use the ALTER TABLE...SPLIT SUBPARTITION command to divide a subpartition into two subpartitions and redistribute the content of the subpartition. The ALTER TABLE...SPLIT SUBPARTITION command has two forms.

The first form splits a range subpartition into two subpartitions: 
ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
  AT (range_part_value)
  INTO 
  (
    SUBPARTITION new_subpart1 
      [TABLESPACE tablespace_name],
    SUBPARTITION new_subpart2 
      [TABLESPACE tablespace_name]
  );
The second form splits a list subpartition into two subpartitions: 
ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
  VALUES (value[, value]...) 
  INTO 
  (
    SUBPARTITION new_subpart1 
      [TABLESPACE tablespace_name],
    SUBPARTITION new_subpart2 
      [TABLESPACE tablespace_name]
  );

Description

The ALTER TABLE...SPLIT SUBPARTITION command adds a subpartition to an existing subpartitioned table. The number of defined subpartitions is not limited. When you run an ALTER TABLE...SPLIT SUBPARTITION command, PolarDB for Oracle creates two new subpartitions. It moves rows that contain values that are constrained by the specified subpartition rules into new_subpart1, and the remaining rows into new_subpart2.

The new subpartition rules must reference the column specified in the rules that define the existing subpartitions.

Include the TABLESPACE clause to specify a tablespace in which a new subpartition will reside. If you do not specify a tablespace, the subpartition will be created in the default tablespace.

If the table is indexed, the index will be created on the new subpartition.

To use the ALTER TABLE...SPLIT SUBPARTITION command, you must be the table owner, or have superuser (or administrative) privileges.

Parameters

Parameter Description
table_name The name (optionally schema-qualified) of the partitioned table.
subpartition_name The name of the subpartition to be split.
new_subpart1

The name of the first new subpartition to be created. Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.

new_subpart1 will receive the rows that meet the subpartitioning constraints specified in the ALTER TABLE...SPLIT SUBPARTITION command.

new_subpart2

The name of the second new subpartition to be created. Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.

new_subpart2 will receive the rows that are not directed to new_subpart1 by the subpartitioning constraints specified in the ALTER TABLE... SPLIT SUBPARTITION command.

(value[, value]...)

Use value to specify a quoted literal value (or a list of literal values separated by commas) by which table entries will be grouped into partitions. Each partitioning rule must specify at least one value, but the number of values specified in a rule is not limited. value may be null, default (if specifying a LIST subpartition), or maxvalue (if specifying a RANGE subpartition).

For more information about creating a DEFAULT or MAXVALUE partition, see Handle stray values in a list- or range-partitioned table.

tablespace_name The name of the tablespace in which the partition or subpartition resides.

Example - split a LIST subpartition

The following example splits a list subpartition and redistributes the content of the subpartition between two new subpartitions. The sample table (sales) is created by using the following command: 

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST (country)
  (
    PARTITION first_half_2012 VALUES LESS THAN('01-JUL-2012')
    (
      SUBPARTITION p1_europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION p1_americas VALUES ('US', 'CANADA')
    ),
    PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013') 
    (
      SUBPARTITION p2_europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION p2_americas VALUES ('US', 'CANADA')
    )
  );
The sales table has two partitions, named first_half_2012 and second_half_2012. Each partition has two range-defined subpartitions that distribute the content of the partition into subpartitions based on the value of the country column. 
acctg=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
  partition_name  | subpartition_name |    high_value     
------------------+-------------------+-------------------
 second_half_2012 | p2_europe         | 'ITALY', 'FRANCE' 
 first_half_2012  | p1_europe         | 'ITALY', 'FRANCE' 
 second_half_2012 | p2_americas       | 'US', 'CANADA'   
 first_half_2012  | p1_americas       | 'US', 'CANADA'  
(4 rows)
The following command adds rows to each subpartition: 
INSERT INTO sales VALUES
  (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
  (40, '9519b', 'US', '12-Apr-2012', '145000'),
  (40, '4577b', 'US', '11-Nov-2012', '25000'),
  (30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
  (30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
  (30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
  (40, '3788a', 'US', '12-May-2012', '4950'),
  (10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
  (10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
  (10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
  (40, '4788a', 'US', '23-Sept-2012', '4950'),
  (40, '4788b', 'US', '09-Oct-2012', '15000');
A SELECT statement is used to confirm that rows are distributed among the subpartitions as expected: 
acctg=# SELECT tableoid::regclass, * FROM sales;
     tableoid      | dept_no | part_no | country|        date        |amount 
-------------------+---------+---------+--------+--------------------+------
 sales_p1_europe   |      10 | 4519b   | FRANCE | 17-JAN-12 00:00:00 |  45000
 sales_p1_americas |      40 | 9519b   | US     | 12-APR-12 00:00:00 | 145000
 sales_p1_americas |      30 | 9519b   | CANADA | 01-FEB-12 00:00:00 |  75000
 sales_p1_americas |      30 | 4519b   | CANADA | 08-APR-12 00:00:00 | 120000
 sales_p1_americas |      40 | 3788a   | US     | 12-MAY-12 00:00:00 |   4950
 sales_p2_europe   |      10 | 9519b   | ITALY  | 07-JUL-12 00:00:00 |  15000
 sales_p2_europe   |      10 | 9519a   | FRANCE | 18-AUG-12 00:00:00 | 650000
 sales_p2_europe   |      10 | 9519b   | FRANCE | 18-AUG-12 00:00:00 | 650000
 sales_p2_americas |      40 | 4577b   | US     | 11-NOV-12 00:00:00 |  25000
 sales_p2_americas |      30 | 7588b   | CANADA | 14-DEC-12 00:00:00 |  50000
 sales_p2_americas |      40 | 4788a   | US     | 23-SEP-12 00:00:00 |   4950
 sales_p2_americas |      40 | 4788b   | US     | 09-OCT-12 00:00:00 |  15000
(12 rows)
The following command splits the p2_americas subpartition into two new subpartitions and redistributes the content: 
ALTER TABLE sales SPLIT SUBPARTITION p2_americas
  VALUES ('US') 
  INTO 
  (
    SUBPARTITION p2_us,
    SUBPARTITION p2_canada
  );
After this command is called, the p2_americas subpartition is deleted. In the place of the subpartition, the server creates two new subpartitions (p2_us and p2_canada): 
acctg=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
  partition_name  | subpartition_name |    high_value     
------------------+-------------------+-------------------
 first_half_2012  | p1_europe         | 'ITALY', 'FRANCE' 
 first_half_2012  | p1_americas       | 'US', 'CANADA'    
 second_half_2012 | p2_europe         | 'ITALY', 'FRANCE' 
 second_half_2012 | p2_canada         | 'CANADA'          
 second_half_2012 | p2_us             | 'US'              
(5 rows)
Querying the sales table shows that the content of the p2_americas subpartition has been redistributed: 
acctg=# SELECT tableoid::regclass, * FROM sales;
     tableoid      | dept_no | part_no | country |        date        |amount
-------------------+---------+---------+---------+--------------------+------
 sales_p1_europe   |      10 | 4519b   | FRANCE  | 17-JAN-12 00:00:00 | 45000
 sales_p1_americas |      40 | 9519b   | US      | 12-APR-12 00:00:00 |145000
 sales_p1_americas |      30 | 9519b   | CANADA  | 01-FEB-12 00:00:00 | 75000
 sales_p1_americas |      30 | 4519b   | CANADA  | 08-APR-12 00:00:00 |120000
 sales_p1_americas |      40 | 3788a   | US      | 12-MAY-12 00:00:00 |  4950
 sales_p2_europe   |      10 | 9519b   | ITALY   | 07-JUL-12 00:00:00 | 15000
 sales_p2_europe   |      10 | 9519a   | FRANCE  | 18-AUG-12 00:00:00 |650000
 sales_p2_europe   |      10 | 9519b   | FRANCE  | 18-AUG-12 00:00:00 |650000
 sales_p2_us       |      40 | 4577b   | US      | 11-NOV-12 00:00:00 | 25000
 sales_p2_us       |      40 | 4788a   | US      | 23-SEP-12 00:00:00 |  4950
 sales_p2_us       |      40 | 4788b   | US      | 09-OCT-12 00:00:00 | 15000
 sales_p2_canada   |      30 | 7588b   | CANADA  | 14-DEC-12 00:00:00 | 50000
(12 rows)

Example - split a RANGE subpartition

The following example splits a range subpartition and redistributes the content of the subpartition between two new subpartitions. The sample table (sales) is created by using the following command: 
CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
  SUBPARTITION BY RANGE(date)
(
  PARTITION europe VALUES('FRANCE', 'ITALY')
    (
      SUBPARTITION europe_2011 
        VALUES LESS THAN('2012-Jan-01'),
      SUBPARTITION europe_2012 
        VALUES LESS THAN('2013-Jan-01')
    ),
  PARTITION asia VALUES('INDIA', 'PAKISTAN')
    (
      SUBPARTITION asia_2011 
        VALUES LESS THAN('2012-Jan-01'),
      SUBPARTITION asia_2012 
        VALUES LESS THAN('2013-Jan-01')
    ),
  PARTITION americas VALUES('US', 'CANADA')
    (
      SUBPARTITION americas_2011 
        VALUES LESS THAN('2012-Jan-01'),
      SUBPARTITION americas_2012 
        VALUES LESS THAN('2013-Jan-01')
    )
);
The sales table has three partitions (europe, asia, and americas). Each partition has two range-defined subpartitions that distribute the content of the partition into subpartitions based on the value of the date column. 
acctg=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
 partition_name | subpartition_name |  high_value   
----------------+-------------------+---------------
 europe         | europe_2011       | '2012-Jan-01' 
 europe         | europe_2012       | '2013-Jan-01' 
 asia           | asia_2011         | '2012-Jan-01' 
 asia           | asia_2012         | '2013-Jan-01' 
 americas       | americas_2011     | '2012-Jan-01' 
 americas       | americas_2012     | '2013-Jan-01' 
(6 rows)
The following command adds rows to each subpartition: 
INSERT INTO sales VALUES
  (10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
  (20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
  (40, '9519b', 'US', '12-Apr-2012', '145000'),
  (20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
  (40, '4577b', 'US', '11-Nov-2012', '25000'),
  (30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
  (30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
  (30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
  (40, '3788a', 'US', '12-May-2012', '4950'),
  (10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
  (10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
  (10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
  (20, '3788b', 'INDIA', '21-Sept-2012', '5090'),
  (40, '4788a', 'US', '23-Sept-2012', '4950'),
  (40, '4788b', 'US', '09-Oct-2012', '15000'),
  (20, '4519a', 'INDIA', '18-Oct-2012', '650000'),
  (20, '4519b', 'INDIA', '2-Dec-2012', '5090');
A SELECT statement is used to confirm that rows are distributed among the subpartitions as expected: 
acctg=# SELECT tableoid::regclass, * FROM sales;
      tableoid       | dept_no|part_no| country |        date        |amount 
---------------------+--------+-------+---------+--------------------+---
 sales_europe_2012   |      10| 4519b | FRANCE  | 17-JAN-12 00:00:00 | 45000
 sales_europe_2012   |      10| 9519b | ITALY   | 07-JUL-12 00:00:00 | 15000
 sales_europe_2012   |      10| 9519a | FRANCE  | 18-AUG-12 00:00:00 | 650000
 sales_europe_2012   |      10| 9519b | FRANCE  | 18-AUG-12 00:00:00 | 650000
 sales_asia_2012     |      20| 3788a | INDIA   | 01-MAR-12 00:00:00 | 75000
 sales_asia_2012     |      20| 3788a | PAKISTAN| 04-JUN-12 00:00:00 | 37500
 sales_asia_2012     |      20| 3788b | INDIA   | 21-SEP-12 00:00:00 | 5090
 sales_asia_2012     |      20| 4519a | INDIA   | 18-OCT-12 00:00:00 | 650000
 sales_asia_2012     |      20| 4519b | INDIA   | 02-DEC-12 00:00:00 | 5090
 sales_americas_2012 |      40| 9519b | US      | 12-APR-12 00:00:00 | 145000
 sales_americas_2012 |      40| 4577b | US      | 11-NOV-12 00:00:00 | 25000
 sales_americas_2012 |      30| 7588b | CANADA  | 14-DEC-12 00:00:00 | 50000
 sales_americas_2012 |      30| 9519b | CANADA  | 01-FEB-12 00:00:00 | 75000
 sales_americas_2012 |      30| 4519b | CANADA  | 08-APR-12 00:00:00 | 120000
 sales_americas_2012 |      40| 3788a | US      | 12-MAY-12 00:00:00 | 4950
 sales_americas_2012 |      40| 4788a | US      | 23-SEP-12 00:00:00 | 4950
 sales_americas_2012 |      40| 4788b | US      | 09-OCT-12 00:00:00 | 15000
(17 rows)
The following command splits the americas_2012 subpartition into two new subpartitions and redistributes the content: 
ALTER TABLE sales 
  SPLIT SUBPARTITION americas_2012 
  AT('2012-Jun-01')
  INTO
  (
    SUBPARTITION americas_p1_2012, 
    SUBPARTITION americas_p2_2012
  );
After this command is called, the americas_2012 subpartition is deleted. In the place of the subpartition, the server creates two new subpartitions (americas_p1_2012 and americas_p2_2012): 
acctg=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
 partition_name | subpartition_name |  high_value   
----------------+-------------------+---------------
 europe         | europe_2012       | '2013-Jan-01' 
 europe         | europe_2011       | '2012-Jan-01' 
 americas       | americas_2011     | '2012-Jan-01' 
 americas       | americas_p2_2012  | '2013-Jan-01' 
 americas       | americas_p1_2012  | '2012-Jun-01' 
 asia           | asia_2012         | '2013-Jan-01' 
 asia           | asia_2011         | '2012-Jan-01' 
(7 rows)
Querying the sales table shows that the content of the americas_2012 subpartition has been redistributed: 
acctg=# SELECT tableoid::regclass, * FROM sales;
      tableoid         | dept_no|part_no|country |      date         |amount 
-----------------------+--------+-------+--------+-------------------+------- sales_europe_2012      |      10| 4519b |FRANCE  | 17-JAN-12 00:00:00|  45000
 sales_europe_2012     |      10| 9519b |ITALY   | 07-JUL-12 00:00:00|  15000
 sales_europe_2012     |      10| 9519a |FRANCE  | 18-AUG-12 00:00:00| 650000
 sales_europe_2012     |      10| 9519b |FRANCE  | 18-AUG-12 00:00:00| 650000
 sales_asia_2012       |      20| 3788a |INDIA   | 01-MAR-12 00:00:00|  75000
 sales_asia_2012       |      20| 3788a |PAKISTAN| 04-JUN-12 00:00:00|  37500
 sales_asia_2012       |      20| 3788b |INDIA   | 21-SEP-12 00:00:00|   5090
 sales_asia_2012       |      20| 4519a |INDIA   | 18-OCT-12 00:00:00| 650000
 sales_asia_2012       |      20| 4519b |INDIA   | 02-DEC-12 00:00:00|   5090
 sales_americas_p1_2012|      40| 9519b |US      | 12-APR-12 00:00:00| 145000
 sales_americas_p1_2012|      30| 9519b |CANADA  | 01-FEB-12 00:00:00|  75000
 sales_americas_p1_2012|      30| 4519b |CANADA  | 08-APR-12 00:00:00| 120000
 sales_americas_p1_2012|      40| 3788a |US      | 12-MAY-12 00:00:00|   4950
 sales_americas_p2_2012|      40| 4577b |US      | 11-NOV-12 00:00:00|  25000
 sales_americas_p2_2012|      30| 7588b |CANADA  | 14-DEC-12 00:00:00|  50000
 sales_americas_p2_2012|      40| 4788a |US      | 23-SEP-12 00:00:00|   4950
 sales_americas_p2_2012|      40| 4788b |US      | 09-OCT-12 00:00:00|  15000
(17 rows)