Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









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





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.