Materialized Views Tuning

Oracle Database Tips by Donald Burleson

For specific performance issues when refreshing materialized views, see my important notes on tuning materialized view refreshes.

Materialized Views are a wonderful tool for reducing repetitive I/O and they are a true silver bullet under certain circumstances. See my notes on tuning materialized views in Oracle Warehouse Builder OWB.

Without materialized views you may see unnecessary repeating large-table full-table scans, as summaries are computed, over and over:

A materialized view is a piece of replicated data (just like a snapshot) and we must keep it synchronized with the master tables (depending on our tolerance for staleness). As such, tuning materialized views is very challenging.

Mike Ault uses Oracle materialized views and partitioning for materialized view tuning, and others suggest a more radical materialized view tuning approach.

Blogger David Aldridge notes that materialized view refreshes are a one-size-fits-all solution and that a customized solution may run many times faster:

I just reduced my client's refresh time on 7 materialized view's of a major fact table from two hours to 6 minutes by abandoning oracle's refresh code in favour of my own.

The thing about materialized view refreshes is that they are pretty much inefficient all round, being a "one-size-fits-all" solution. The partition change tracking-based fast refresh may not be as inefficient as other methods, particularly when the materialized view and the master table have exactly the same partitioning scheme, but if you ever get a refresh failure then you can't use PCTFR again on that materialized view until you have performed a complete refresh on it.

The problem is that an materialized view "fast" refresh uses a merge statement and joins the aggregated change data to the materialized view on a function of the join columns (sys_op_map_nonnull) so ...

i) you can't get partition pruning on the materialized view data.

ii) you have an otherwise-useless large composite index on the materialized view.

iii) you generally get a nested loop join instead of a nice hash join.

iv) you sometimes end up merging when an insert would be sufficient.

Here's what I've done myself in these situations ...

i) create the materialized view on a prebuilt table
ii) drop the materialized view at refresh time
iii) use my own merge or insert statement to load the materialized view
iv) recreate the materialized view

In 10g you don't need the materialized view at all, you use the DBMS advanced rewrite package instead. This is a lot more manageable.

You can also use "tricks" like ...

i) rolling up to multiple levels of materialized view data using a HOLAP query and then pushing the aggregated results into the summary tables.

ii) Looking for scenarios where you can insert into the materialized view without needing the merge.

iii) Leveraging some level of aggregation for materialized view1 to reduce the resources required to produce materialized view2 etc

��