Materialized View

Everytime you use a view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in, unlike a view it contains space and storage just like a regular table. You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it. Creating materialized views is simple but optimizing it can be tricky, keeping the data up to date and also getting the CBO (cost based optimizer) to use the view. As with view materialized views can be inserted, updated and deleted from.

There are 3 types of materialized views:

Readonly Materialized view	Cannot be updated and complex materialized views are supported
Updateable Materialized view	can be updated even when disconnected from the master site, are refreshed on demand and consume fewer resources but requires advanced replication option to be installed
Writeable Materialized view	are created with the for update clause, any changes are lost when the view is updated this also requiresadvanced replication option to be installed.

When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. In the case of ON COMMIT, the materialized view is changed every time a transaction commits, thus ensuring that the materialized view always contains the latest data. Alternatively, you can control the time when refresh of the materialized views occurs by specifying ON DEMAND. In this case, the materialized view can only be refreshed by calling one of the procedures in the DBMS_MVIEW package.

DBMS_MVIEW provides three different types of refresh operations:
DBMS_MVIEW.REFRESH ------Refresh one or more materialized views.
DBMS_MVIEW.REFRESH_ALL_MVIEWS -----Refresh all materialized views.
DBMS_MVIEW.REFRESH_DEPENDENT ----Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views. 

Materialized View Refresh

To ensure that a materialized view is consistent with its master table or master materialized view, you must refresh the materialized view periodically. Oracle provides the following three methods to refresh materialized views:

    ■ Fast refresh uses materialized view logs to update only the rows that have changed since the last refresh.

    ■ Complete refresh updates the entire materialized view.

    ■ Force refresh performs a fast refresh when possible. When a fast refresh is not possible,force refresh performs a complete refresh.

1. INTRODUCTION

===============

A materialized view is a replica of a target master from a single point in time.

The concept was first introduced with Oracle7 termed as SNAPSHOT.

NOTE :   Materialized views can be used for many purposes, including:

·         Denormalization

·         Validation

·         Data Warehousing

·         Replication.

2. Usage of Materialized Views

=======================

Materialized views can be used both for:

   - creating summaries to be utilized in data warehouse environments

   - replicating data in distributed environments

3. Refreshing Materialized Views

==========================

Initially, a materialized view contains the same data as in the master table.

After the materialized view is created, changes can be made to the master table, and possibly also to the materialized view. To keep a materialized view's data relatively current with the data in the master table, the materialized view must be periodically refreshed. Refresh can be accomplished by one of the following procedures

   dbms_mview.refresh( '<mview list>', '<Refresh Type>' )

   dbms_refresh.refresh( '<refresh group>' )

You can choose between Complete, Fast, and Force refresh types.

4. Materialized View Types

======================

Read-Only Materialized Views

----------------------------------------

As the name implies it is not possible to perform DML on snapshots in this category.

Updatable Materialized Views

----------------------------------------

Updatable materialized views eliminate the restriction of DMLs on snapshots. Users are allowed to insert, update and delete rows of the updatable materialized view.

Subquery Materialized Views

---------------------------------------

Materialized views that are created with subqueries in the WHERE clause of the mview query are referred to as subquery materialized views.

Multitier Materialized Views

----------------------------

A multitier materialized view is a materialized view whose master table is itself a materialized view. This feature enables fast refresh of materialized views that have materialized views as their masters.

5. Data type Support

================

The following datatypes are supported in snapshot replication:

 - VARCHAR2

 - NVARCHAR2

 - NUMBER

 - DATE

 - TIMESTAMP

 - TIMESTAMP WITH TIME ZONE

 - TIMESTAMP LOCAL TIME ZONE

 - INTERVAL YEAR TO MONTH

 - INTERVAL DAY TO SECOND

 - RAW

 - ROWID

 - CHAR

 - NCHAR

 - User-defined data types

 - Binary LOB (BLOB)

 - Character LOB (CLOB)

 - National character LOB (NCLOB)

 - UROWID (supported only for readonly materialized views)

The following types are NOT supported in snapshot replication:

 - LONG

 - LONG RAW

 - BFILE

 - UROWID (not supported for updatable snapshots)

NOTE :   3 distinct types of users perform operations on materialized views:

                Creator: The user who creates the materialized view.

                Refresher: The user who refreshes the materialized view.

                Owner: The user who owns the materialized view. The materialized view resides in this user's schema

Read more:  Master Note for Materialized View (MVIEW) (Doc ID 1353040.1)

                       Overview of Refreshing Materialized Views (Doc ID 549874.1)