Re: Materialized Views, Refresh Groups and DDMS_REFRESH Procedures

"Markus Kunze" <markus.kunze_at_sdm.de> wrote in message news:agc5kv$i0m$1_at_solti3.muc.sdm.de...
> 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.

I stand to be corrected, but I don't believe refresh groups changes the behaviour of a complete refresh. Both will perform a truncate before applying the refresh.

The difference between using / not using a refresh group is that Oracle will not release the locks until all the MVs in the refreah group have been refreshed, ensuring that all MVs are viewed consistently (at the same point in time).

>
> 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.

You can *only* read *consistent* data. Oracle ensures this by locking the MVs in the refresh group during the refresh to prevent data being changed that could lead to inconsistencies. The data you read must therefore always be consistent (using Oracle's read consistency model) and importantly the data must always be consistent between related tables as well. This is the key reason for refresh groups (that and the fact that many MVs can be refreshed with just one call)

>
> 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.

I beleive this to be in the case with or without refresh groups. Test it and see !!

>
> Or am i wrong?
>
> M

Good Luck

Richard

>
>
>
> "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 - 23:19:58 CDT