Re: MV refresh
- From: Roger Xu <wellmetus@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 2 Sep 2009 11:50:23 -0500
Hello again,
I created a MV and create some indexes on the MV. Now the same refresh takes
much longer.
Is there an easy way to defer the index building until the MV refresh is
done?
Otherwise, I would have to:
1) drop indexes;
2) execute DBMS_MVIEW.refresh('PAYMENT','C');
3) create indexes;
Also, what is the right way to collect statistics after MV refresh?
Thanks,
Roger Xu
:
> On Thu, Aug 27, 2009 at 3:58 PM, Allen, Brandon <
> Brandon.Allen@xxxxxxxxxxx> wrote:
>
> Not positive off the top of my head, but I think it’s possible for that to
> happen if you don’t use the atomic refresh setting because then it does a
> truncate first, which is a DDL transaction, followed by the inserts, however
> if you use the atomic refresh, then it will keep the whole refresh together
> as a single transaction, which means it uses DELETE instead of TRUNCATE, so
> it’s slower and generates significantly more redo. If I recall correctly
> there was also a change related to this functionality between 9i and 10g,
> but I forget the details – just search for atomic refresh on Metalink and
> I’m sure you’ll find it. Regarding capturing the error message, just
> redirect the output of your refresh script to a log file and then run a
> check on that log file to see if there are errors or not.
>
>
>
> Regards,
>
> Brandon
>
>
>
> On Thu, Aug 27, 2009 at 3:50 PM, Roger Xu < wellmetus@xxxxxxxxx > wrote:
>
>
>
> Hi MV gurus,
>
> Is it possible that when a MV is being refreshed but gets some kind of
> error and ends up an empty MV in 10g?
>
> exec DBMS_MVIEW.refresh('MYMV','C'); - here is how we refresh.
>
> The application team wants me to write a procedure to refresh MV and
> capture the error message if it fails and restore the MV to the previous
> state. How do I capture the error? I know I can show errors in SQL*PLUS.
>
> Thanks,
>
> Roger Xu
>
> --
>
>
>
>
> ------------------------------
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>
>
>
>
>
> *Click here <http://www.drpeppersnapplegroup.com/about/corporate>* to
> learn more about Dr Pepper Snapple Group’s commitment to corporate* *
> social* *responsibility.
>
> Please be conscious of the environment and print this email only if
> absolutely necessary.
>
> This e-mail (including any attachments) is confidential and may contain
> privileged information of Dr Pepper Snapple Group, Inc. and/or its
> subsidiaries ("Dr Pepper Snapple Group"). If you are not the intended
> recipient or receive it in error, you may not use, distribute, disclose or
> copy any of the information contained within it and it may be unlawful to do
> so. If you are not the intended recipient, please notify us immediately by
> returning this e-mail to us at mailerror@xxxxxxxx and destroy all
> copies. Any views expressed by individuals within this e-mail do not
> necessarily reflect the views of Dr Pepper Snapple Group. This e-mail does
> not constitute a binding offer, acceptance, amendment, waiver or other
> agreement, unless the intent that an e-mail will constitute such is clearly
> stated in the body of the email. Recipients are advised to subject this
> e-mail and attachments to their own virus checking, in keeping with good
> computing practice. Please note that e-mail received by Dr Pepper Snapple
> Group may be monitored in accordance with applicable law.
>
>
Other related posts:
