Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.