This topic describes how to use EXCHANGE PARTITION to exchange a partition or subpartition of a partitioned table with a non-partitioned table.
Syntax
The ALTER TABLE...EXCHANGE PARTITION command is used to exchange a partition or subpartition of a partitioned table with a non-partitioned table that has the same table schema with the partitioned table. Syntax:
ALTER TABLE target_table
EXCHANGE PARTITION target_partition
WITH TABLE source_table
[{WITH | WITHOUT} VALIDATION];Parameters
Parameter | Description |
target_table | The name of the destination table. |
target_partition | The name of the destination partition. |
source_table | The name of the source table. |
When ALTER TABLE...EXCHANGE PARTITION command is executed, rows in the target_partition are swapped into the source_table, and rows in the source_table are swapped into the target_partition.
When WITHOUT VALIDATION is specified, the ALTER TABLE ... EXCHANGE PARTITION operation does not perform any row-by-row validation when exchanging a partition a non-partitioned table, similar to an "INSTANT DDL" operation. This allows database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition.
When you perform the EXCHANGE PARTITION operation, make sure that source_table and target_table have the same structure, columns, data types, engines, table attributes, and indexes.
Limitations
If a field has been added to the partition table or the non-partitioned table by using a INSTANT ADD COLUMN statement, the EXCHANGE PARTITION syntax cannot be used for exchange.
Performance test
Exchange p0 of sales_list with sales_list_tmp.
ALTER TABLE sales_list
EXCHANGE PARTITION p0
WITH TABLE sales_list_tmp;