Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

August 27, 2007

Materialized Views – Summary Management with Oracle

Filed under: Oracle Business Intelligence — H.Tonguç Yılmaz @ 4:48 am

In this blog’s Oracle Business Intelligence category I made introduction before to both Parallel Execution and Partitioning two epical and maybe most used two options of Oracle on BIS environments.

Using Materialized Views is a critical performance argument for data warehousing – decision support – reporting needs, they are also widely used to replicate and distribute data. By calculating the answers to the really hard questions and also query rewrite feature, we greatly reduce the load on the system and these pre-computed summaries and would typically be very small compared to the original source data. Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables –

Code Listing 121 – Materialized View Basics Demo

A materialized view can be partitioned, indexed, large materialized views in can be created in parallel and you may use Materialized View Logs for incremental refreshes. Also they are very similar to indexes in several ways –
* They consume storage space,
* They must be refreshed when the data in their master tables changes,
* They improve the performance of SQL execution when they are used for query rewrites,
* Their existence is transparent to SQL applications and users,
* With write operations they have performance impact.
Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.

Managing Materialized Views after 10g is much more easier, if you are interested with dbms_advisor.tune_mview – dbms_advisor.create_file – select /*+ REWRITE_OR_ERROR */ .. stuff please check this Arup Nanda‘s article; Oracle Database 10g: The Top 20 Features for DBAs by Arup Nanda – Week 12 – Materialized Views

Also this week in the group oracle-l, which I mentioned earlier in one of my posts, “Materialized View Refresh method has been changed from 9i to 10g” was a hot topic. As a summary; prior to 9i for complete refreshes Oracle did truncate mv and insert /*+ append */ where as after 10g it does delete and normal insert causing performance overhead. This was also discussed on this AskTom thread. As Jared Still mentioned on one of his follow ups, by changing the refresh method to set atomic_refresh = false, the truncate/append behavior can be restored;

— dbms_mview.refresh(‘MVTEST_MV’,method => ‘C’);
— use this with 10g to return to truncate/append behavior
dbms_mview.refresh(‘MVTEST_MV’,method => ‘C’, atomic_refresh=>false);

ps: to whom may be interested, as I promised earlier I published my two days Optimizing for Performance seminar by Joze Senegacnik seminar notes.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release

Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – DBMS_ADVISOR.TUNE_MVIEW Procedure


  1. If you are having invalid materialized view problems check Metalink Note 264036.1: “This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview. However, the query on MV will not return latest update done in master table unless MV is refreshed.”

    Reference – http://forums.oracle.com/forums/thread.jspa?threadID=569700 feed by Yas

    Comment by H.Tonguç Yılmaz — October 5, 2007 @ 7:05 am | Reply

  2. Materialized Views by Dizwell –

    Comment by H.Tonguç Yılmaz — December 13, 2007 @ 5:49 pm | Reply

  3. to use REWRITE_OR_ERROR hint at session level there is an undocumented parameter in 10g;

    ALTER SESSION SET “_query_rewrite_or_error” = TRUE;

    Comment by H.Tonguç Yılmaz — July 10, 2008 @ 5:22 am | Reply

  4. Investigating why my materialized view does not rewrite?

    statement_id VARCHAR2(30), — id for the query
    mv_owner VARCHAR2(30), — owner of the MV
    mv_name VARCHAR2(30), — name of the MV
    sequence INTEGER, — sequence no of the msg
    query VARCHAR2(2000), — user query
    query_block_no INTEGER, — block no of the current subquery
    rewritten_txt VARCHAR2(2000), — rewritten query
    message VARCHAR2(512), — EXPLAIN_REWRITE msg
    pass VARCHAR2(3), — rewrite pass no
    mv_in_msg VARCHAR2(30), — MV in current message
    measure_in_msg VARCHAR2(30), — Measure in current message
    join_back_tbl VARCHAR2(30), — Join back table in message
    join_back_col VARCHAR2(30), — Join back column in message
    original_cost INTEGER, — Cost of original query
    rewritten_cost INTEGER, — Cost of rewritten query
    flags INTEGER, — associated flags
    reserved1 INTEGER, — currently not used
    reerved2 VARCHAR2(10)) — currently not used

    dbms_mview.explain_rewrite(‘select .. ‘,

    select * from rewrite_table ;

    Comment by H.Tonguç Yılmaz — July 12, 2008 @ 8:12 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: