Re: Newbie question re refreshing Materialized Views

> 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_mview2.htm#94156 http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mview.htm#sthref4927

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?

> 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