Help Center/ GaussDB/ Centralized_8.x/ SQL Reference/ SQL Syntax/ A/ ALTER TABLE SUBPARTITION

Updated on 2024-06-03 GMT+08:00

View PDF

ALTER TABLE SUBPARTITION

Description

Modifies partitions of a level-2 partitioned table, including adding, deleting, clearing, splitting, merging, exchanging, and renaming partitions, moving partition tablespaces, enabling and disabling the automatic partitioning function, and modifying partition attributes.

Precautions

The tablespace of the added partition cannot be PG_GLOBAL.
The name of the added partition must be different from the names of the existing level-1 and level-2 partitions in the partitioned table.
The key value of the added partition must be consistent with the type of partition keys in the partitioned table.
If a range partition is added, the key value of the added partition must be greater than the upper limit of the last range partition in the partitioned table. To add a partition to a table with the MAXVALUE partition, you are advised to use the SPLIT syntax.
If a list partition is added, the key value of the added partition cannot be the same as that of an existing partition. To add a partition to a table with the DEFAULT partition, you are advised to use the SPLIT syntax.
Hash partitions cannot be added. However, if the level-2 partition mode of an level-2 partitioned table is hash but the level-1 partition mode is not hash, you can add a level-1 partition and create the corresponding level-2 partition.
If the number of partitions in the target partitioned table has reached the maximum (1048575), partitions cannot be added.
If the partitioned table contains only one level-1 or level-2 partition, the partition cannot be deleted.
Hash partitions cannot be deleted.
PARTITION FOR() and SUBPARTITION FOR() can be used to select partitions. The number of specified values in the brackets must be the same as the number of columns defined in partition creation.
Only level-2 partitions (leaf nodes) can be split. Only range and list partitioning policies can be used and hash partitioning policies are not supported.
Only level-2 partitions (leaf nodes) can be merged, and the source partitions must belong to the same level-1 partition.
Only the owner of a partitioned table or users granted with the ALTER permission on the partitioned table can run the ALTER TABLE PARTITION command. The system administrator has the permission to run the command by default.
Deleting, splitting, clearing, and exchanging partitions will invalidate the global index. The UPDATE GLOBAL INDEX clause can be used to update the index synchronously.
If the UPDATE GLOBAL INDEX clause is not used when you delete, split, clear, or exchange partitions, concurrent DML services may report errors due to invalidated indexes.
If enable_gpi_auto_update is set to on, the global index is automatically updated even if the UPDATE GLOBAL INDEX clause is not declared.
If you add a row-level expression when adding or changing an ILM policy for a data object, note that the row-level expression supports only the functions listed in the whitelist. For details about the whitelist function list, see Row Expression Function Whitelist.
To enable automatic level-1 or level-2 list partitioning, ensure that no DEFAULT partition key exists in the corresponding partitions.

Syntax

Modifying partitions in a level-2 partitioned table includes modifying the main syntax of a table partition, modifying the syntax of a table partition name, resetting the partition ID, and enabling or disabling the syntax of the automatic partitioning function.

