What is the concept of partition exchange in Oracle? Why is this needed? What are the real world possible scenarios to implement partition exchange in a data ware house?

asked May 14 '14 at 08:56

Diwanshu's gravatar image


One Answer:

You can use partition exchange to swap a staging table with a partition in a fact table. Loading directly into the fact table can be slow performance because:

  • There can be a large number of indexes on the fact table (slowing inserts)
  • If these are bitmap indexes, this limits your ability to load in parallel (bitmap indexes can block concurrent DML)

You can avoid some of these issues by loading the data into a staging table. If you have a fact table partitioned by (transaction) day, then you can:

  • Drop indexes on the staging table
  • Load the data in
  • Create indexes and gather stats (on the staging table)
  • Exchange the staging table with the target partition in the main fact table.

The exchange partition is near-instant and will load the data complete with indexes. When working with large data sets, this process can make the ETL significantly faster.

If you'd like more details, have a look at Tim Gorman's "Scaling to Infinity" article.

answered May 14 '14 at 22:14

Chris%20Saxon's gravatar image

Chris Saxon