Materialized views the good, bad and the ugly.

[roadwarriorDBA]

General question on mviews. I started my new job 2.5 miles from my home yesterday. I am in the hot seat to recommend the most suitable features of Oracle 10g to various development groups. Its a neat-o job position but I am going to be slammed for the forseeable future.

I am going to clarify the teams understanding of Mviews today but would like to hear some real world application Do's and Dont's if you have them. In past data warehouses we did this all manually so I have only read about Mviews academically. For instance it looks as if REFRESH ON COMMIT might be bad for an OLTP system and would be better off done all at once off hours - business allowing.

Thanks for any and all commentary.

[marist89]

Mviews are great, except when they're not.

[gandolf989]

Mviews are great, except when they're not.

Answers like this are why you are a supper genious!!!

If I had to take a swag at it I would say that they are best for data that does not change often. I haven't worked with them lately, but when I was researching them, I found that normal views worked better for the data and queries that I was working with at the time, and yes I did have query rewrite turned on.

[marist89]

Answers like this are why you are a supper genious!!!

Without more information, there's really no right answer to roadwarriorDBA's question.

If I had to take a swag at it I would say that they are best for data that does not change often. I haven't worked with them lately, but when I was researching them, I found that normal views worked better for the data and queries that I was working with at the time, and yes I did have query rewrite turned on.

depends. If you can use fast refresh, then the rate at which the data changed is less relavent.

[roadwarriorDBA]

This my take on it, some from Jlewis and some from Akadia.

Pre-Joined real table result ? Yes.

"A materialized view creates a real table, and this table may
be indexed, analyzed, and so on."

"Materialized views will increase your need for one resource -
more permanently allocated disk. We need extra storage space
to accommodate the materialized views, of course, but for the
price of a little extra disk space, we can reap a lot of benefit."

The Mviews roots are in snapshots and what has been added is
insight into how to employ Query Rewrite as well as
automatically funneling changes from the base table to the
Mviews or summary table which are real tables occupying space.

Out of control Optimizer ? Not necessarily.

"Sometimes, you know Oracle could use the materialized view,
but it is not able to do so simply because Oracle lacks
important information."

I think we may want to look at QUERY REWRITE INTEGRITY set to
either
- TRUSTED
- STALE TOLERATED

I think we will not want to use REFRESH ON COMMIT as it will
add overhead to modifications performed on the base tables in
order to capture the changes. Mviews with refresh on commit
have a significant overhead at commimt. They also introduce
odd performance side effects if the query rewrite integrity is
enforced because a query against uncommitted data has to visit
the base tables whereas a query against committed visits the
mview. I will look at 'Mview Logs' and see if its just
recording the delta or aggregating the entire MVIEW again.

We could instead force all base table changes to be funneled
to the Mview each nite, liberating the OLTP changes from the
Mview synchronizations.

This is a good read on Mviews -

http://www.akadia.com/services/ora_m...zed_views.html - I
suspect its not current so I will hit the Oracle 10g docs and
see what the features and variations are today.

[mike9]

A nice and usefull feature for refreshing is Partition Change Tracking (PCT). You may have look at it either on the Oralce Doc or on metalink.

[tamilselvan]

Mviews may be bad, may be good. It all depends upon how you use them.
The only way you can figure it is:
Benchmark, Benchmark, Benchmark.

Tamil