My Oracle Support Banner

Wrong Results Using Fast Refresh In Version 11.2, Insert works but Updates get lost. (Doc ID 1174455.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

When fast refreshing a MVIEW with Joins we are seeing that UPDATES to the master tables are not being recognized in the mview after a FAST refresh.  INSERTS are appearing as expected.



Changes

We have the following MView logs defined and the following MVIEW.

CREATE MATERIALIZED VIEW LOG ON T_USR_USER TABLESPACE TBS_MU WITH ROWID, SEQUENCE(Fungrpid,delyes,ScopegrpId);
CREATE MATERIALIZED VIEW LOG ON T_USR_GRPFUNS TABLESPACE TBS_MU WITH ROWID,SEQUENCE(FungrpId,FunctionId);
CREATE MATERIALIZED VIEW LOG ON T_USR_FUNGRP TABLESPACE TBS_MU WITH ROWID,SEQUENCE(FungrpId);
CREATE MATERIALIZED VIEW LOG ON T_USR_FUNCTION TABLESPACE TBS_MU WITH ROWID,SEQUENCE(FunctionId);

CREATE MATERIALIZED VIEW V_USR_P_GRPFUNS
TABLESPACE TBS_CARGO_LOB
REFRESH FAST ON DEMAND
AS
SELECT
a.rowid arowid,
b.rowid browid,
c.rowid crowid,
d.rowid drowid,
d.USERID,
d.USERNAME,
a.FunctionId,
a.FungrpId,
b.Dsc as FUNGRPDSC,
c.FuntypeId,
c.Dsc as FUNCTIONDSC
from T_USR_GRPFUNS a, T_USR_FUNGRP b, T_USR_FUNCTION c, T_USR_USER d
Where a.FungrpId = b.FungrpId and a.FunctionId=c.FunctionId and d.FUNGRPID=b.FUNGRPID and d.DELYES='N';

The only way to get UPDATES from the master tables to the mview is via a COMPLETE refresh.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.