My Oracle Support Banner

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

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
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.