Re: How does Oracle Fast Refresh on Materizlied View for Joined Tables?

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