ALTER TABLE...ADD SUBPARTITION - PolarDB - Alibaba Cloud Documentation Center

Overview

You can use the ALTER TABLE...ADD SUBPARTITION command to add a subpartition to an existing subpartitioned table. Syntax:

ALTER TABLE table_name MODIFY PARTITION partition_name 
      ADD SUBPARTITION subpartition_definition;

Where subpartition_definition is:

{list subpartition | range subpartition}

and list_subpartition is:

SUBPARTITION [subpartition_name]
  VALUES (value[, value]...)
  [TABLESPACE tablespace_name]

and range_subpartition is:

SUBPARTITION subpartition_name
  VALUES LESS THAN (value[, value]...) 
  [TABLESPACE tablespace_name]

Description

The ALTER TABLE...ADD SUBPARTITION command adds a subpartition to an existing subpartitioned partition. The number of defined subpartitions is not limited.

New subpartitions must be of the same type (LIST or RANGE) as existing subpartitions. The subpartitioning rules for new subpartitions must reference the same column specified in the subpartitioning rules that define the existing subpartitions.

You cannot use the ALTER TABLE...ADD SUBPARTITION statement to add subpartitions to tables that have a MAXVALUE or DEFAULT rule. Alternatively, you can use the ALTER TABLE... SPLIT SUBPARTITION statement to split an existing subpartition. This effectively allows you to add a subpartition to a table.

You cannot add a new subpartition that precedes existing subpartitions in a range-partitioned table. Range subpartitions must be specified in ascending order.

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...ADD 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 in which the subpartition resides.
partition_name	The name of the partition in which the new subpartition will reside.
subpartition_name	The name of the subpartition to be created. Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
(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` partition), or `maxvalue` (if specifying a `RANGE` partition). 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 subpartition resides.

Example - add a subpartition to a LIST-RANGE partitioned table

The following example adds a RANGE subpartition to the list-partitioned sales table. The sales table 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:

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 a subpartition named europe_2013:

ALTER TABLE sales MODIFY PARTITION europe 
  ADD SUBPARTITION europe_2013 
  VALUES LESS THAN('2015-Jan-01');

After this command is called, the table contains the europe_2013 subpartition:

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' 
 europe         | europe_2013       | '2015-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' 
(7 rows)

Note that when you add a new range subpartition, the subpartitioning rules must specify a range that is located after existing subpartitions.

Example - add a subpartition to a RANGE-LIST partitioned table

The following example adds a LIST subpartition to the range-partitioned sales table. The sales table 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 europe VALUES ('ITALY', 'FRANCE'),
      SUBPARTITION americas VALUES ('US', 'CANADA')
    ),

    PARTITION second_half_2012 VALUES LESS THAN('01-JAN-2013') 
    (
      SUBPARTITION asia VALUES ('INDIA', 'PAKISTAN')
    )
  );

The sales table has two partitions, named first_half_2012 and second_half_2012, respectively. The first_half_2012 partition has two subpartitions named europe and americas, respectively. The second_half_2012 partition has one subpartition named asia.

acctg=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
  partition_name  | subpartition_name |     high_value      
------------------+-------------------+---------------------
 first_half_2012  | europe            | 'ITALY', 'FRANCE'  
 first_half_2012  | americas          | 'US', 'CANADA' 
 second_half_2012 | asia              | 'INDIA', 'PAKISTAN' 
(3 rows)

The following command adds a subpartition named east_asia to the second_half_2012 partition:

ALTER TABLE sales MODIFY PARTITION second_half_2012 
  ADD SUBPARTITION east_asia VALUES ('CHINA');

After this command is called, the table contains the east_asia subpartition:

acctg=# SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS;
  partition_name  | subpartition_name |     high_value      
------------------+-------------------+---------------------
 first_half_2012  | europe            | 'ITALY', 'FRANCE'   
 first_half_2012  | americas          | 'US', 'CANADA'     
 second_half_2012 | asia              | 'INDIA', 'PAKISTAN' 
 second_half_2012 | east_asia         | 'CHINA'        
(4 rows)