New records not reflected after fast refresh,if materialized view has a self join between the primary and the foreign key columns (Doc ID 1566931.1)

Last updated on JULY 05, 2013

Applies to:

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

Symptoms

New records not reflected after fast refresh,if materialaized view has a self join between the primary and the foreign key columns.

The behaviour can be illustrated with the following test case:


sqlplus "/ as sysdba"

create user test1 identified by test1 default tablespace users;

grant create session to test1;

grant create materialized view to test1;

grant create table to test1;

alter user test1 quota unlimited on users;

connect test1/test1

create table tab1 (col1 number,col2 varchar2(30),col3 number);

alter table tab1 add constraint tab1 primary key (col1);
ALTER TABLE tab1 ADD ( CONSTRAINT FK_PARENT FOREIGN KEY (col3) REFERENCES
tab1(col1) ENABLE VALIDATE);

/* */

CREATE MATERIALIZED VIEW LOG ON tab1
TABLESPACE USERS
WITH PRIMARY KEY, rowid, SEQUENCE;

insert into tab1 values(1,'a',null);
insert into tab1 values(2,'b',1);
insert into tab1 values(3,'c',1);
insert into tab1 values(4,'d',3);

commit;

CREATE MATERIALIZED VIEW tab1_MV (col1,col2,col3,rowid1,rowid2)
TABLESPACE USERS
BUILD DEFERRED
REFRESH FAST ON DEMAND
AS
SELECT t1.col1,t1.col2,t1.col3,t1.rowid,t2.rowid
from tab1 t1,tab1 t2
where t1.col3 = t2.col1(+);

exec dbms_mview.refresh('TAB1_MV','C');

set lines 120
col col1 form 999
col col2 form a10
col col3 form 999

select * from tab1_mv;

insert into tab1 values(5,'e',3);

commit;

PROMPT We have just inserted a 5th row into the table

select * from tab1;

exec dbms_mview.refresh('TAB1_MV','F');

PROMPT Now we've fast refreshed the MV
PROMPT the 5th row should be in the mat view
PROMPT but it isn't

select * from tab1_mv;

exec dbms_mview.refresh('TAB1_MV','C');

PROMPT but if we complete refresh again
PROMPT the extra row is now present in the mat view

select * from tab1_mv;



Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms