Snapshot Overview

Materialized views (previously known as snapshots) are used to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes. When a materialized view is fast refreshed, Oracle must examine all of the changes to the master table or master materialized view since the last refresh to see if any apply to the materialized view. Therefore, if any changes where made to the master since the last refresh, then a materialized view refresh takes some time to apply the changes to the materialized view. If, however, no changes at all were made to the master since the last refresh of a materialized view, then the materialized view refresh should be very quick. A materialized view can be either read-only, updatable, or writeable. Users cannot perform data manipulation language (DML) statements on read-only materialized views, but they can perform DML on updatable and writeable materialized views. Now CREATE MATERIALIZED VIEW is a synonym for CREATE SNAPSHOT

The following is an example of a read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees AS
SELECT * FROM hr.employees@orc1.world;

The following is an example of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
SELECT * FROM hr.departments@orc1.world;

The following statement creates a materialized view group:

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'hr_repg',
master => 'orc1.world',
propagation_mode => 'ASYNCHRONOUS');
END;
/

The following statement adds the hr.departments materialized view to the materialized view group, making the materialized view updatable:

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'hr_repg',
sname => 'hr',
oname => 'departments',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
/

A writeable materialized view is one that is created using the FOR UPDATE clause but is not part of a materialized view group. Users can perform DML operations on a writeable materialized view, but if you refresh the materialized view, then these changes are not pushed back to the master and the changes are lost in the materialized view itself. Writeable materialized views are typically allowed wherever fast-refreshable read-only materialized views are allowed.