You can convert a partition or subpartition into a nonpartitioned table, and a nonpartitioned table into a partition or subpartition of a partitioned table by exchanging their data segments. You can also convert a hash partitioned table into a partition of a composite *-hash partitioned table, or convert the partition of a composite *-hash partitioned table into a hash partitioned table. Similarly, you can convert a range- or list-partitioned table into a partition of a composite *-range or -list partitioned table, or convert a partition of the composite *-range or -list partitioned table into a range- or list-partitioned table.
Exchanging table partitions is useful to get data quickly in or out of a partitioned table. For example, in data warehousing environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an existing partitioned table.
OLTP and data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterward.
When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged with the
INDEXES clause, and if rows are to be validated for proper mapping with the
When you specify
VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if
VALIDATION were specified to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before performing the exchange partition operation:
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name KEEP INDEX
Enable the constraints after the exchange.
If you specify
VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.
Unless you specify
INDEXES, the Oracle Database marks the global indexes or all global index partitions on the table whose partition is being exchanged as
UNUSABLE. Global indexes or global index partitions on the table being exchanged remain invalidated.
You cannot use
INDEXES for index-organized tables. Use
Incremental statistics on a partitioned table are maintained with a partition exchange operation if the statistics were gathered on the nonpartitioned table when
DBMS_STATS table preferences
INCREMENTAL is set to true and
INCREMENTAL_LEVEL is set to
In situations where column statistics for virtual columns are out of order, the column statistics are deleted rather than retaining the stale statistics. Information about this deletion is written to the alert log file.
Oracle Database SQL Tuning Guide for more information about incremental statistics
Oracle Database PL/SQL Packages and Types Reference for information about the
For information about using views to monitor details about partitioned tables and indexes, refer to "Viewing Information About Partitioned Tables and Indexes".
This section contains the following topics: