Skip to Main Content
  • Questions
  • Fast Refresh on Materialized View seems not working with OUTER JOIN in some conditions

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Giuseppe.

Asked: May 04, 2017 - 12:34 pm UTC

Last updated: May 04, 2017 - 3:08 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

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

and Chris said...

Exactly which version of 11g are you on?

All seems to be in order for me using 11.2.0.4:

-- 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 (+);

SELECT * FROM TABLE_MV;

ID_FATHER  TEXT_FATHER  ID_CHILD  TEXT_CHILD  FATHER_ROWID        CHILD_ROWID         
1          father1      1         child1      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAA  
1          father1      2         child2      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAB

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

ID_FATHER  TEXT_FATHER  ID_CHILD  TEXT_CHILD  FATHER_ROWID        CHILD_ROWID         
1          father1      1         child1      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAA  
1          father1      2         child2      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAB  
1          father1      3         child3      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAC

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

ID_FATHER  TEXT_FATHER  ID_CHILD  TEXT_CHILD  FATHER_ROWID        CHILD_ROWID         
1          father1      1         child1      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAA  
1          father1      2         child2      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAB  
1          father1      3         child33     AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAC

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

ID_FATHER  TEXT_FATHER  ID_CHILD  TEXT_CHILD  FATHER_ROWID        CHILD_ROWID         
1          father1      1         child1      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAA  
1          father1      2         child2      AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAB  
1          father1      3         child33     AAAcBhAABAAAS3JAAA  AAAcBjAABAAAS3NAAC  
2          father2                            AAAcBhAABAAAS3JAAB

select staleness from user_mviews
where  mview_name = 'TABLE_MV';

STALENESS  
FRESH   


Is this answer out of date? If it is, please let us know via a Comment