Modify the syntax of the table partition.
```
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    action [, ... ];
```
action indicates the following clauses for maintaining partitions. For the partition continuity when multiple clauses are used for partition maintenance, GaussDB executes DROP PARTITION and ADD PARTITION first, and then the rest clauses in sequence.
```
    move_clause  |
    exchange_clause  |
    row_clause  |
    merge_clause  |
    modify_clause  |
    add_clause    |
    drop_clause   |    
    split_clause  |
    truncate_clause  |
    ilm_clause    |
    set_partitioning_clause
```
- The move_clause syntax is used to move the partition to a new tablespace.
```
MOVE SUBPARTITION { subpartion_name | FOR ( subpartition_value [, ...] ) } TABLESPACE tablespacename
```
- The exchange_clause syntax is used to move the data from a general table to a specified partition.
```
EXCHANGE SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) } 
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
    [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ] [ UPDATE GLOBAL INDEX ]
```
  The ordinary table and partition whose data is to be exchanged must meet the following requirements:
  - The number of columns of the ordinary table is the same as that of the partition, and their information should be consistent, including: column name, data type, constraint, collation information, storage parameter, and compression information.
  - The compression information of the ordinary table and partitioned table should be consistent.
  - The number of ordinary table indexes is the same as that of local indexes of the partition, and the index information is the same.
  - The number and information of constraints of the ordinary table and partition should be consistent.
  - The ordinary table cannot be a temporary table, and the partitioned table should be a level-2 partitioned table.
  - Ordinary tables and partitioned tables do not support dynamic data masking and row-level security constraints.
  - When the exchange is done, the data and tablespace of the ordinary table and partitioned table are exchanged. In this case, the statistics on the ordinary table and partition become unreliable, and they should be analyzed again.
  - A non-partition key cannot be used to create a local unique index. Therefore, if an ordinary table contains a unique index, data cannot be exchanged.
    To exchange data, you can create an intermediate table, insert partition data into the intermediate table, truncate partitions, insert ordinary table data into the partitioned table, drop the ordinary table, and rename the intermediate table.
  - If the DROP COLUMN operation is performed on an ordinary or partitioned table, the deleted column still exists physically. Therefore, you need to ensure that the deleted column of the ordinary table is strictly aligned with that of the partition. Otherwise, the exchange will fail.
- The row_clause syntax is used to set row movement of a partitioned table.
```
{ ENABLE | DISABLE } ROW MOVEMENT
```
- The merge_clause syntax is used to merge partitions into one. The maximum number of source partitions that can be merged in a command is 300.
```
MERGE SUBPARTITIONS { subpartition_name } [, ...] INTO SUBPARTITION partition_name 
    [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )]] [ TABLESPACE tablespacename ] [ UPDATE GLOBAL INDEX ]
```
  - For range partitioning, the ranges of the source partitions must increase continuously, and the partition name after MERGE can be the same as the name of the last source partition. For list partitioning, there is no such range requirement on the source partitions, and the partition name after MERGE can be the same as that of any source partition. If the partition name after MERGE is the same as that of a source partition, they are considered as the same partition.
  - If the GUC parameter enable_ilm is not enabled and the merge_clause syntax is used to merge multiple partitions with the ILM policy into one partition, the new partition does not inherit the ILM policy.
  Ustore tables do not support ALTER TABLE MERGE SUBPARTITIONS in transaction blocks and stored procedures.
- The modify_clause syntax is used to set whether a partitioned index is usable. The syntax can be used in level-1 partitions.
```
MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }
```
  It can also be used in level-2 partitions.
```
MODIFY SUBPARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }
```
- The add_clause syntax is used to add one or more partitions to a specified partitioned table. The syntax can be used in level-1 partitions.
```
ADD {partition_less_than_item | partition_list_item } [ ( subpartition_definition_list ) ]
```
  It can also be used in level-2 partitions.
```
MODIFY PARTITION partition_name ADD subpartition_definition
```
  partition_less_than_item defines a range partition. The syntax is as follows:
```
PARTITION partition_name VALUES LESS THAN ( partition_value | MAXVALUE )[ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ]
```
  partition_list_item defines a list partition. The syntax is as follows:
```
PARTITION partition_name VALUES ( partition_value [, ...] | DEFAULT ) [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ]
```
  subpartition_definition_list contains the subpartition_definition object of one or more level-2 partitions. The syntax is as follows:
```
SUBPARTITION subpartition_name [ VALUES LESS THAN ( partition_value | MAXVALUE ) | VALUES ( partition_value [, ...] | DEFAULT )] [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ]  [ TABLESPACE tablespace ]
```
  If the level-1 partition is a hash partition, you cannot use ADD to add a level-1 partition. If the level-2 partition is a hash partition, you cannot use MODIFY to add a level-2 partition.
- The drop_clause syntax is used to remove a partition from a specified partitioned table. The syntax can be used in level-1 partitions.
```
DROP PARTITION  { partition_name | FOR (  partition_value )  } [ UPDATE GLOBAL INDEX ]
```
  It can also be used in level-2 partitions.
