Learning About Exchange Partition Mechanism – Oracle

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.

By |2016-10-31T14:21:28+00:00February 22nd, 2015|Oracle|6 Comments

About the Author:

Kumar Gauraw is senior IT professional with over 16 years of experience while serving in various capacities at Fortune 500 companies in the field of Data Integration, Data Modeling, Data Migration and architecting end-to-end Datawarehousing solutions. Kumar has strong experience in a wide spectrum of tools and technologies related to BI and Data Integration.

6 Comments

  1. Sundheep January 12, 2016 at 4:49 pm - Reply

    Hi Sir, thank you for your post.. i have below query on partition swap. i have target table with 3 range partitions values 0-10,10-20 and 20-30.

    In temporary table i stored new data with values ranging from 0-30. Now if i try to swap the partition how it works?

    Thanks in advance

    • Kumar Gauraw January 12, 2016 at 5:49 pm - Reply

      Hi Sundheep,

      For Exchange Partition Load to work (partition swap as you said) only works if the temporary table has data that fits into one of the partitions.

      In your case, your temp table seems to have data that spreads across all three partitions and therefore, it will throw error when you try the swap.

      Your temp table needs to have data that is either in 0-10 or 10-20 or 20-30 range to be swapped with a partition that is appropriate for the data in the table.

      Hope this answers your question.

      Regards,
      Kumar

  2. Giovani Marinho July 13, 2017 at 1:17 pm - Reply

    Hi Kumar,

    When we do exchange a partition without validation (no indexes, fks, pks, etc.), all the data that was on the table goes to the partition (only dictionary updates, no rows movement). That’s fine. But when we do SELECT on the partitioned table, with one (or more) predicates that tells Oracle that look into one partition is enought, Oracle will look just to one partition, or it will do FTS against the partitioned table because of “without validation” clause on exchange partition?

  3. Kumar January 30, 2018 at 2:48 am - Reply

    Hi Kumar Gauraw,

    Does exchange partition required additional storage/space to perform the activity ?

    For ex : If a table size is 10TB, how much space its required to complete exchange partition activity?

    Thanks
    Kumar

  4. masoud haghverdi February 12, 2018 at 12:33 pm - Reply

    hi
    i have few question, i want to exchange a non-partitioned table to partitioned table with exchange command.
    what is the correct command for my request?
    the non-partitioned table has a lot of data inside it. about 2GB. what comes up with the data?
    does the table data move?

  5. Richard Antony October 10, 2018 at 11:32 am - Reply

    Can we do partition switch from one database schema to different database schema?
    For Eg:

    Server – 192.168.10.10 , Database Name : ORCL Schema Name : Source Table : Src_table
    Server – 192.168.10.11 , Database Name : ORCLK Schema Name : Destination Table : Dest_table

    Both table have the same columns. Can we do switch the data from Src_table to Dest_table using Database link ? Is it possible ?

    Thanks
    Richard Antony

Leave A Comment