EXCHANGE PARTITION

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

View PDF

You can run this command to exchange the data in a partition with that in an ordinary table. This command can quickly import data to or export data from a partitioned table, achieving efficient data loading. In service migration scenarios, using EXCHANGE PARTITION is much faster than using common import operation. You can exchange a partition by specifying the partition name or partition value.

NOTICE:

Running this command will invalidate the global index. You can use the UPDATE GLOBAL INDEX clause to update the global index synchronously or rebuild the global index.
When exchanging partitions, you can declare WITH/WITHOUT VALIDATION, indicating whether to validate that ordinary table data meets the partition key constraint rules of the target partition (validated by default). The overhead of data validation is high. If you ensure that the exchanged data belongs to the target partition, you can declare WITHOUT VALIDATION to improve the exchange performance.
You can declare WITH VALIDATION VERBOSE. In this case, the database validates each row of the ordinary table, inserts the data that does not meet the partition key constraint of the target partition to other partitions of the partitioned table, and exchanges the ordinary table with the target partition.

For example, if the following partition definition and data distribution of the exchange_sales table are provided, and the DATE_202001 partition is exchanged with the exchange_sales table, the following behaviors exist based on the declaration clause:

If WITHOUT VALIDATION is declared, all data is exchanged to the DATE_202001 partition. Because '2020-02-03' and '2020-04-08' do not meet the range constraint of the DATE_202001 partition, subsequent services may be abnormal.
If WITH VALIDATION is declared, and '2020-02-03' and '2020-04-08' do not meet the range constraint of the DATE_202001 partition, the database reports an error.

If WITH VALIDATION VERBOSE is declared, the database inserts '2020-02-03' into the DATE_202002 partition, inserts '2020-04-08' into the DATE_202004 partition, and exchanges the remaining data with the DATE_202001 partition.

-- Partition definition
PARTITION DATE_202001 VALUES LESS THAN ('2020-02-01'),
PARTITION DATE_202002 VALUES LESS THAN ('2020-03-01'),
PARTITION DATE_202003 VALUES LESS THAN ('2020-04-01'),
PARTITION DATE_202004 VALUES LESS THAN ('2020-05-01')
-- Data distribution of exchange_sales
('2020-01-15', '2020-01-17', '2020-01-23', '2020-02-03', '2020-04-08')

WARNING:

If the data to be exchanged does not completely belong to the target partition, do not declare WITHOUT VALIDATION. Otherwise, the partition constraint rules will be damaged, and subsequent DML statement results of the partitioned table will be abnormal.

The ordinary table and partition whose data is to be exchanged must meet the following requirements:

The number of columns in an ordinary table is the same as that in a partition, and the information in the corresponding columns is strictly consistent.
The compression information of the ordinary table and partitioned table is 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 are consistent.
The ordinary table is not a temporary table.
The ordinary table and partitioned table do not support dynamic data masking and row-level security constraints.

You can use ALTER TABLE EXCHANGE PARTITION to exchange partitions for a partitioned table.

For example, exchange the partition date_202001 of the partitioned table range_sales with the ordinary table exchange_sales by specifying the partition name without validating the partition key, and update the global index.

ALTER TABLE range_sales EXCHANGE PARTITION (date_202001) WITH TABLE exchange_sales WITHOUT VALIDATION UPDATE GLOBAL INDEX;

Alternatively, exchange the partition corresponding to '2020-01-08' in the range partitioned table range_sales with the ordinary table exchange_sales by specifying a partition value, validate the partition, and insert data that does not meet the target partition constraints into another partition of the partitioned table. Global indexes become invalid after this command is executed because the UPDATE GLOBAL INDEX clause is not used.

ALTER TABLE range_sales EXCHANGE PARTITION FOR ('2020-01-08') WITH TABLE exchange_sales WITH VALIDATION VERBOSE;

Parent topic: Partitioned Table O&M Management

Previous topic: DROP PARTITION

Next topic: TRUNCATE PARTITION

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