ALTER TABLE PARTITION

Function

ALTER TABLE PARTITION is used to modify table partitions, including adding, deleting, splitting, merging partitions, migrating data from ordinary tables to partitioned tables, and modifying partition attributes.

A partitioned table is a logical table that is divided into segments, called partitions whose data is stored on different physical blocks.

Common partitioning strategies define data ranges based on one or more columns. Each partition stores data within a range. These columns are called partition keys.

Currently, DWS row-store and column-store tables support range partitioning and list partitioning.

• Range partitioning

  A table is partitioned into ranges defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. Each range has a dedicated partition for data storage.

  • Core feature: Data is distributed by consecutive intervals. It is used in scenarios where data is ordered and retrieved by range.
  • Scenario: Partition keys increase linearly or have continuous intervals. Data query is usually performed around a certain range, and new data naturally falls into a new range. For example, data about orders is partitioned by time range, and data about user levels is partitioned by value range.
  • Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. This is the most commonly used partitioning policy. Currently, range partitioning only allows the use of the range partitioning policy.
• List partitioning

  List partitioning allocates records to partitions based on the key values in each partition. The key values do not overlap in different partitions. Each set of key values gets its own partition for storing related data. List partitioning is supported only by clusters of version 8.1.3 or later.

  • Core feature: Data is divided based on discrete enumerated values, which are used as fixed classification category or high-frequency filter criteria.
  • Scenario: Partition key values are fixed and discrete. Data query is usually based on a certain category, and key values do not increase irregularly. For example, the user information table is partitioned by province, and each province corresponds to a partition.
  • In list partitioning, data is mapped to a created partition based on the partition key value. If the data can be mapped to, it is inserted into the specific partition. If it cannot be mapped to, error messages are returned.

Precautions

• The name of the partition to be added cannot be the same as that of an existing partition in the partition table.
• For a range partitioned table, the boundary value of the added partition must be the same type as the partition key of the partitioned table. The key value of the added partition must exceed the upper limit of the last partition.
• For a list partitioned table, if the DEFAULT partition has been defined, no new partition can be added.
• Unless otherwise specified, the syntax of range partitioned tables is the same as that of column-store partitioned tables.
• If the number of partitions in the target partitioned table has reached the maximum (32767), partitions cannot be added.

• If a partitioned table has only one partition, the partition cannot be deleted.
• When you run the DROP PARTITION command to delete a partition, the data in the partition is also deleted.
• Use PARTITION FOR() to choose partitions. The number of specified values in the brackets should be the same as the column number in customized partition, and they must be consistent.
• The Value partitioned table does not support the Alter Partition operation.
• For OBS hot and cold tables:
  • They do not support specifying the partition table's tablespace as the OBS tablespace for MOVE, EXCHANGE, MERGE, and SPLIT operations.
  • When an ALTER statement is executed, the data in the cold partition should stay in the cold partition, and the data in the hot partition should remain in the hot partition. It is not allowed to move cold partition data to the local tablespace.
  • Only the default tablespace is supported for cold partitions.
  • Cold and hot partitions cannot be merged.
  • Cold partition switching is not supported during the EXCHANGE operation.

• Avoid performing ALTER TABLE, ALTER TABLE PARTITION, DROP PARTITION, and TRUNCATE operations during peak hours to prevent long SQL statements from blocking these operations or SQL services.
• For more information about development and design specifications, see Development and Design Proposal.

Syntax

1
2

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, DWS does DROP PARTITION and then ADD PARTITION, and finally runs the rest clauses in sequence.

• modify_clause: Set Whether a Partition Index Is Usable
• rebuild_clause: Rebuild the Index of a Partition
• exchange_clause: Move the Data From an Ordinary Table to a Specified Partition
• row_clause: Enable or Disable Row Movement of a Partitioned Table
• merge_clause: Merge Partitions Into One
• split_clause: Split One Partition into Partitions
• add_clause sub-syntax: Add One or More Partitions to a Specified Partitioned Table
• drop_clause: Delete a Specified Partition or Multiple Partitions From a Partitioned Table
• truncate_partitioned_clause: Clear Data in a Table Partition

ALTER TABLE PARTITION Main Clause Parameters

modify_clause: Set Whether a Partition Index Is Usable

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

rebuild_clause: Rebuild the Index of a Partition

This syntax is supported only by clusters of version 8.3.0.100 or later.

