Re: Materialized Views, Refresh Groups and DDMS_REFRESH Procedures

Hi Richard,

thanks a lot!

Now I know that refresh groups are the right thing for me!

Greetings from Munich!

Markus

"Richard Foote" <richard.foote_at_bigpond.com> schrieb im Newsbeitrag news:eptW8.31035$Hj3.93804_at_newsfeeds.bigpond.com...
>
> "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 Tue Jul 09 2002 - 06:39:15 CDT