The ALTER TABLE...EXCHANGE PARTITION command swaps an existing table with a partition or subpartition.
Overview
If you plan to add a large quantity of data to a partitioned table, you can use the ALTER TABLE...EXCHANGE PARTITION command to transfer a bulk load of data. You can also use the ALTER TABLE...EXCHANGE PARTITION command to remove outdated or redundant data from storage.
The ALTER TABLE...EXCHANGE PARTITION command has two forms.
- The first form swaps a table for a partition:
ALTER TABLE target_table EXCHANGE PARTITION target_partition WITH TABLE source_table [(WITH | WITHOUT) VALIDATION]; - The second form swaps a table for a subpartition:
ALTER TABLE target_table EXCHANGE SUBPARTITION target_subpartition WITH TABLE source_table [(WITH | WITHOUT) VALIDATION];
The ALTER TABLE...EXCHANGE PARTITION command makes no distinctions between a partition and a subpartition:
- You can exchange a partition by using the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause.
- You can exchange a subpartition by using EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause.
Description
When the ALTER TABLE...EXCHANGE PARTITION command completes, the data is swapped. The data that originally resides in the target partition resides in the source table, and the data that originally resides in the source table resides in the target partition.
The structure of the source table must match the structure of the target table (both tables must have matching columns and data types). The data contained within the table must adhere to the partitioning constraints.
PolarDB for PostgreSQL(Compatible with Oracle) accepts the WITHOUT VALIDATION clause, but ignores it. The new table is always validated.
You must own a table to call ALTER TABLE...EXCHANGE PARTITION or ALTER TABLE...EXCHANGE SUBPARTITION against that table.
Parameters
| Parameter | Description |
|---|---|
| target_table | The name (optionally schema-qualified) of the table in which the partition resides. |
| target_partition | The name of the partition or subpartition to be replaced. |
| source_table | The name of the table that will replace the target_partition. |
Example - exchange a table for a partition
The following example demonstrates exchanging a table for a partition (americas) of the sales table. You can run the following command to create the sales table:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
)
PARTITION BY LIST(country)
(
PARTITION europe VALUES('FRANCE', 'ITALY'),
PARTITION asia VALUES('INDIA', 'PAKISTAN'),
PARTITION americas VALUES('US', 'CANADA')
);Run the following command to add sample data to the sales table:
INSERT INTO sales VALUES
(40, '9519b', 'US', '12-Apr-2012', '145000'),
(10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
(20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
(20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
(10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
(10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
(10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
(20, '3788b', 'INDIA', '21-Sept-2012', '5090'),
(20, '4519a', 'INDIA', '18-Oct-2012', '650000'),
(20, '4519b', 'INDIA', '2-Dec-2012', '5090');Querying the sales table shows that only one row resides in the americas partition:
acctg=# SELECT tableoid::regclass, * FROM sales;
tableoid | dept_no| part_no | country | date | amount
---------------+--------+---------+---------+-------------------+-----------
sales_europe | 10| 4519b | FRANCE | 17-JAN-12 00:00:00| 45000
sales_europe | 10| 9519b | ITALY | 07-JUL-12 00:00:00| 15000
sales_europe | 10| 9519a | FRANCE | 18-AUG-12 00:00:00| 650000
sales_europe | 10| 9519b | FRANCE | 18-AUG-12 00:00:00| 650000
sales_asia | 20| 3788a | INDIA | 01-MAR-12 00:00:00| 75000
sales_asia | 20| 3788a | PAKISTAN| 04-JUN-12 00:00:00| 37500
sales_asia | 20| 3788b | INDIA | 21-SEP-12 00:00:00| 5090
sales_asia | 20| 4519a | INDIA | 18-OCT-12 00:00:00| 650000
sales_asia | 20| 4519b | INDIA | 02-DEC-12 00:00:00| 5090
sales_americas| 40| 9519b | US | 12-APR-12 00:00:00| 145000
(10 rows)The following command creates a table (n_america) that matches the definition of the sales table:
CREATE TABLE n_america
(
dept_no number,
part_no varchar2,
country varchar2(20),
date date,
amount number
);The following command adds data to the n_america table. The data conforms to the partitioning rules of the americas partition:
INSERT INTO n_america VALUES
(40, '9519b', 'US', '12-Apr-2012', '145000'),
(40, '4577b', 'US', '11-Nov-2012', '25000'),
(30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
(30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
(30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
(40, '3788a', 'US', '12-May-2012', '4950'),
(40, '4788a', 'US', '23-Sept-2012', '4950'),
(40, '4788b', 'US', '09-Oct-2012', '15000');The following command swaps the table into the partitioned table:
ALTER TABLE sales
EXCHANGE PARTITION americas
WITH TABLE n_america; Querying the sales table shows that the content of the n_america table has been exchanged for the content of the americas partition:
acctg=# SELECT tableoid::regclass, * FROM sales;
tableoid | dept_no| part_no | country | date | amount
---------------+--------+--------+----------+--------------------+-----------
sales_europe | 10| 4519b | FRANCE | 17-JAN-12 00:00:00 | 45000
sales_europe | 10| 9519b | ITALY | 07-JUL-12 00:00:00 | 15000
sales_europe | 10| 9519a | FRANCE | 18-AUG-12 00:00:00 | 650000
sales_europe | 10| 9519b | FRANCE | 18-AUG-12 00:00:00 | 650000
sales_asia | 20| 3788a | INDIA | 01-MAR-12 00:00:00 | 75000
sales_asia | 20| 3788a | PAKISTAN| 04-JUN-12 00:00:00 | 37500
sales_asia | 20| 3788b | INDIA | 21-SEP-12 00:00:00 | 5090
sales_asia | 20| 4519a | INDIA | 18-OCT-12 00:00:00 | 650000
sales_asia | 20| 4519b | INDIA | 02-DEC-12 00:00:00 | 5090
sales_americas| 40| 9519b | US | 12-APR-12 00:00:00 | 145000
sales_americas| 40| 4577b | US | 11-NOV-12 00:00:00 | 25000
sales_americas| 30| 7588b | CANADA | 14-DEC-12 00:00:00 | 50000
sales_americas| 30| 9519b | CANADA | 01-FEB-12 00:00:00 | 75000
sales_americas| 30| 4519b | CANADA | 08-APR-12 00:00:00 | 120000
sales_americas| 40| 3788a | US | 12-MAY-12 00:00:00 | 4950
sales_americas| 40| 4788a | US | 23-SEP-12 00:00:00 | 4950
sales_americas| 40| 4788b | US | 09-OCT-12 00:00:00 | 15000
(17 rows)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:
acctg=# SELECT tableoid::regclass, * FROM n_america;
tableoid | dept_no | part_no | country | date | amount
-----------+---------+---------+---------+--------------------+------------
n_america | 40 | 9519b | US | 12-APR-12 00:00:00 | 145000
(1 row)