exchange_clause: Move the Data From an Ordinary Table to a Specified Partition

1
2
3
4

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 
    WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} 
    [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]

The ordinary table and the partitioned table 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 partitioned table, and their information should be consistent, including the column name, data type, constraint, collation, storage parameter, compression, and data type of a deleted column.
• The compression information of the ordinary table and partitioned table should be consistent.
• The distribution column information of the ordinary table and the partitioned table should be consistent.
• The number and information of indexes of the ordinary table and the partitioned table should be consistent.
• The number and information of constraints of the ordinary table and the partitioned table should be consistent.
• The ordinary table cannot be a temporary table or unlogged table.
• Exchanging partitions between an ordinary table and a partitioned table requires them to be in the same logical cluster or node group to avoid slow data insertion. Otherwise, the exchange operation becomes a slow data copy process, especially with large tables.
• In online scale-out and redistribution scenarios, the exchange partition statement may interfere with the redistribution of ordinary tables and partitioned tables (if there are lock conflicts between the partition exchange and redistribution statement). Usually, the redistribution of ordinary tables and partitioned tables is retried twice after being interrupted, but if a table is exchanged too often, the redistribution may fail for multiple times. If the redistribution process of an ordinary table is interrupted by the partition exchange operation, the data has been replaced with the data in the original partitioned table during the redistribution retry. In this case, full redistribution will be performed again.
• If other columns following the last valid column in the partitioned table are deleted and the deleted columns are not considered, the partitioned table can be exchanged with the ordinary table as long as the columns of the two tables are the same.
• The table-level parameter colversion must be consistent between the column-store ordinary table and the column-store partitioned table. The colversion 2.0 and colversion 1.0 cannot be exchanged.

When the exchange is done, the data and tablespace of the ordinary table and partitioned table are exchanged. In this case, statistics about the ordinary table and the partitioned table become unreliable. Both tables should be analyzed again.

row_clause: Enable or Disable Row Movement of a Partitioned Table

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
{ ENABLE | DISABLE } ROW MOVEMENT

merge_clause: Merge Partitions Into One

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name  

• The partition before the keyword INTO is called the source partition, and the partition after the INTO is called the target partition.
• The number of source partitions cannot be less than 2.
• The source partition name must be unique.
• The source partition cannot have unusable indexes. Otherwise, an error will be reported.
• The target partition name must either be the same as the name of the last source partition or different from all partition names of the table.
• The boundaries of the target partition are the union of the boundaries of all the source partitions.
• For a range partitioned table, all source partitions must have contiguous boundaries.
• For list partitioning, if the source partition contains a DEFAULT partition, the boundary of the target partition is also DEFAULT.

split_clause: Split One Partition into Partitions

split_clause specifies range partitioning.

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }

add_clause sub-syntax: Add One or More Partitions to a Specified Partitioned Table

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
ADD { partition_less_than_item... | partition_start_end_item }

• The partition_less_than_item syntax can only be used for range partitioned tables. Otherwise, an error will be reported.
• The syntax of partition_less_than_item is the same as the syntax specifying partitions in creating a range partitioned table.
• If the boundary value of the last partition is a MAXVALUE, new partitions cannot be added. Otherwise, an error will be reported.

add_clause specifies list partitioning.

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
ADD list_partition_item

drop_clause: Delete a Specified Partition or Multiple Partitions From a Partitioned Table

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
DROP PARTITION  { partition_name | FOR (  partition_value [, ...] )  } 

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
DROP PARTITION  { partition_name [, ... ] }

truncate_partitioned_clause: Clear Data in a Table Partition

1
2

ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
TRUNCATE PARTITION { partition_name | FOR (  partition_value  [, ...] )  };

Syntax for Modifying a Partition Name

1
2

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

Example: Create a Range Partitioned Table customer_address

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

