10.3.6 ALTER TABLE… EXCHANGE PARTITION

Table of Contents Previous Next



The ALTER TABLE…EXCHANGE PARTITION command swaps an existing table with a partition. 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
[(INCLUDING | EXCLUDING) INDEXES]
ALTER TABLE target_table
EXCHANGE SUBPARTITION
target_subpartition
WITH TABLE
source_table
[(INCLUDING | EXCLUDING) INDEXES]
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.
If the INCLUDING INDEXES clause is specified with EXCHANGE PARTITION, then matching indexes in the target_partition and source_table are swapped. Indexes in the target_partition with no match in the source_table are rebuilt and vice versa (that is, indexes in the source_table with no match in the target_partition are also rebuilt).
If the EXCLUDING INDEXES clause is specified with EXCHANGE PARTITION, then matching indexes in the target_partition and source_table are swapped, but the target_partition indexes with no match in the source_table are marked as invalid and vice versa (that is, indexes in the source_table with no match in the target_partition are also marked as invalid).
The previously used matching index term refers to indexes that have the same attributes such as the collation order, ascending or descending direction, ordering of nulls first or nulls last, and so forth as determined by the CREATE INDEX command.
If both INCLUDING INDEXES and EXCLUDING INDEXES are omitted, then the default action is the EXCLUDING INDEXES behavior.
The same behavior as previously described applies for the target_subpartition used with the EXCHANGE SUBPARTITION clause.
You must own a table to invoke ALTER TABLEEXCHANGE PARTITION or ALTER TABLE EXCHANGE SUBPARTITION against that table.
The name of the table that will replace the target_partition or target_subpartition.
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:


Table of Contents Previous Next