Swapping Partitions

SWAP_PARTITIONS_BETWEEN_TABLES combines the operations of DROP_PARTITION and MOVE_PARTITIONS_TO_TABLE as a single transaction. SWAP_PARTITIONS_BETWEEN_TABLES is useful if you regularly load partitioned data from one table into another and need to refresh partitions in the second table.

For example, you might have a table of revenue that is partitioned by date, and you routinely move data into it from a staging table. Occasionally, the staging table contains data for dates that are already in the target table. In this case, you must first remove partitions from the target table for those dates, then replace them with the corresponding partitions from the staging table. You can accomplish both tasks with a single call to SWAP_PARTITIONS_BETWEEN_TABLES.

By wrapping the drop and move operations within a single transaction, SWAP_PARTITIONS_BETWEEN_TABLES maintains integrity of the swapped data. If any task in the swap operation fails, the entire operation fails and is rolled back.

Examples

In the following example, SWAP_PARTITIONS_BETWEEN_TABLES drops from table member_info all partitions in range specified by partition keys 2008 and 2009. It replaces the dropped partitions with the corresponding partitions in source table customer_info:

=> SELECT SWAP_PARTITIONS_BETWEEN_TABLES('customer_info',2008,2009,'member_info');
                           SWAP_PARTITIONS_BETWEEN_TABLES
-----------------------------------------------------------------------------------
 1 partition values from table customer_info and 2 partition values from table 
member_info are swapped at epoch 1250.

 

See Also

Tutorial for Swapping Partitions