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
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