Use ALTER TABLE...SPLIT SUBPARTITION to split a subpartition - PolarDB

Splits an existing subpartition into two new subpartitions and redistributes its rows between them.

Syntax

Split a range subpartition (uses the AT clause):

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]
  );

Split a list subpartition (uses the VALUES clause):

ALTER TABLE table_name SPLIT SUBPARTITION subpartition_name
  VALUES (value[, value]...)
  INTO
  (
    SUBPARTITION new_subpart1
      [TABLESPACE tablespace_name],
    SUBPARTITION new_subpart2
      [TABLESPACE tablespace_name]
  );

How it works

When you run ALTER TABLE...SPLIT SUBPARTITION, PolarDB for Oracle deletes the original subpartition and creates exactly two new subpartitions in its place:

Rows that satisfy the subpartitioning constraint specified in the command go into new_subpart1.
All remaining rows go into new_subpart2.

The subpartitioning constraint must reference the same column used to define the existing subpartitions.

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

To run this command, you must be the table owner or have superuser (or administrative) privileges.

Parameters

Parameter	Description
`table_name`	The name of the partitioned table. Optionally schema-qualified.
`subpartition_name`	The name of the subpartition to split.
`new_subpart1`	The name of the first new subpartition. Receives rows that satisfy the subpartitioning constraint. Must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
`new_subpart2`	The name of the second new subpartition. Receives all rows not directed to `new_subpart1`. Must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
`value[, value]...`	One or more quoted literal values that define the subpartitioning constraint. Each rule requires at least one value. Accepted special values: `null`, `default` (LIST subpartitions only), and `maxvalue` (RANGE subpartitions only). For more information, see Handle stray values in a list- or range-partitioned table.
`tablespace_name`	The tablespace in which the new subpartition resides. If omitted, the subpartition is created in the default tablespace.

Limitations

ALTER TABLE...SPLIT SUBPARTITION always splits one subpartition into exactly two new subpartitions. Splitting into more than two subpartitions in a single command is not supported.
The subpartitioning constraint in the INTO clause must reference the same column as the existing subpartition definition.

Example: split a LIST subpartition

The following example splits the p2_americas list subpartition into two new subpartitions (p2_us and p2_canada) and redistributes the rows.

Create the sales table partitioned by RANGE(date) and subpartitioned by LIST(country):

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')
    )
  );

Query ALL_TAB_SUBPARTITIONS to confirm the initial subpartition layout:

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)

Insert rows into the table:

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');

Verify row distribution across subpartitions:

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)

Split p2_americas into p2_us (rows where country = 'US') and p2_canada (all other rows):

ALTER TABLE sales SPLIT SUBPARTITION p2_americas
  VALUES ('US')
  INTO
  (
    SUBPARTITION p2_us,
    SUBPARTITION p2_canada
  );

p2_americas is deleted and replaced by the two new subpartitions. Query ALL_TAB_SUBPARTITIONS to confirm:

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)

Query the sales table to verify that rows are redistributed correctly:

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 the americas_2012 range subpartition into two new subpartitions (americas_p1_2012 and americas_p2_2012) at the '2012-Jun-01' boundary.

Create the sales table partitioned by LIST(country) and subpartitioned by RANGE(date):

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')
    )
);

Query ALL_TAB_SUBPARTITIONS to confirm the initial subpartition layout:

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)

Insert rows into the table:

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');

Verify row distribution across subpartitions:

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)

Split americas_2012 at '2012-Jun-01', sending rows before that date to americas_p1_2012 and the rest to americas_p2_2012:

ALTER TABLE sales
  SPLIT SUBPARTITION americas_2012
  AT('2012-Jun-01')
  INTO
  (
    SUBPARTITION americas_p1_2012,
    SUBPARTITION americas_p2_2012
  );

americas_2012 is deleted and replaced by the two new subpartitions. Query ALL_TAB_SUBPARTITIONS to confirm:

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)

Query the sales table to verify that rows are redistributed correctly:

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)