```
DROP SUBPARTITION  { subpartition_name | FOR (  partition_value, subpartition_value )  } [ UPDATE GLOBAL INDEX ]
```
  - If the level-1 partition is a hash partition, the level-1 partition cannot be deleted. If the level-2 partition is a hash partition, the level-2 partition cannot be deleted.
  - At least one sub-partition must be retained.
- The split_clause syntax is used to split one partition into partitions.
```
SPLIT SUBPARTITION { subpartition_name| FOR ( subpartition_value [, ...] ) } { split_point_clause | no_split_point_clause } [ UPDATE GLOBAL INDEX ]
```
  - The partition name after SPLIT can be the same as the source partition name, but they are regarded as different partitions.
  - If the GUC parameter enable_ilm is not enabled and the split_clause syntax is used to split a partition with the ILM policy into multiple partitions, the new partitions do not inherit the ILM policy.
  - The syntax for specifying the split point for range partitioning is as follows:
```
AT ( subpartition_value ) INTO ( SUBPARTITION subpartition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] , SUBPARTITION subpartition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] )
```
    The split point must be in the range of partition keys of the partition to be split. Specifying a split point can only split one partition into two partitions.
  - The syntax of not specifying the split point for range partitioning is as follows (the range of the last partition is not defined, that is, the VALUES LESS THAN (subpartition_value) part is not defined; by default, the last partition inherits the upper boundary value of the range defined for the source partition):
```
INTO ( SUBPARTITION subpartition_name VALUES LESS THAN (subpartition_value) [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ][, ...] )
```
    The range defined for the first new partition must be greater than that of the previous partition (if any) of the partition being split.
    
    The range of the last new partition cannot be defined and it inherits the upper boundary value of the range defined for the source partition by default.
    
    The new partition must meet the constraint of continuously increasing range.
  - The syntax for specifying the split point for list-range partitioning is as follows:
```
VALUES ( subpartition_value ) INTO ( SUBPARTITION subpartition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] , SUBPARTITION subpartition_name [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ] )
```
    The split point must be a non-empty true subset of the source partition. Specifying a split point can only split one partition into two partitions.
  - The syntax for not specifying the split point for a list partitioned table is as follows:
```
INTO ( SUBPARTITION subpartition_name VALUES (subpartition_value_list) [ ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR )] ] [ TABLESPACE tablespacename ][, ...] )
```
    The range of the last partition is not defined, that is, the VALUES (partition_value_list) part is not defined; the partition range is equal to the remaining set of the source partition excluding other level-2 partitions.
    
    If no split point is specified, each new partition must be a non-empty true subset of the source partition and does not overlap with each other.
- The truncate_clause syntax is used to remove a specified partition from a partitioned table. The syntax can be used in level-1 partitions.
```
TRUNCATE PARTITION  { partition_name | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]
```
  It can also be used in level-2 partitions.
```
TRUNCATE SUBPARTITION  { subpartition_name | FOR (  subpartition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]
```
- The ilm_clause syntax adds an ILM policy to a partition. It supports the syntax for the OLTP table compression feature of data lifecycle management. The syntax can be used in level-1 partitions.
```
MODIFY PARTITION partition_name ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]
```
  It can also be used in level-2 partitions.
```
MODIFY SUBPARTITION subpartition_name ILM ADD POLICY ROW STORE { COMPRESS ADVANCED } { ROW } AFTER n { day | month | year } OF { NO MODIFICATION } [ ON ( EXPR ) ]
```
- The set_partitioning_clause syntax enables or disables automatic list partitioning.
```
SET { PARTITIONING | SUBPARTITIONING } { AUTOMATIC | MANUAL }
```

Modify the name of a partition. This syntax can be used to modify level-1 partitions of a partitioned table.

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

It can also be used to modify level-2 partitions of a partitioned table.

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )}
    RENAME SUBPARTITION { subpartion_name | FOR ( subpartition_value [, ...] ) } TO subpartition_new_name;

Reset a partition ID.

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name  )} RESET PARTITION;

Parameters

table_name
Specifies the name of a partitioned table.

Value range: an existing partitioned table name.
subpartition_name
Specifies the name of a level-2 partition name.

