Fast Refresh on Materialized View seems not working with OUTER JOIN in some conditions

Hi guys,

it’s a pleasure to write here for the first time. I’ll try to explain my problem that seems simple.

I create 2 tables, TableFather and TableChild, linked by foreign key.

I create Materialized View Log for both.

I create Materialized View with fast refresh as left join from TableFather to TableChild.

Result is

1. Adding a child record, Materialized View is refreshed

2. Modifying a child field, Materialized View is refreshed

3. Adding a father record, Materialized View is NOT refreshed

This is the code

 -- Tables CREATE TABLE TABLE_FATHER ( ID NUMBER(10, 0), TEXT NVARCHAR2(50), CONSTRAINT PK__TABLE1 PRIMARY KEY (ID) ); CREATE TABLE TABLE_CHILD ( ID NUMBER(10, 0), TEXT NVARCHAR2(50), ID_FATHER NUMBER(10, 0), CONSTRAINT PK__TABLE2 PRIMARY KEY (ID), CONSTRAINT FK_TABLE_FATHER FOREIGN KEY (ID_FATHER) REFERENCES TABLE_FATHER (ID) ); -- Some record before materialized view creation INSERT INTO TABLE_FATHER(ID, TEXT) VALUES(1, 'father1'); INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(1, 'child1', 1); INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(2, 'child2', 1); -- Logs CREATE MATERIALIZED VIEW LOG on TABLE_FATHER WITH PRIMARY KEY, ROWID; CREATE MATERIALIZED VIEW LOG on TABLE_CHILD WITH PRIMARY KEY, ROWID; -- Materialized View CREATE MATERIALIZED VIEW TABLE_MV REFRESH FAST ON COMMIT AS SELECT TABLE_FATHER.ID ID_FATHER, TABLE_FATHER.TEXT TEXT_FATHER, TABLE_CHILD.ID ID_CHILD, TABLE_CHILD.TEXT TEXT_CHILD, TABLE_FATHER.ROWID FATHER_ROWID, TABLE_CHILD.ROWID CHILD_ROWID FROM TABLE_FATHER, TABLE_CHILD WHERE TABLE_FATHER.ID = TABLE_CHILD.ID_FATHER (+); 

At this point you can verify, first result

 INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(3, 'child3', 1); COMMIT; SELECT * FROM TABLE_MV; 

Then second result

UPDATE TABLE_CHILD SET TEXT = 'child33' WHERE ID = 3; COMMIT; SELECT * FROM TABLE_MV;

Then third result

 INSERT INTO TABLE_FATHER(ID, TEXT) VALUES(2, 'father2'); COMMIT; SELECT * FROM TABLE_MV; 

As you will see (it’s what happens to me), Materialized View is not refreshed in the last case.

I would like to know what i’m doing wrong.

Thank you for your help

Related:

Leave a Reply