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.

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.

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.