Does anyone have any real world experience of immediately refreshed materialized views versus manually refreshed materialized views?
We have a situation where we think a materialized view could help. Currently a very complex group of views is used as the basis of a selector screen and numerous reports.There are about ten underlying tables and about three outer joins involved. The underlying data is addeded to fairly frequently, but manualy (maybe one or two records per minute on a largish system). The data is rarely edited and never deleted.
In a large system the full result set may be 5-10 million rows. The data is used in a lot of management reports and the optimizer sometimes struggles to come up with efficient plans.
We are going to experiment with rewriting the structure as a single view that can then be materialized (and indexed) and are wondering about the best refresh strategy. We can experiment, but it's hard to do that on a large live system!
Reading the docs, it seems that on an immediately refreshed MZ only changed / new rows are effected, whereas a manual refresh re-runs the whole thing. This seems like a good argument for immediate, but I'm not clear if the immediate refresh will delay the completion of a transaction in progress that involves its underlying data.
For the purposes we have in mind a 5 minute or so delay would be acceptable, but manually updating the view that often (if it really involves dumping and recreating the result set) would be a huge overhead.
Many thanks for any input
I would also consider not trying to utilize a single MV in order to answer this particular query. It may be that breaking the overall query into two or more MVs may be beneficial, in both the sense that lock contention between multiple updaters can be reduced, and that the smaller views can be utilized for other queries.
The drawback of additional MVs are:
a) the need to join two or more views to create the final result.
b) for immediate views, the greater the risk of deadlock when multiple updaters are executing concurrently.
answered 10 Nov '09, 16:07
Are you saying the MV will have 5-10 million rows, or that the MV summarizes 5-10 million rows?
A couple of things to note:
1) When you update the underlying data for a MV, the transaction will be delayed while the MV is updated as well.
answered 09 Nov '09, 19:29
Create an event to do that an create an schedule for it. And if you need to refresh immediately, you just trigger your event.
I have this solution working fine. My MV is an union of about 10 tables, and have about 40M rows.
answered 10 Nov '09, 10:08
I would suggest best refresh strategy is Force method. Because it will search first fast it is not available then it will go complete refresh method.
answered 24 Sep '10, 10:36