Re: Parallelizing partitioned materialized view updates

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Wed, 23 Jan 2008 13:45:21 -0800 (PST)
Message-ID: <539921.40992.qm@web58813.mail.re1.yahoo.com>

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_at_gmail.com>

<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>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 23 2008 - 15:45:21 CST