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

asked 09 Nov '09, 18:00

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

edited 15 Nov '09, 10:46

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050


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.

permanent link

answered 10 Nov '09, 16:07

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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.

permanent link

answered 09 Nov '09, 19:29

Jason%20Hinsperger's gravatar image

Jason Hinspe...
2.7k63447
accept rate: 35%

Thanks for that - the MV itself will have 5-10 million rows

(10 Nov '09, 13:08) Justin Willey

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.

permanent link

answered 10 Nov '09, 10:08

Zote's gravatar image

Zote
1.7k364051
accept rate: 43%

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.

permanent link

answered 24 Sep '10, 10:36

karanam's gravatar image

karanam
161
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31
×21
×15

question asked: 09 Nov '09, 18:00

question was seen: 4,379 times

last updated: 24 Sep '10, 10:36