Parallelizing partitioned materialized view updates

  • From: "Thomas Day" <tomdaytwo@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 23 Jan 2008 13:43:32 -0500

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.

Thanks

Other related posts: