Partition Exchange

Oracle lets you exchange a partition (or subpartition) of a table with another (non-partitioned) table. It even lets you exchange a subpartitioned partiiton with a partitioned table. The DDL statement to use is

ALTER TABLE … EXCHANGE PARTITION … WITH TABLE …

Partition exchange can be useful in a number of situations, especially in the area of data warehousing. For example:

• Data cleanup
• Data warehouse refresh
• Move data to a different schema (as the two tables may be in different schemas).

The basic workflow is that you create a table with the new contents of the partition and then exchange them. The advantage of this approach over e.g. truncating the table and inserting the rows is that the table is available to queries during the process. Several conditions need to be fulfilled for this mechanism to work.

Columns

The first is that the columns (in physical order) have the same type and size. The column names are irrelevant. This can usually be achieved by creating the table to be used for the new contents from the target table using

CREATE TABLE … AS SELECT * FROM …

(“CTAS*”) The caveats here are hidden and virtual columns. A hidden column (e.g. from SET UNUSED) in the target table will not be part of the new table. Conversely, a virtual column will become a physical column in the new table. Both these situations will prevent partition exchange. If you plan to use partition exchange for maintenance of a table, don’t give it virtual columns, and don’t remove columns with SET UNUSED.

Constraints

The next is that constraints are the same. Again names are irrelevant, but state is important, i.e. whether the constraint is enabled and/or validated. Constraints that are neither enabled nor validated are ignored. It applies to

• Not null constraints
• Other check constraints
• Primary key constraints
• Unique constraints
• Foreign key constraints

If the new table was created with CTAS*, then not null constraints that are enabled and validated are created automatically. Note that primary key and unique constraints are not interchangeable, if a constraint is a primary key on one table, it can not be a unique key on the other, even if the columns are not null. The existence of a unique index on the columns is not sufficient either. The partition key(s) must be part of the primary/unique key.

Incoming foreign keys is another issue. If the table has any referencing foreign keys, then they will be checked as part of partition exchange operation. Rumor has it that if the child table is partitioned the same way, and if the corresponding partition is empty (i.e. has been truncated, or never had any row inserted), then this step is skipped. I have done any investigations into this matter. Partition by reference may also be of help here, but again I have no experience with this.

Indexes

If the target table has indexes they have to handled as well. The options depend on whether an index is local (i.e. partitioned the same way as the table) or global.

Local Indexes

For local indexes, the simplest way is to add indexes to the new table, corresponding to the local indexes on the target table and then specify INCLUDING INDEXES on the EXCHANGE statement. Using this approach, the indexes are available immediately when the exchange takes place. The indexes must be created identically to the ones on the target table. This includes:

• Columns and their order, including function based indexes.
• Key compression.
• Whether the index is unique, non-unique, or bitmap.

For bitmap indexes, there is further the complication that the Håkan factor must match. See my post Bitmap Indexes and the Håkan factor for what this means and for how to achieve the match. The solution requires the use of CTAS*, so it works only for tables with no virtual or hidden columns.

Global Indexes

For global indexes, there are basically two options: Either you rebuild them as part of the exchange operation, or you invalidate them. Either option results in some degree of unavailability. You also have the option to specify exactly what to rebuild; please refer to the Oracle documentation if you want to explore this.

Validation

You also have the option of checking whether you want to check that the partition key of every row during the exchange. If you use partition exchange as part of a maintenance process, you will normally know that this is the case, and you can specify WITHOUT VALIDATION.

If you are not absolutely sure about it, you may specify WITH VALIDATION.

Recommendations

If you plan to use partition exchange as part of day-to-day operations of a system,  you should plan the database such that you (if at all possible):

• Avoid using virtual columns.
• Use only local indexes only.

Also, do not use SET UNUSED for structure changes.

One Little Trick

You may use the technique for a non partitioned table as well. In this case, create the new table as a partitioned table with one partition. Be sure to put at least one column from all unique indexes into the partition key.