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 primary 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 primary database. The table associated with the materialized view at the primary site is the primary table.
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, from a single master site or master materialized view site
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:
1. They contain actual data and consume storage space.
2. They can be refreshed when the data in their primary tables changes.
3. They can improve performance of SQL execution when used for query rewrite operations.
4. Their existence is transparent to SQL applications and users.
Usage of Materialized Views
Materialized views can be used both for:
1. Creating summaries to be utilized in data warehouse environments
2. Replicating data in distributed environments
A typical create MVIEW statement has the following form:
create materialized view snap_test
refresh fast
start with sysdate
next sysdate+1 as
select * from master_table@master_db;
This statement should be executed in snap_db. It will create
* A MVIEW base table called SNAP_TEST which has the same structure as MASTER_TABLE.
* A new object namely SNAP_TEST of type materialized view.
* A UNIQUE index on the PK columns or ROWID depending on the type of MVIEW log at master site
Since this is a fast refresh MVIEW the master table should have a log to record the changes on it that can be created by running.
create materialized view log on master_table;
In master_db. This will create the following objects:
* A table called MLOG$_master_table
* An internal trigger on MASTER_TABLE that populates the log table
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 procedure:
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 datatypes
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)
