Sample Header Ad - 728x90

Materialized view log not updating

1 vote
1 answer
2595 views
Under what conditions would a materialized view log not update? I must be missing something. I've got a 10gR2 database. I can create the following MV logs: CREATE MATERIALIZED VIEW LOG ON linked_t1 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON t1 WITH SEQUENCE, ROWID, PRIMARY KEY INCLUDING NEW VALUES; Then if I do an insert: INSERT INTO linked_t1 (id, link_id, link_analysis_id) VALUES ('11111111','22222222','0000000001'); I see my insert in the MV Log: SQL> select * from MLOG$_LINKED_T1 t; LINK_ID M_ROW$$ SEQUENCE$$ SNAPTIME$$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ -------- ------------------ ---------- ----------- --------- --------- --------------- 11111111 AAHvaaAAHAABvDsACR 11 1/1/4000 I N FE Not really sure what to make of the 1/1/4000 snaptime. As soon as I create a materialized view: CREATE MATERIALIZED VIEW mv$sub2 (c1, c2, m1, m2, m3, m4, m5) USING INDEX TABLESPACE idx REFRESH FAST WITH ROWID ON COMMIT DISABLE QUERY REWRITE AS SELECT la.rowid c1, ar.rowid c2, ar.analysis_id m1, ar.id m2, ar.valid m3, la.analysis_id m4, la.id m5 FROM linked_t1 la, t1 ar WHERE ar.analysis_id = la.analysis_id; The materialized view log on LINKED_T1 is emptied, and will no longer track any DML! Why is that? I have not made any changes to T1 for this test. I should mention that this MV was suggested by DBMS_ADVISOR.TUNE_MVIEW.
Asked by DCookie (572 rep)
Mar 22, 2013, 05:07 PM
Last activity: Mar 22, 2013, 07:26 PM