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.

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