Master Note for Materialized View (MVIEW) [ID 1353040.1]

Applies to:

Oracle Server – Enterprise Edition – Version and later
Information in this document applies to any platform.


This article is intended to give you a single reference point and to assist in quickly finding various information about materialized view


The document will cover the following topics:

How-To Section
Troubleshooting Section
Known issues
Additional Resources


Concepts/Definitions/Configuration/Set-up Section

Materialized views are query results that have been stored or “materialized” in advance as schema objects. The FROM clause of the query can name tables, views, and materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term).

Materialized views are used to summarize, compute, replicate, and distribute data.
In a replication environment, a materialized view shares data with a table in a different database, called a master database. The table associated with the materialized view at the master site is the master table.

Characteristics of Materialized Views

Materialized views share some characteristics of non-materialized views and indexes. Materialized views are similar to indexes in the following ways:
They contain actual data and consume storage space.
They can be refreshed when the data in their master tables changes.
They can improve performance of SQL execution when used for query rewrite operations.
Their existence is transparent to SQL applications and users.

Document 106024.1 Using Materialized Views
Document 258227.1 Overview of the types of MVIEW available
Document 889342.1 REFRESHING MATERIALIZED VIEW – An Overview
Document 746869.1 Overview of Query Rewrite(ENABLE QUERY REWRITE,Example and Use)
Document 254593.1 Differences Between Rowid & Primary Key Materialized Views
Document 1344554.1Interoperability / Compatibility / Compatible Database Versions in Distributed Materialized View Environment
Document 207303.1 Client / Server / Interoperability Support Between Different Oracle Versions

Relevant Links:

Materialized View Concepts and Architecture
Overview of Materialized View Management Tasks
Managing Materialized View Logs

How-To Section

Document 791205.1 How to create a simple test scenario for Materialized Views involving remote tables
Document 729187.1 Explain Materialized View Refresh Types With Scripts
Document 258021.1 How to monitor the progress of a materialized view refresh (MVIEW)
Document 730908.1 How to use DBMS_MVIEW.EXPLAIN_MVIEW to check for Fast refresh options.
Document 806833.1 How to Cancel a Refresh Operation of a Materialized View (MV)
Document 341842.1 How to kill a hung automated materialized view refresh job
Document 577870.1 How to Refresh a Materialized View in Parallel
Document 149815.1 How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite Problems
Document 334878.1 Can Redo Generation Be Disabled During Materialized View Refresh
Document 800833.1 How to Refresh MView in Remote Database from Master Database
Document 731707.1 How to Create and Refresh a Rowid MView
Document 149112.1 Materialized View Sub-Query Enhancements

Troubleshooting Section

Document 1087507.1Master Note for MVIEW ‘ORA-‘ error diagnosis. For Materialized View CREATE or REFRESH
Document 122039.1 Troubleshooting Basics for Advanced Replication
Document 1035874.6 Troubleshooting Guide: Advanced/Multimaster Replication Propagation (Doc ID )
Document 746771.1 Useful Notes for Materialized View Progress and Performance Issues

Document 1072040.1 Invalid Materialized View
Document 454552.1 What Causes So Many Invalid Materialized Views
Document 1118914.1 Create Materilized View, or Complete Refresh takes a long time across a dblink
Document 956462.1 Tuning Materialized Views with Subqueries and/or Inline Views
Document 245635.1 Using Execution Plan to Verify Materialized View Query Rewrite
Document 752541.1 Checklist for ORA-600 [16606] or ORA-7445 [qcdlgcd()] When Performing A Materialized View Refresh
Document 204127.1 Diagnosing ORA-12034 Materialized View Log Younger Than Last Refresh
Document 733054.1 Fast Refresh of Mview Taking 20 Min. Due to Hints & Full Table Scans of 20gb Table
Document 179466.1 Diagnosing ORA-12015 fast refresh materialized view / complex queries
Document 759428.1 Materialized View Refresh Over Dblink Hangs After Firewall Upgrade
Document 1313218.1 How to Troubleshoot Slow CREATE MATERIALIZED VIEW Issues
Document 1231613.1 Materialized View Privileges Needed for Local & Remote Databases to Prevent ORA-1031 & ORA-942

Performance Issues with MVIEW:

Document 412400.1 General Materialized View Refresh Performance Tuning Tips
Document 1313291.1 How to Troubleshoot Slow Materialized View Complete Refresh Issues
Document 258252.1 MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring
Document 236233.1 Materialized View Refresh : Log Population and Purge
Document 553464.1 Complete Refresh Read Consistency Behavior During Refresh and Complete Refresh Performance as Influenced by the ATOMIC_REFRESH Refresh Parameter
Document 578720.1 Slow On Commit Refresh Of Materialized View With Non-Unique Outerjoins
Document 727215.1 Materialized View Refresh Takes Long Time & High Cpu Usage To Complete
Document 241945.1 Fast Refresh On Materialized Views Too Slow Compared To Complete Refresh — delete
Document 1063953.1 Refresh Collection Snapshots Performance – Managing MLOG$ Tables and Snapshots for Data Collections
Document 1313291.1 How to Troubleshoot Slow Materialized View Complete Refresh Issues

Relevant Links:

Diagnosing Problems with Materialized Views
Managing MLOG$ Tables

Source Metalink Support


One thought on “Master Note for Materialized View (MVIEW) [ID 1353040.1]

  1. Because it is simpler than to run a shop in the market to run a personal on-line shop that to your shop which offers you every thing
    under the sun that are available online retailers give year long discounts.
    Also these online stores like provide particular discount such as overstock
    promotion code for promotional and publicity purpose. Though a
    traditional Overstock voucher is hard to find but after you
    acquire it be sure to be dazzled.

    Overstock Coupon is of very good use, since it has increased the
    trend of on the web buying by many times among your
    brain of people. It’s also many obtaining things which increase its need. Various kinds coupon limitations are there Free Ship Coupon Code, namely Money Saver Coupon Code and many more; all are used for different purposes.
    Today these requirements are being sold out to complete business. This is even comfortable for that clients as they are now free from the risk of marketing using a huge amount of money. Even some recharge internet sites and web sites have started the Overstock Coupon program. They provide schemes like on a charge of Rs.10 just one can get 200-500 concession on shopping for Rs 999 and above. By utilizing these plans, also the sites are extending their sales. A margin has been fixed by many shopping sites such that shopping beyond that certain amount could bear no shipping cost. Coupon codes are now available even to reduce the shipping charge.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s