Value range: an existing level-2 partition name.
tablespacename
Specifies which tablespace the partition moves to.

Value range: an existing tablespace name.
partition_value
Specifies the key value of the level-1 partition.

The value specified by PARTITION FOR ( partition_value [, ...] ) can uniquely identify a level-1 partition.

Value range: partition keys for the level-1 partition to be operated.
subpartition_value
Specifies the level-1 and level-2 partition key values.

The value specified by SUBPARTITION FOR ( subpartition_value [, ...] ) can uniquely identify a level-2 partition.

Value range: partition key values of the level-1 and level-2 partitions for the level-2 partition to be operated.
UNUSABLE LOCAL INDEXES
Sets all the indexes unusable in the partition.
REBUILD UNUSABLE LOCAL INDEXES
Rebuilds all the indexes in the partition.
{ ENABLE | DISABLE } ROW MOVEMET
Specifies whether to enable row movement.

If the tuple value is updated on the partition key during the UPDATE operation, the partition where the tuple is located is altered. Setting this parameter enables error messages to be reported or movement of the tuple between partitions.

Value range:
- ENABLE: Row movement is enabled.
- DISABLE: Row movement is disabled.
By default, this function is enabled.
ordinary_table_name
Specifies the name of the ordinary table whose data is to be migrated.

Value range: an existing table name.
{ WITH | WITHOUT } VALIDATION
Checks whether the ordinary table data meets the specified partition key range of the partition to be migrated.

Value range:
- WITH: checks whether the ordinary table data meets the partition key range of the partition to be migrated. If any data does not meet the required range, an error is reported.
- WITHOUT: does not check whether the ordinary table data meets the partition key range of the partition to be migrated.
The default value is WITH.

The check is time consuming, especially when the data volume is large. Therefore, use WITHOUT when you are sure that the current ordinary table data meets the partition key range of the partition to be migrated.
VERBOSE
When VALIDATION is WITH, if the ordinary table contains data that is out of the partition key range, insert the data to the correct partition. If there is no correct partition where the data can be inserted to, an error is reported.

Only when VALIDATION is WITH, VERBOSE can be specified.
partition_new_name
Specifies the new name of a partition.

Value range: a string. It must comply with the naming convention.
subpartition_new_name
Specifies the new name of a level-2 partition.

Value range: a string. It must comply with the naming convention.
UPDATE GLOBAL INDEX
If this parameter is used, all global indexes in the partitioned table are updated to ensure that data can be queried correctly using global indexes. If this parameter is not used, all global indexes in the partitioned table will become invalid.
SET { PARTITIONING | SUBPARTITIONING } { AUTOMATIC | MANUAL }
Enables or disables the function of automatic level-1/level-2 list partitioning. If the keyword PARTITIONING is used, the level-1 partition is specified. If the keyword SUBPARTITIONING is used, the level-2 partition is specified. If the keyword AUTOMATIC is used, the automatic partitioning function is enabled. If the keyword MANUAL is used, the automatic partitioning function is disabled.

Examples

Rename a partition.

-- Create a level-2 partitioned table tbl_rge_lst_test.
gaussdb=# CREATE TABLE tbl_lst_reg_test(
    area_id char(5),
    sdate char(8),
    eid char(5),
    sales_amt int
) PARTITION BY LIST(area_id) SUBPARTITION BY RANGE(sdate)( 
    PARTITION p_1001 VALUES ('1001')( 
        SUBPARTITION p_1001_201901 VALUES LESS THAN ('20190201'), 
        SUBPARTITION p_1001_201902 VALUES LESS THAN ('20190301'), 
        SUBPARTITION p_1001_201903 VALUES LESS THAN ('20190401') 
    ),
    PARTITION p_1002 VALUES ('1002')( 
        SUBPARTITION p_1002_201901 VALUES LESS THAN ('20190201'), 
        SUBPARTITION p_1002_201902 VALUES LESS THAN ('20190301'), 
        SUBPARTITION p_100w VALUES LESS THAN ('20190401')        
    )  
);

