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.