ORACLE: quick update on a materialized view that does not work with OUTER JOIN in some conditions

Question

The problem is simple. I create a TableFather and a TableChild linked by a foreign key. I am creating a Materialized View Log for both. I am creating a materialized view with a quick update, like a left join from TableFather to TableChild.

Result

• Adding child record, materialized view updated
• Changing child field, materialized view updated
• Adding father record, materialized view is NOT updated

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 check the first result

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

Then the second result

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

Then the third result

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

, Materialized View . , , . , - , MView. Oracle 11g Release 11.2.0.1.0.

Answer 1

Oracle - 8856349: OUTER .

, 11.2.0.1, ​​ 11.2.0.2.

MOS , :

"_mv_refresh_pkfk_relationship_opt" = false.

:

alter system set "_mv_refresh_pkfk_relationship_opt"=false scope=both;

, , Oracle Support ( ) , , , .