-- Change the name of the subpartition p_100w to p_1002_201903.
gaussdb=# ALTER TABLE tbl_lst_reg_test RENAME SUBPARTITION p_100w TO p_1002_201903;

-- Query.
gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test' AND partition_name = 'p_1002';
    table_name    | partition_name | subpartition_name 
------------------+----------------+-------------------
 tbl_lst_reg_test | p_1002         | p_1002_201901
 tbl_lst_reg_test | p_1002         | p_1002_201902
 tbl_lst_reg_test | p_1002         | p_1002_201903
(3 rows)

Move partition tablespaces.

-- Create the tbs_data1 tablespace.
gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace/tbs_data1';

-- Move the level-2 partition p_1002_201901 to the tablespace tbs_data1.
gaussdb=# ALTER TABLE tbl_lst_reg_test MOVE SUBPARTITION p_1002_201901 TABLESPACE tbs_data1;

-- Move the level-2 partition p_1002_201902 to the tablespace tbs_data1.
gaussdb=# ALTER TABLE tbl_lst_reg_test MOVE SUBPARTITION FOR ('1002','20190325') TABLESPACE tbs_data1;

-- Query the tablespace of the modified level-2 partition.
gaussdb=# SELECT subpartition_name,tablespace_name FROM db_tab_subpartitions WHERE subpartition_name IN ('p_1002_201901','p_1002_201903');
 subpartition_name | tablespace_name 
-------------------+-----------------
 p_1002_201901     | tbs_data1
 p_1002_201903     | tbs_data1
(2 rows)

Exchange partitions.

-- Create an ordinary table and insert data into the table.
gaussdb=# CREATE TABLE tbl_test(
    area_id char(5),
    sdate char(8),
    eid char(5),
    sales_amt int
);

gaussdb=# INSERT INTO tbl_test VALUES ('1002','20190326','00001',9000);
gaussdb=# INSERT INTO tbl_test VALUES ('1002','20190326','00002',7500);
gaussdb=# INSERT INTO tbl_test VALUES ('1002','20190326','00003',6000);

-- Insert data into the partitioned table.
gaussdb=# INSERT INTO tbl_lst_reg_test VALUES ('1002','20190301','00001',126);

-- Exchange partitions.
gaussdb=# ALTER TABLE tbl_lst_reg_test EXCHANGE SUBPARTITION (p_1002_201903) WITH TABLE tbl_test;

-- Query data. Data in the table and partition is exchanged.
gaussdb=# SELECT * FROM tbl_lst_reg_test;
 area_id |  sdate   |  eid  | sales_amt 
---------+----------+-------+-----------
 1002    | 20190326 | 00001 |      9000
 1002    | 20190326 | 00002 |      7500
 1002    | 20190326 | 00003 |      6000
(3 rows)

gaussdb=# SELECT * FROM tbl_test;
 area_id |  sdate   |  eid  | sales_amt 
---------+----------+-------+-----------
 1002    | 20190301 | 00001 |       126
(1 row)

-- Check that the partitioned table tablespace is exchanged.
gaussdb=# SELECT subpartition_name,tablespace_name FROM db_tab_subpartitions WHERE subpartition_name = 'p_1002_201903';
 subpartition_name |  tablespace_name   
-------------------+--------------------
 p_1002_201903     | DEFAULT TABLESPACE
(1 row)

Merge partitions.

-- Merge partitions.
gaussdb=# ALTER TABLE tbl_lst_reg_test MERGE SUBPARTITIONS p_1002_201901,p_1002_201902,p_1002_201903 INTO SUBPARTITION p_1002_20191;

-- Query level-2 partition information.
gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test';
    table_name    | partition_name | subpartition_name 
------------------+----------------+-------------------
 tbl_lst_reg_test | p_1001         | p_1001_201901
 tbl_lst_reg_test | p_1001         | p_1001_201902
 tbl_lst_reg_test | p_1001         | p_1001_201903
 tbl_lst_reg_test | p_1002         | p_1002_20191
(4 rows)

Add a partition.

-- Add a level-1 partition.
gaussdb=# ALTER TABLE tbl_lst_reg_test ADD PARTITION p_1003 VALUES('1003') (SUBPARTITION p_1003_201901 VALUES LESS THAN ('20190201')); 

