Oracle of dating
A materialized view log (snapshot log) is a schema object that records changes to a master table's data so that a materialized view defined on that master table can be refreshed incrementally.The frequency of this refresh can be configured to run on-demand or at regular time intervals.
Since the introduction in 8i, Oracle has consistently enhanced the technology for each subsequent release.The speed of a fast refresh will be determined by how much data has changed since the last refresh.If the master table's data is updated very often, then the log table will have more recorded changes to process in order to update the materialized view.After determining that the problem was definitely not on the consumer side, the focus of the analysis shifted to the master site.We had already confirmed that the snapshot logs were present so the next step was to see what was contained in those logs.The master table of the materialized view was a remote table in the same data center.
The user process inserted a row into the master table and then refreshed the materialized view.
Then, I checked the query of the materialized view and confirmed that it was a simple select from the master table without any aggregation or sorting.
To be prudent, DBMS_MVIEW.explain_mview was run on the materialized view. Furthermore, the master table had approximately 10-30 changes per minute while the refresh was happening 1-2 times per minute.
A materialized view that is verified to be fast refresh should update relatively fast.
But what happens when there are few changes to the master table, no network issues, no aggregation in the snapshot query and the refresh still runs slow?
The data in a materialized view is updated by either a complete or incremental refresh.