Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie question re refreshing Materialized Views

Re: Newbie question re refreshing Materialized Views

From: <fitzjarrell_at_cox.net>
Date: Thu, 16 Aug 2007 09:21:13 -0700
Message-ID: <1187281273.323418.219890@g4g2000hsf.googlegroups.com>


Comments embedded.
On Aug 16, 4:45 am, Adam Cameron <adam_j..._at_hotmail.com> wrote:
> > Default complete refresh mechanism for 9i is truncate/insert /*+
> > append*/
>
> So it seems.
>
> > Default complete refresh mechanism for 10g is delete, conventional
> > insert although there is possibility to switch back to old mechanism
> > using dbms_mview.refresh with atomic_refresh = false.
>
> I'm puzzled by your comment here. It sounds like you're suggesting the
> refresh process (of a single view) can be made atomic by using the
> atomic_refresh option.
>
> So this:
> <atom>
> truncate
> repopulate
> </atom>
>
> From my reading of both 9i's and 10g's coverage of atomic_refresh, the
> "atomicity" (misuse of that word, but you get my drift, maybe) of the
> operation is that the list of views are refreshed as an atom, not that the
> refresh process itself is handled as an atom. Am I reading it wrong?
>
> So this:
> <atom>
> view1 refresh
> view2 refresh
> etc
> </atom>
>
> But this in itself doesn't mean the refresh operation for a single view is
> itself atomic. If that makes sense.
>
> If the atomic_refresh option means "handle the refresh itself as an atom",
> then this is exactly what I want.
>
> Could you elaborate on this for me, please? (just for clarification of what
> you were saying).
>
> References:http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_mvie...http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mvie...
>
> I guess I could give it a go and find out, in the mean time.
>
> > So if you are using 9i
>
> Yes, sorry: I should have said that from the outset. I am indeed using 9i.
>
> > then there might be some moments just after
>
> Doesn't this strike you as being quite crap?

No, and you wouldn't, either, but apparently you don't understand that truncate is a DDL operation, not DML, and doesn't generate undo, the primary mechanism for consistent reads. If no undo exists you can't construct a read-consistent view of the data.

>
> > truncate and before insert when the MV is empty. To avoid that one
> > possibility is to use 2 MV approach I was explained in my article
> > Effective search in a normalized application at
> >http://www.gplivna.eu/papers/mat_views_search.htm
> > Of course that means you are querying (possibly) stale data, but
> > usually full refresh means that :)
>
> I shall read this.
>
> > Gints Plivna
>
> Cheers Gints. Your post was very helpful.
>
> --
> Adam

David Fitzjarrell Received on Thu Aug 16 2007 - 11:21:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US