Re: Slow refresh mview
From: Steve Howard <stevedhoward_at_gmail.com>
Date: Tue, 21 Dec 2010 13:08:19 -0800 (PST)
Message-ID: <9f21314e-8385-4808-bfaa-c510dbfc0613_at_32g2000yqz.googlegroups.com>
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
Date: Tue, 21 Dec 2010 13:08:19 -0800 (PST)
Message-ID: <9f21314e-8385-4808-bfaa-c510dbfc0613_at_32g2000yqz.googlegroups.com>
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 "nonatomic" 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. Received on Tue Dec 21 2010 - 15:08:19 CST