If you have been involved in creating and managing ETL (Extract, Transformation and Load) processes for datawarehousing environments, you know how time consuming it can be especially when data volume is high.

One of the best features in Oracle data warehousing is the ability to swap-out standard Oracle tables and partitioned tables.

If you are on Oracle database, Partition Exchange is one of the best features that can dramatically help you improve data loading time while loading a relatively small amount of data into a target table that contains a much larger volume of historical data. The target can be any partitioned table – a dimension, a fact or any other table.

About Partition Exchange Loading

Partition Exchange is about being able to exchange a partition (or sub-partition) of a table with another (non-partitioned) table. The DDL statement to use for performing Exchange Partition goes like this:

ALTER TABLE <table_name>
EXCHANGE PARTITION <partition_name>
WITH TABLE <new_table_name>
<including | excluding> INDEXES <with | without> VALIDATION
EXCEPTIONS INTO <schema.table_name>;

Partition exchange feature can be useful in a number of situations. For example:

  • Data cleanup activities
  • Datawarehouse/table refresh
  • Moving data from one schema to another schema

The idea is that you have a source table with the new data which needs to be loaded into a partitioned table and then you use the exchange partition mechanism to move data from source table into the target partitioned table without moving data. It is a data dictionary update.

Exchange partition is an operation with no actual data movement and therefore, performance improvement is significant. In addition, the biggest advantage is that, unlike delete/insert processes, the table remains available for performing queries.

Important Points Related To Exchange Partition

There are a few important points you need to remember while doing exchange partition in Oracle:

  • The tables in ALTER TABLE EXCHANGE PARTITION must have the same number and type of columns.
  • Exchange Partition using WITHOUT VALIDATION is the normally a fast operation because it involves only data dictionary updates. It doesn’t do any constraint validation.

    However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation will be performed as if WITH VALIDATION were specified in order to maintain the integrity of the constraints. The validation process can slow down the operation.

  • Exchange partition mechanism can be used to exchange a partitions of a range, hash, or list-partitioned table with a non-partitioned table, or the reverse.

    For examples and steps involved in doing various partitions, visit http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#i1107555

  • Primary Key on the partitioned target table must be GLOBAL if the source table involved in Exchange Partition has a primary key in which case UPDATE GLOBAL INDEXES must be used while performing the operation.

    This slows down the process significantly and therefore, it is recommended to drop (before the Exchange Partition operation) and rebuild global indexes (after the operation) which works much faster.Visit http://www.akadia.com/services/ora_exchange_partition.html for some great examples and steps to simulate this.

  • You cannot use the EXCHANGE (SUB)PARTITION command when a FOREIGN KEY constraints are enabled on one of the tables exchanged, unless the reference table is empty.

ETL Tools That Support Exchange Partition Mechanism

Exchange Partition is a powerful way to load data into partitioned datawarehouse tables. However, here is the list of ETL tools that support this mechanism when the target database is Oracle:

    • Oracle Warehouse Builder (OWB) – Natively supported
    • Oracle Data Integrator (ODI) – Knowledge Module available
    • Informatica Power Center ( Using Stored Procedure transformation or Command stage)

We will discuss how to perform Partition Exchange using each of these tools in separate articles. However, I hope this gives a good insight into this mechanism and how it can be used for data movement in DWH environments.

Your Turn – Share Your Thoughts

Do you have some experience in Partition Exchange mechanism? Please share through your comments. We would love to learn from you.