Question

Using v11.0.1

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

Justin

Answer 1

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.
2) Basically, when the rows in the MV are updated, all rows related to the creation of that row are locked
ie. you could have multiple rows from multiple different tables locked in order to update that single MV row. This can have severe negative effects on concurrency if you are not careful.

It sounds like in this case, since the write volume is relatively low, immediate update may help. Have you tried the index consultant/application profiler to see if any changes can be made to improve your original query? You could also try re-writing the query against the base tables rather than views you have created.

Answer 2

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.

Answer 3

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.