Re: Slow refresh mview
From: onedbguru <onedbguru_at_yahoo.com>
Date: Tue, 21 Dec 2010 16:51:57 -0800 (PST)
Message-ID: <5a416a6e-2784-4ad7-b7a8-f6805d4d7e66_at_39g2000yqa.googlegroups.com>
On Dec 21, 4:19 pm, HoneyMonster <some..._at_someplace.invalid> wrote:
> On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
> > On Dec 21, 8:22 am, "bob123" <nom..._at_nowhere.com> wrote:
> >> Hi,
>
> >> I have some Mviews for query rewrite
> >> it takes 15-20 minutes to create them The refresh takes more than 5
> >> hours ...
>
> >> why ?
> >> What can I do to speed this ?
>
> >> Oracle 9.2.0.7
>
> >> Thanks in advance
>
> > In addition to Adrian's comments, in 10g and above you can do a "non-
> > atomic" refresh that truncates the materialized view (rather than
> > deleting the rows) under the covers. This is much faster provided you
> > have your query rewrite stuff set up correctly.
>
> IIRC 9i would do that too on a full refresh, provided you didn't make the
> snapshot^H^H^H mview part of a group.
Date: Tue, 21 Dec 2010 16:51:57 -0800 (PST)
Message-ID: <5a416a6e-2784-4ad7-b7a8-f6805d4d7e66_at_39g2000yqa.googlegroups.com>
On Dec 21, 4:19 pm, HoneyMonster <some..._at_someplace.invalid> wrote:
> On Tue, 21 Dec 2010 13:08:19 -0800, Steve Howard wrote:
> > On Dec 21, 8:22 am, "bob123" <nom..._at_nowhere.com> wrote:
> >> Hi,
>
> >> I have some Mviews for query rewrite
> >> it takes 15-20 minutes to create them The refresh takes more than 5
> >> hours ...
>
> >> why ?
> >> What can I do to speed this ?
>
> >> Oracle 9.2.0.7
>
> >> Thanks in advance
>
> > In addition to Adrian's comments, in 10g and above you can do a "non-
> > atomic" refresh that truncates the materialized view (rather than
> > deleting the rows) under the covers. This is much faster provided you
> > have your query rewrite stuff set up correctly.
>
> IIRC 9i would do that too on a full refresh, provided you didn't make the
> snapshot^H^H^H mview part of a group.
It is a real shame that you are subjected to using such old technology. While it may work, the amount of time you can save doing mundane DBA tasks would more than pay for the upgrade to something a little more current. Being that far behind will also subject you and your company to jumping through lots of hoops to get current. Being that old also subjects you to MANY security risks that have been corrected. Being able to recover easily from failure is another huge benefit to upgrading. I can't imagine what unpatched an vulnerable OS you are running on... I do know that the 9i runInstaller will not even run on some of the later OS versions - (Solaris, AIX, even Linux). Received on Tue Dec 21 2010 - 18:51:57 CST