Re: Parallelizing partitioned materialized view updates
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: tomdaytwo@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 23 Jan 2008 13:45:21 -0800 (PST)
Thomas
Have you considered using a good old fashioned partition view based on N
materialised views, each of which can (I believe) be fast refreshed from the
source? Partition views have been discouraged since 8i (see
http://download-uk.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/partiti.htm#12345)
but they still seem to work.
Jonathan Lewis has a venerable (11/1996) article here
http://www.jlcomp.demon.co.uk/pv.doc on how to set up partition views, and how
the optimizer copes with them (and some gotchas at the time). Now, do they
still work?
I've tried simple tables, and my plan on a simple pv over 2 tables was (Oracle
XE 10.2.0.1.0):
Execution Plan
----------------------------------------------------------
Plan hash value: 2602506964
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62 | 30194 | 3 (0)| 00:00:01 |
| 1 | VIEW | TESTPV | 62 | 30194 | 3 (0)| 00:00:01 |
| 2 | UNION-ALL PARTITION| | | | | |
|* 3 | TABLE ACCESS FULL | TEST1 | 72 | 35064 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| TEST2 | 1 | 487 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("PARTNR"=1)
4 - filter(NULL IS NOT NULL)
5 - filter("PARTNR"=1)
That suggests it's all still happening, but you may want a more scientific test
based on MVs...
Good luck
Nigel
----- Original Message ----
From: Thomas Day <tomdaytwo@xxxxxxxxx>
<snip>
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?
<snip>
Other related posts:
