 |
The Largest Online Resource for Oracle Articles, Oracle Tips, Oracle Scripts & Oracle Tools!!
|
|
Author: Don Burleson A materialized view is a piece of replicated data that must be kept synchronized with the master tables. Materialized Views are a wonderful tool for reducing repetitive I/O. They prevent unnecessary repeating large-table full-table scans, as summaries are computed, over and over. Tuning materialized views is very challenging. In this article I will give some useful tips for tuning the materialized views. Materialized View Refreshes:Materialized view refreshes are a one-size-fits-all solution and a customized solution may run many times faster. However materialized view refreshes 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. Problem with Materialized View Fast Refresh:The problem is that a 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). Due to this you can't get partition pruning on the materialized view data. You have an otherwise-useless large composite index on the materialized view. You generally get a nested loop join instead of a nice hash join and sometimes you end up merging when an insert would be sufficient.Below are some tips to avoid such problems Creating Materialized Views:Create the materialized view on a pre-built table Dropping Materialized Views:Drop the materialized view at refresh time Loading Materialized Views:Use your own merge or insert statement to load the materialized view Recreating Materialized Views:Recreate the materialized view. DBMS advanced rewrite package:In Oracle 10g you don't need the materialized view at all. Rather you use the DBMS advanced rewrite package instead. This is a lot more manageable.Rolling up Materialized Views:Roll up to multiple levels of materialized view data using a HOLAP query and then push the aggregated results into the summary tables. Inserting Materialized Views:Look for the scenarios where you can insert into the materialized view without needing the merge. Leveraging for Materialized Views:Leverage some level of aggregation for materialized view1 in order to reduce the resources required to produce materialized view2 etc
More Oracle Articles, Database Articles and DBA Tips 
|
|||
The name Oracle is a trademark of Oracle Corporation. Any other names used on this website may be trademarks of their respective owners