Re: Update on snapshot target overwritten after fast refresh

The "lazy" answer is that this is how it supposed to be. What you have is a "writeable" materialized view, not "updateable" materialized view. Whatever changes you make to the writeable MVs will be lost at the next refresh. ORACLE appears to make the effort of finding out what changes you made to the MV and systematically remove them during a MV "fast" refresh - this is done by looking up the USLOG$ table.

Now, to the question, why would ORACLE not allow you to keep the changes you made to the MV, even though you "fast", or incrementally, refresh the MV. I think "conflict resolution" consideration might be the reason. Say, you change the same row on the master table and the MV, which version would you keep during the MV refresh? As long as the MV is writeable, not updateable, it might be easier to just keep the version from the master table. And as long as some of the changes is removed from the MV due to conflict with the master table, one might as well remove all the insert and delete changes that were made against the MV. This way you don't have to get into all the good stuff about conflict resolution.

• Jusung Yang

bchorng_at_yahoo.com (Bass Chorng) wrote in message news:<bd9a9a76.0211061640.773db99d_at_posting.google.com>...
> I created master/slave snapshot (9.2.0) with the slave side updateable.
> This is not a master to master configuration as I do not want the
> change on slave side reflected back to the master.
>
> I tried to create both the snapshot log and snapshot with primary key,
> rowid or no option at all, but in any of the above cases, my changes on the
> slave site will be gone after a fast refresh.
>
> It looks like as if the refresh were complete, although I did specify
> "fast" in the creation.
>
> On master I did this:
>
> create snapshot log on push_tbl;
>
> On the slave I did this:
>
> create snapshot push_tbl refresh fast start with sysdate
> next sysdate+(1/288) using rollback segment big for update
> as select * from push_tbl_at_se80;
>
> Does anybody know why it behaves this way ? Thanks.
Received on Fri Nov 08 2002 - 03:08:51 CST