RE: MView... Fresh build is much faster than Complete refresh...
From: "Robert Freeman" <robertgfreeman@xxxxxxxxx>
To: "Jared Still" <jkstill@xxxxxxxxx>
Date: Wed, 22 Aug 2007 08:04:55 -0600
I'll try that!!
RF
Robert G. Freeman
Oracle Consultant/DBA/Author
Principal Engineer/Team Manager
The Church of Jesus Christ of Latter-Day Saints
Father of Five, Husband of One,
Author of various geeky computer titles
from Osborne/McGraw Hill (Oracle Press)
Oracle Database 11g New Features Now Available for Pre-sales on Amazon.com!
BLOG: http://robertgfreeman.blogspot.com/
Sig V1.2
-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Wednesday, August 22, 2007 7:45 AM
To: robertgfreeman@xxxxxxxxx
Cc: oracle-l
Subject: Re: MView... Fresh build is much faster than Complete refresh...
On 8/21/07, Robert Freeman <robertgfreeman@xxxxxxxxx> wrote:
So I have this materialized view. When I execute the create materialized
view command, it takes 32.39 seconds to build. A complete refresh (exec
dbms_mview.refresh('mview_name','C'); ) from the prompt this takes almost
3-4 times longer. Does anyone have any thoughts on why this might be so just
right off hand. I've not run a 10046 on it yet, but I thought maybe there
is something about MVIEW Full Refreshes that I'm just missing.
10g, right?
Try using atomic_refresh:
Here's an example from a thread here a few months ago:
begin
-- dbms_mview.refresh('MVTEST_MV',method => 'C');
-- use this with 10g to return to truncate/append behavior
dbms_mview.refresh('MVTEST_MV',method => 'C', atomic_refresh=>false);
end;
/
The default refresh in 10g changed to delete/insert.
In 9i it was truncate/insert append.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
