Master Note for Materialized View (MVIEW) [ID 1353040.1]
Posted by ZyK on 03/29/2013
Applies to:
Oracle Server – Enterprise Edition – Version 10.2.0.2 and later
Information in this document applies to any platform.
Information in this document applies to any platform.
Purpose
This article is intended to give you a single reference point and to assist in quickly finding various information about materialized view
Scope
The document will cover the following topics:
Concepts/Definitions
How-To Section
Troubleshooting Section
Known issues
Additional Resources
How-To Section
Troubleshooting Section
Known issues
Additional Resources
Details
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.
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.
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 549874.1 OVERVIEW OF REFRESHING MATERIALIZED VIEWS
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
Document 258227.1 Overview of the types of MVIEW available
Document 889342.1 REFRESHING MATERIALIZED VIEW – An Overview
Document 549874.1 OVERVIEW OF REFRESHING MATERIALIZED VIEWS
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
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
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 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 726333.1 WHY DOES MVIEW STALENESS in DBA_MVIEWS BECOME UNUSABLE ?
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
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 726333.1 WHY DOES MVIEW STALENESS in DBA_MVIEWS BECOME UNUSABLE ?
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 1031924.6 SNAPSHOT LOGS GROWS DUE TO MISSING/INVALID SNAPSHOT
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
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 1031924.6 SNAPSHOT LOGS GROWS DUE TO MISSING/INVALID SNAPSHOT
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:
Source Metalink Support
No comments:
Post a Comment