You can do a fast refresh on a complex mview BUT only under very specific
circumstances - see the Oracle documentation for all the details. One
workaround is to do fast refreshes on individual tables, then do your joins
in a view in the destination database. In 9i you can even do a materialized
view on the individual mvs in the destination database - see the Oracle docs
again.
"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
news:130ba93a.0307260934.6b341bcf_at_posting.google.com...
> Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message
news:<ips2ivcbpu3lfj5jai5248g9cqg9qg53lb_at_4ax.com>...
> > On 25 Jul 2003 09:19:49 -0700, yyoodd_at_hotmail.com (yyoodd) wrote:
> >
> > >I wonder if anyone knows how oracle accomplish the task of Fast
> > >Refreshing MView for joined tables. I know it has logs for all changes
> > >happened on the underlying tables. However, let's say I have an MView
> > >as simple as Select * from T1, T2 where T1.C1=T2.C2 and T1.C2=1. When
> > >there is an insert on T1, how can Oracle tell if it needs to bring new
> > >rows from T2(that now should be in the MView) solely depending on
> > >change logs? Since the data in T2 are not changed themselves. And
> > >where does Oracle retrieve the row data? From the underlying table T2?
> > >What if change happened on T2 at the same time so that the data image
> > >of that row is no longer available? Apply undo?
> > >
> > >So confused....
> > >
> > >yyoodd
> >
> >
> > IIRC you can't have fast refresh on complex mviews. This includes
> > joined tables.
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
> Well, believe it or not, materialized join view can be fast refreshed.
> It can even be fast refreshed "on commit". One of the requirements of
> the MJV is that the ROWID from all the detail tables must be included
> in the select list.
>
> Exactly how ORACLE handles each type of refresh can be found out by
> doing some basic SQL tracing. It is quite complex. But basically
> ORACLE needs to join the MV log back to the mater table to find out
> correct join data before inserting the new data into the MV.
>
>
> - Jusung Yang
Received on Sun Jul 27 2003 - 16:40:27 CDT
