4

problem is simple. I create TableParent and TableChild linked by foreign key. I create Materialized View Log for both. I create Materialized View with fast refresh as left join from TableParent 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 parent record, Materialized View is NOT refreshed

This is the code

-- Tables
CREATE TABLE TABLE_PARENT (
  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_PARENT NUMBER(10, 0),
  CONSTRAINT PK__TABLE2 PRIMARY KEY (ID),
  CONSTRAINT FK_TABLE_PARENT FOREIGN KEY (ID_PARENT)
  REFERENCES TABLE_PARENT (ID)
);

-- Some record before materialized view creation
INSERT INTO TABLE_PARENT(ID, TEXT) VALUES(1, 'parent1');
INSERT INTO TABLE_CHILD(ID, TEXT, ID_PARENT) VALUES(1, 'child1', 1);
INSERT INTO TABLE_CHILD(ID, TEXT, ID_PARENT) VALUES(2, 'child2', 1);

-- Logs
CREATE MATERIALIZED VIEW LOG on TABLE_PARENT 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_PARENT.ID ID_PARENT,
       TABLE_PARENT.TEXT TEXT_PARENT,
       TABLE_CHILD.ID ID_CHILD,
       TABLE_CHILD.TEXT TEXT_CHILD,
       TABLE_PARENT.ROWID PARENT_ROWID,
       TABLE_CHILD.ROWID CHILD_ROWID
  FROM TABLE_PARENT,
       TABLE_CHILD
  WHERE TABLE_PARENT.ID = TABLE_CHILD.ID_PARENT (+);

At this point you can verify, first result

INSERT INTO TABLE_CHILD(ID, TEXT, ID_PARENT) 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_PARENT(ID, TEXT) VALUES(2, 'parent2');
COMMIT;
SELECT * FROM TABLE_MV;

As you can see, Materialized View is not refreshed in the last case. I can guess what could be the problem, but i want to see your explanations first. I hope i'm doing something wrong and it's not a kind of MView limitation problem. I'm working on Oracle 11g Release 11.2.0.1.0.

Thanks much for your help

kinghomer
  • 3,021
  • 2
  • 33
  • 56

1 Answers1

3

According to My Oracle Support - I found Bug 8856349: Fast refresh of OUTER join materialized view does not work

Bug confirmed in 11.2.0.1, fixed in 11.2.0.2 base release.

The MOS note says that the workaround is to:

Set the parameter "_mv_refresh_pkfk_relationship_opt"=false.

You can change this parameter via the following:

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

However, as this is a hidden parameter, I would either consult Oracle Support (if you have access) as to any potential side effects of setting this parameter, or thoroughly test materialized view refreshes on a test system first before rolling this out to a production system.

Kris Johnston
  • 718
  • 5
  • 15
  • I found it! Value is TRUE. Is it possible to modify, just for try, this hidden parameter via pl/sql ? Do you know which query i have to execute? Thanks much for your help. Unfortunally i've no access to MOS – kinghomer May 04 '17 at 14:02
  • @kinghomer I've edited the answer with how to change init parameters (and this one in particular). – Kris Johnston May 04 '17 at 14:10
  • Ok. Modifying parameter, refresh fast with outer join works well now. Thanks much – kinghomer May 04 '17 at 14:35