ALTER TABLE...EXCHANGE PARTITION v11

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 TABLE… EXCHANGE PARTITION command to implement a bulk load. You can also use the ALTER TABLE… EXCHANGE PARTITION command to remove old or unneeded data for storage.

The command syntax is available in two forms.

The first form swaps a table for a partition:

ALTER TABLE <target_table>
  EXCHANGE PARTITION <target_partition>
  WITH TABLE <source_table>
  [(INCLUDING | EXCLUDING) INDEXES]
  [(WITH | WITHOUT) VALIDATION];

The second form swaps a table for a subpartition:

ALTER TABLE <target_table>
  EXCHANGE SUBPARTITION <target_subpartition>
  WITH TABLE <source_table>
  [(INCLUDING | EXCLUDING) INDEXES]
  [(WITH | WITHOUT) VALIDATION];

Description

When the ALTER TABLE… EXCHANGE 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 TABLE… EXCHANGE 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 TABLE… EXCHANGE PARTITION or ALTER TABLE… EXCHANGE SUBPARTITION against that table.

Parameters

target_table

The name (optionally schema-qualified) of the table in which the partition or subpartition resides.

target_partition

The name of the partition to be replaced.

target_subpartition

The name of the subpartition to be replaced.

source_table

The name of the table that will replace the target_partition or target_subpartition.