This content has been marked as final. Show 5 replies
1) Once all registered materialized views have read a particular row from a materialized view log, it is removed, yes. If there are multiple materialized views that depend on the same log, they would all need to refresh before it would be safe to remove the MV log entry. If one of the materialized views does a non-incremental refresh, there may be cases where the log doesn't get purged automatically.
2) No, your query wouldn't cause anything to be purged (though you wouldn't see anything interesting unless you happen to implement lots of code to parse the change vectors stored in the log). I don't know that the exact mechanism that Oracle uses has been published, though you could go through and trace a session to get an idea of the moving pieces. From a practical standpoint, you just need to know that when you create a fast-refreshable materialized view, it's going to register itself as being interested in particular MV logs.
3) It would depend on what is stored in the MV log. The refresh process may need to grab particular columns from the table if your log is just storing the fact that data for a particular key changed. You can specify when you create a materialized view log that you want to store particular columns or to include new values (with the INCLUDING NEW VALUES) clause. That may be beneficial (or necessary) to the fast refresh process but it would tend to increase the storage space for the materialized view log and to increase the cost of maintianing the materialized view log.
4) Sequential reads against a table are perfectly normal-- it just implies that someone is looking at a particular block in the table (i.e. looking up a row in the table by ROWID based on the ROWID in an index or in a materialized view log).
Thanks Justin for the clear explanation. Last time when the MV refresh hung, I terminated the process, dropped and recreated the MV again. Now when I refresh the MV again , would the MV know that it does not have to consider the old change vectors in the MLOG table? When would the old change vectors from the MLOG table get purged in this scenario?
The materialized view knows that it doesn't need older log entries, yes.
It is theoretically possible that the materialized view would not know that the dropped materialized view was really gone (generally, this only happens when you're using materialized views for replication and the link between systems goes down when you drop and recreate and even then it's an error) in which case you could use the dbms_mview.purge_log or dbms_mview.purge_mview_from_log procedure to purge the old entries if you see a stray entry in DBA_REGISTERED_MVIEWS.
One more question...When we do a fast refresh, Oracle reads the change vectors from the MLOG and applies the changes to the MV...Seems like Oracle does a commit only after applying all the changes This was apparent because whenever I did a count(*) from the MV, the count never increased while the MV refresh was in progress. Only after the MV refresh comepleted I noticed an increase in the record count. Is there a way to track the progress of a fast MV refresh? Currently I have a master table whose MLOG has 300K records (mostly "insert" change vectors) in database A. The MV is in database B. The MV fast refreshes over the database link. The refresh has been running for about 2 hours now but I don't see an increase in the MV record count. I see from the session_wait_history and ASH that the MV refresh is progressing as the wait events keep changing but I'm unable to detremine how many MLOG records it has processed so far and how may records are left...Any ideas?
Thanks for your time...