Can an index be created on MLOG$ (SNAPSHOT LOG)?
You have noticed that your fast refreshes are becoming slower and slower and
less efficient as time goes on. You want to add an index on the MLOG$ snapshot
log table to speed up the refreshes and to prevent a Full Table Scan on the
First of all, the answer is NOT to add an index on the snapshot log table.
Oracle has to do a full table scan on the snapshot log so that it can read all
the rows that it needs to refresh for a particular snapshot. Besides, all SQL
statements generated by the refresh operation is hardcoded in the kernel.
What may be causing the performance degradation is that your snapshot log's
High Water Mark (HWM) may be wastefully high. The snapshot log table grows at
peak times, but never shrinks once the refresh is done. Therefore, during a
fast refresh of the snapshot, Oracle will read the snapshot log using full
table scan all the way to the HWM.
The answer to speeding up the performance of the snapshot refresh is to reset
the HWM. The only way to do this is to truncate the snapshot log or recreate
Once your snapshot log is purged (meaning all snapshots have already refreshed
against that master table), then issue a truncate on it.
i.e. SQL> truncate table mlog$_EMP;
This will reset the HWM without affecting any of your snapshot's fast refreshes.
If you choose to RECREATE your snapshot log, you will have to follow up by
performing a COMPLETE refresh on all the affected snapshots.