Oracle 11g partition maintenance (4) -- Exchanging Partitions

Oracle 11g partition maintenance (4) -- Exchanging Partitions

Swap Partition

You can convert a partition (or sub-partition) into a non-partition table by exchanging data segments, or convert a non-partition table into a partition (or sub-partition) of a partition table ). You can also convert a hash partition table into a partition of a composite hash partition table, or convert a partition of a composite hash partition table into a hash partition table. Similarly, you can convert a [range | list] partition table into a partition of a composite [range | list] partition table, you can also convert a partition of a composite [range | list] partition table into a [range | list] partition table.

When you need to convert a non-partition table into a partition of a partition table, it is very useful to swap table partitions. For example, in a data warehouse environment, swap partitions help to quickly load new data to an existing partition table. In general, both OLTP and data warehouse benefit from the exchange of old partition data from a partition table. This data is cleared from the partition table without actual deletion and can be archived separately later.

When you swap shards, the log attributes are retained. You can specify whether the local index is also exchanged (including indexes clause), and you can specify whether the row is verified WITH the appropriate ing (with validation clause ).

Note:

When you specifyWITHOUT VALIDATIONClause, this is usually a very fast operation, because it only involves updating the data dictionary. However, if the primary key or unique constraint is enabled for the table or partition table involved in the exchange operation, the switch operation is executed by default.WITH VALIDATIONTo maintain the integrity of the constraints.

To avoid overhead of the verification operation in this case, execute the following statement for each constraint before performing the swap partition operation: