EXCHANGE PARTITION

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.

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

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.

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

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

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