RE: MView... Fresh build is much faster than Complete refresh...

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_at_gmail.com]   Sent: Wednesday, August 22, 2007 7:45 AM   To: robertgfreeman_at_yahoo.com
  Cc: oracle-l
  Subject: Re: MView... Fresh build is much faster than Complete refresh...

  On 8/21/07, Robert Freeman <robertgfreeman_at_yahoo.com> 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

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