DROP TABLE IF EXISTS customer_address;
CREATE TABLE customer_address
(
    ca_address_sk       INTEGER                  NOT NULL   ,
    ca_address_id       CHARACTER(16)            NOT NULL   ,
    ca_street_number    CHARACTER(10)                       ,
    ca_street_name      CHARACTER varying(60)               ,
    ca_street_type      CHARACTER(15)                       ,
    ca_suite_number     CHARACTER(10)                    
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE(ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(100),
        PARTITION P2 VALUES LESS THAN(200),
        PARTITION P3 VALUES LESS THAN(300)
);

Example: Create a List Partitioned Table

DROP TABLE IF EXISTS data_list;
CREATE TABLE data_list(
    id int,
    time int,
     sarlay decimal(12,2)
)PARTITION BY LIST (time)(
        PARTITION P1 VALUES (202209),
        PARTITION P2 VALUES (202210,202208),
        PARTITION P3 VALUES (202211),
        PARTITION P4 VALUES (202212),
        PARTITION P5 VALUES (202301)
);

Example: Use modify_clause to Set Whether a Partitioned Index Is Usable

Create the local index student_grade_index for the partitioned table customer_address and set partition index names.

1
2
3
4
5
6

CREATE INDEX customer_address_index ON customer_address(ca_address_id) LOCAL
(
        PARTITION P1_index,
        PARTITION P2_index,
        PARTITION P3_index
);

Rebuild all indexes on partition P1 in the partitioned table customer_address.

1

ALTER TABLE customer_address MODIFY PARTITION P1 REBUILD UNUSABLE LOCAL INDEXES;

Set all indexes in partition P3 of the partitioned table customer_address to be unusable.

1

ALTER TABLE customer_address MODIFY PARTITION P3 UNUSABLE LOCAL INDEXES;

Example: Use add_clause to Add One or More Partitions to a Specified Partitioned Table

Add a partition to the range partitioned table customer_address.

1

ALTER TABLE customer_address ADD PARTITION P5 VALUES LESS THAN (500);

Add the following partitions to the range partitioned table customer_address: [500, 600), [600, 700).

1

ALTER TABLE customer_address ADD PARTITION p6 START(500) END(700) EVERY(100);

Add the MAXVALUE partition p7 to the range partitioned table customer_address.

1

ALTER TABLE customer_address ADD PARTITION p7 END(MAXVALUE);

Add partition P6 to a list partitioned table.

1

ALTER TABLE data_list ADD PARTITION P6 VALUES (202302,202303);

Example: Use split_clause to Split a Partition into Multiple Partitions

Split partition P7 in the range partitioned table customer_address at 800.

1

ALTER TABLE customer_address SPLIT PARTITION P7 AT(800) INTO (PARTITION P6a,PARTITION P6b); 

Split the partition at 400 in the range partitioned table customer_address into multiple partitions.

1

ALTER TABLE customer_address SPLIT PARTITION FOR(400) INTO(PARTITION p_part START(300) END(500) EVERY(100));

Split partition P2 in the list partitioned table data_list into two partitions: p2a and p2b.

1

ALTER TABLE data_list SPLIT PARTITION P2 VALUES(202210) INTO (PARTITION p2a,PARTITION p2b);

Example: Use exchange_clause to Migrate Data from a Common Table to a Specified Partition

The following example demonstrates how to migrate data from table math_grade to partition math in partitioned table student_grade.

1. Create the partitioned table student_grade.

   1 2 3 4 5 6 7 8 9 10 11 12 13

   CREATE TABLE student_grade ( stu_name char(5), stu_no integer, grade integer, subject varchar(30) ) PARTITION BY LIST(subject) ( PARTITION gym VALUES('gymnastics'), PARTITION phys VALUES('physics'), PARTITION history VALUES('history'), PARTITION math VALUES('math') );

2. Add data to the partitioned table student_grade.

   1 2 3 4 5 6 7

   INSERT INTO student_grade VALUES ('Ann', 20220101, 75, 'gymnastics'), ('Jeck', 20220103, 60, 'math'), ('Anna', 20220108, 56, 'history'), ('Jann', 20220107, 82, 'physics'), ('Molly', 20220104, 91, 'physics'), ('Sam', 20220105, 72, 'math');

3. Query the records of partition math in student_grade.

   1	SELECT * FROM student_grade PARTITION (math);

   The query result is as follows:

    stu_name |  stu_no  | grade | subject
   ----------+----------+-------+---------
    Jeck     | 20220103 |    60 | math
    Sam      | 20220105 |    72 | math
   (2 rows)

4. Create an ordinary table math_grade that matches the definition of the partitioned table student_grade.

   1 2 3 4 5 6 7

   CREATE TABLE math_grade ( stu_name char(5), stu_no integer, grade integer, subject varchar(30) );

5. Insert data to the math_grade table. The data in the student_grade partitioned table conforms to the partition rule of partition math.

   1 2 3 4 5

   INSERT INTO math_grade VALUES ('Ann', 20220101, 75, 'math'), ('Jeck', 20220103, 60, 'math'), ('Anna', 20220108, 56, 'math'), ('Jann', 20220107, 82, 'math');

6. Migrate data from the ordinary table math_grade to partition math in the partitioned table student_grade.

   1	ALTER TABLE student_grade EXCHANGE PARTITION (math) WITH TABLE math_grade;

7. Query the student_grade partitioned table. The results show that data in the math_grade table has exchanged with data in the math partition of the student_grade table.

   1	SELECT * FROM student_grade PARTITION (math);

   1 2 3 4 5 6 7

   stu_name | stu_no | grade | subject ----------+----------+-------+--------- Anna | 20220108 | 56 | math Jeck | 20220103 | 60 | math Ann | 20220101 | 75 | math Jann | 20220107 | 82 | math (4 rows)

8. Query the math_grade table. The result shows that the data stored in the math partition of the student_grade partitioned table has been exchanged to the math_grade table.

   1	SELECT * FROM math_grade;

   1 2 3 4 5

   stu_name | stu_no | grade | subject ----------+----------+-------+--------- Jeck | 20220103 | 60 | math Sam | 20220105 | 72 | math (2 rows)

Example: Use truncate_partitioned_clause to Clear Data in a Table Partition

Clear the p1 partition of the customer_address table.

1

ALTER TABLE customer_address TRUNCATE PARTITION p1;

Examples: Enabling and Disabling ROW MOVEMENT

If ROW MOVEMENT is enabled, the data of a row is moved to a new partition when the partition key of the row is updated to a new value and the new value belongs to another partition.

If a partition key is frequently updated, enabling ROW MOVEMENT may cause extra I/O overhead. To enable it, evaluate service requirements first.

Create a table partitioned by date and enable ROW MOVEMENT.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DROP TABLE IF EXISTS sale_data; 
CREATE TABLE  sales_data (
    sale_id  INT,
    product_name VARCHAR(100),
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023q1 VALUES LESS THAN ('2023-04-01'),
    PARTITION p_2023q2 VALUES LESS THAN ('2023-07-01'),
    PARTITION p_2023q3 VALUES LESS THAN ('2023-10-01'),
    PARTITION p_2023q4 VALUES LESS THAN ('2024-01-01')
)
ENABLE ROW MOVEMENT;

Insert test data.

1
2
3
4

INSERT INTO sales_data (sale_id, product_name, sale_date, amount)
VALUES 
(1, 'Product A', '2023-02-15', 1000.00), -- It belongs to p_2023q1.
(2, 'Product B', '2023-05-20', 1500.00); -- It belongs to p_2023q2.

Query the data distribution of each partition.

1

SELECT tableoid::regclass AS partition, * FROM sales_data ORDER BY sale_id;

Update the partition key (which is moved from p_2023q1 to p_2023q2).

1

UPDATE sales_data SET sale_date = '2023-05-01'  WHERE sale_id = 1;

Query the data distribution of each partition again to confirm that the row has been moved.

1

SELECT tableoid::regclass AS partition, *  FROM sales_data  ORDER BY sale_id;

Disable ROW MOVEMENT.

1

ALTER TABLE sales_data DISABLE ROW MOVEMENT;

Modify the partition key again (which is move from p_2023q2 back to p_2023q2).

UPDATE sales_data SET sale_date = '2023-02-15'  WHERE sale_id = 1;

View the command output. It indicates that ROW MOVEMENT is disabled and the partition key cannot be updated.

Example: Use merge_clause to Merge Multiple Partitions into One

Merge partitions P2 and P3 in the range partitioned table customer_address into one.

1

ALTER TABLE customer_address MERGE PARTITIONS P2, P3 INTO PARTITION P_M; 

Example: Use drop_clause to Delete a Specified Partition From a Partitioned Table

Delete partitions P6a and P6b from partitioned table customer_address.

1

ALTER TABLE customer_address DROP PARTITION P6a, P6b;

Helpful Links

CREATE TABLE PARTITION and DROP TABLE

FAQs

• Practice: Creating and Managing DWS Partitioned Tables
• Practice: Case: Reconstructing Partitioned Tables
• "inserted partition key does not map to any table partition" Is Reported When Data Is Inserted into a Partitioned Table
• Error upper boundary of adding partition MUST overtop last existing partition Is Reported When a New Partition Is Added to a Range Partitioned Table