Re: Materialized Views, Refresh Groups and DDMS_REFRESH Procedures

Hi Richard,

I read in Oracle Metalink 2 Notes about that: 244561.995, 1029824.6. You are right with the consistence thing:

It looks to me as if the difference between "grouping 2 mvs" and "setting both mvs atomic"
is the way Oracle handles COMPLETE REFRESH. When not using a group, oracle truncates the table and, if the following insert results in an error, the mv was NULL. When using a group oracle doesnt truncate and is able to rollback.

but how can i guarantee a consistant state then?

When using refresh group: during the time the refresh takes for all mvs I dont know if i read old or new data.

When using no refresh group but setting my mvs to ATOMIC: Oracle truncates my COMPLETE REFRESH mvs and , in case of an error, performs a rollback but leaves the error-mv empty.

Or am i wrong?

M

"Richard Foote" <richard.foote_at_bigpond.com> schrieb im Newsbeitrag news:MefW8.30617$Hj3.92566_at_newsfeeds.bigpond.com...
> Hi Markus,
>
> The use of refresh groups is that by grouping MVs together and refreshing
> them together ensures that all grouped MVs are "consistent" after the
> refresh. This eliminates RI issues between the related MVs. In my view
it's
> incorrect to view the refresh as being ONE transaction, but rather the
> applying of a collection of transactions to a specific point in time.
>
> There may also be performance benefits in applying one large group of
> changes than applying the changes separately (it depends).
>
> Have a good read of the Replication manual, it's all explained in there.
>
> Good Luck
>
> Richard
> "Markus Kunze" <markus.kunze_at_sdm.de> wrote in message
> news:1103_1026126341_at_news.sdm.de...
> > Hi,
> >
> > I have defined MVs (A, B, C) on tables of my database. The mvs are
defined
> with FAST refresh option and with a given period NEXT 'SYSDATE+320/86400'.
> > I want the MVs to update periodically and the update transaction must be
> atomic.
> > According to Oracle Docs I executed the following statement:
> >
> > BEGIN
> > DBMS_SNAPSHOT.REFRESH (
> > 'A,B,C'
> > ,NULL
> > ,NULL
> > ,TRUE
> > ,FALSE
> > ,1
> > ,0
> > ,0
> > ,TRUE);
> > END;
> > /
> >
> > This means my mvs will refresh FAST (their own refresh option) every 5
> minutes in ONE transaction.
> >
> > But I also found information about refresh groups in the oracle docs and
I
> don't understand what they are used for.
> >
> > Can anyone help me? What is the use for Refresh Groups?
> >
> > Is it better to group my mvs first and define the refresh option on the
> refresh group?
> >
> > Thanks in advance
> >
> > Markus
> >
> >
>
>
Received on Mon Jul 08 2002 - 08:55:43 CDT