Parallelizing partitioned materialized view updates

From: Thomas Day <>
Date: Wed, 23 Jan 2008 13:43:32 -0500
Message-ID: <>

We're refreshing our staging area in our data warehouse with materialized views of our source database.

The problem is that a full refresh (initial refresh) of some of our largest tables is taking too long.

What we'd like to do is partition the table, build a materialized view on that pre-built, partitioned table and have Oracle refresh each partition in parallel. It doesn't work that way. The snapshot refresh serves up the records to the
partitioning mechanism which then decides which partition to put it in.

So, I was thinking, why not create each partition as a separate table, create a materialized view on each table (with a where clause in the select statement to enforce the partitioning), update each table simultaneously, and swap the
tables into the partitioned table with transportable tablespaces?

It shojuld work great for the initial, full refresh. The problem is that the resulting partitioned table cannot be fast refreshed by itself (since it's never had its own full refresh).

Now, would Oracle let us move the tables into partitions without droping the materialized views first? I don't know yet. We'll experiement on that.

If it were possible and we swapped an empty partition with these tables, would the fast refresh bring over only the incremental changes? I think that I see
serious problems with trying to update records that aren't present.

If we swapped the partitions back to being independent tables (and the materialized views were intact), would the fast refresh bring over only the increamental changes?

If we partitioned the materialized view log on the partitioned table/materialized view and brought the MV log together the same way that we're swapping the
partitions into the table, is there some way to befuddle the source database into thinking that it has done a full refresh of the partitioned materialized view
and have it just do fast refreshes?

Our other alternative is to just slap a regular view with a union all on top of these tables to let the ETL folks think that it's a single table.

Any advice, suggestions, or tall tales from your own experience would be gladly appreciated.


Received on Wed Jan 23 2008 - 12:43:32 CST

Original text of this message