3.5.6 ALTER TABLE… EXCHANGE PARTITION

Table of Contents Previous Next


3 Enhanced SQL Features : 3.5 Partitioning Command Syntax : 3.5.6 ALTER TABLE… EXCHANGE PARTITION

The ALTER TABLE…EXCHANGE PARTITION command swaps an existing table with a partition or subpartition. If you plan to add a large quantity of data to a partitioned table, you can use the ALTER TABLEEXCHANGE PARTITION command to implement a bulk load. You can also use the ALTER TABLEEXCHANGE PARTITION command to remove old or unneeded data for storage.
ALTER TABLE target_table
EXCHANGE PARTITION
target_partition
WITH TABLE
source_table
[(WITH | WITHOUT) VALIDATION];
ALTER TABLE target_table
EXCHANGE SUBPARTITION
target_subpartition
WITH TABLE
source_table
[(WITH | WITHOUT) VALIDATION];
You can exchange a partition with the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause.
You can exchange a subpartition with EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause.
When the ALTER TABLEEXCHANGE PARTITION command completes, the data originally located in the target_partition will be located in the source_table, and the data originally located in the source_table will be located in the target_partition.
The ALTER TABLEEXCHANGE PARTITION command can exchange partitions in a LIST, RANGE or HASH partitioned table. The structure of the source_table must match the structure of the target_table (both tables must have matching columns and data types), and the data contained within the table must adhere to the partitioning constraints.
Advanced Server accepts the WITHOUT VALIDATION clause, but ignores it; the new table is always validated.
You must own a table to invoke ALTER TABLEEXCHANGE PARTITION or ALTER TABLE EXCHANGE SUBPARTITION against that table.
The example that follows demonstrates swapping a table for a partition (americas) of the sales table. You can create the sales table with the following command:
Querying the sales table shows that only one row resides in the americas partition:
The following command creates a table (n_america) that matches the definition of the sales table:
The following command adds data to the n_america table. The data conforms to the partitioning rules of the americas partition:
Querying the sales table shows that the contents of the n_america table has been exchanged for the content of the americas partition:
Querying the n_america table shows that the row that was previously stored in the americas partition has been moved to the n_america table:

3 Enhanced SQL Features : 3.5 Partitioning Command Syntax : 3.5.6 ALTER TABLE… EXCHANGE PARTITION

Table of Contents Previous Next