-- Add a level-2 partition to a specific level-1 partition.
gaussdb=# ALTER TABLE tbl_lst_reg_test MODIFY PARTITION p_1003 ADD SUBPARTITION p_1003_201902 VALUES LESS THAN ('20190301');

-- Query partition information.
gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test';
    table_name    | partition_name | subpartition_name 
------------------+----------------+-------------------
 tbl_lst_reg_test | p_1001         | p_1001_201901
 tbl_lst_reg_test | p_1001         | p_1001_201902
 tbl_lst_reg_test | p_1001         | p_1001_201903
 tbl_lst_reg_test | p_1002         | p_1002_20191
 tbl_lst_reg_test | p_1003         | p_1003_201901
 tbl_lst_reg_test | p_1003         | p_1003_201902
(6 rows)

Delete a partition.

-- Delete the level-2 partition p_1003_201902.
gaussdb=# ALTER TABLE tbl_lst_reg_test DROP SUBPARTITION p_1003_201902;

-- Query.
gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test' AND partition_name = 'p_1003';
    table_name    | partition_name | subpartition_name 
------------------+----------------+-------------------
 tbl_lst_reg_test | p_1003         | p_1003_201901
(1 row)

-- Delete the level-1 partition p_1003.
gaussdb=# ALTER TABLE tbl_lst_reg_test DROP PARTITION p_1003;

-- Query.
gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test';
    table_name    | partition_name | subpartition_name 
------------------+----------------+-------------------
 tbl_lst_reg_test | p_1001         | p_1001_201901
 tbl_lst_reg_test | p_1001         | p_1001_201902
 tbl_lst_reg_test | p_1001         | p_1001_201903
 tbl_lst_reg_test | p_1002         | p_1002_20191
(4 rows)

Split partitions.

-- Specify the split point to split partitions.
gaussdb=# ALTER TABLE tbl_lst_reg_test  SPLIT SUBPARTITION p_1002_20191 
    AT ('20190201') INTO (SUBPARTITION p_1002_201901,SUBPARTITION p_1002_20191) UPDATE GLOBAL INDEX;

-- Do not specify the split point to split partitions.
gaussdb=# ALTER TABLE tbl_lst_reg_test SPLIT SUBPARTITION p_1002_20191 INTO (
    SUBPARTITION p_1002_201902 VALUES LESS THAN ('20190301'),
    SUBPARTITION p_1002_201903
) UPDATE GLOBAL INDEX;

-- Query partition information.
gaussdb=# SELECT table_name,partition_name,subpartition_name,high_value FROM  db_tab_subpartitions;
    table_name    | partition_name | subpartition_name | high_value 
------------------+----------------+-------------------+------------
 tbl_lst_reg_test | p_1001         | p_1001_201901     | 20190201
 tbl_lst_reg_test | p_1001         | p_1001_201902     | 20190301
 tbl_lst_reg_test | p_1001         | p_1001_201903     | 20190401
 tbl_lst_reg_test | p_1002         | p_1002_201901     | 20190201
 tbl_lst_reg_test | p_1002         | p_1002_201902     | 20190301
 tbl_lst_reg_test | p_1002         | p_1002_201903     | 20190401
(6 rows)

Clear partition data.

-- Clear the level-1 partition.
gaussdb=# ALTER TABLE tbl_lst_reg_test TRUNCATE PARTITION p_1001 UPDATE GLOBAL INDEX;

-- Clear the level-2 partition.
gaussdb=# ALTER TABLE tbl_lst_reg_test TRUNCATE SUBPARTITION p_1002_201903 UPDATE GLOBAL INDEX;

-- Drop the table.
gaussdb=# DROP TABLE tbl_lst_reg_test;
gaussdb=# DROP TABLE tbl_test;

-- Drop the tablespace.
gaussdb=# DROP TABLESPACE tbs_data1;

Helpful Links

CREATE TABLE SUBPARTITION

Parent topic: A

Previous topic: ALTER TABLE PARTITION

Next topic: ALTER TABLESPACE

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot