Materialized view log not updating

fast-refreshoracleoracle-10g

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.

Best Answer

And the answer is, operator ineptitude :-)

Apparently, when the change is applied to the MV, the log entry gets purged. The update shows up in the MV. The reason I was looking in the log at all is that this MV is used to update another MV, and the change was not showing up there. That view has it's own issues which I will have to work out. Thanks for looking!

Related Question