Introduction to Materialised views

MATERIALISED VIEWS:

Materialized views are schema objects that can be used to summarize, precompute, replicate and distribute data.
In mview, the query result is cached as a concrete table that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of some data being potentially out-of-date. It is most useful in datawarehousing scenarios, where frequent queries of the actual base tables can be extremely expensive.
In previous releases it is known as snapshot to replicate data to non-master sites in a replication environment and to cache expensive queries in a datawarehouse environment.
It is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data.
A materialized view can be stored in the same database as it’s base tables or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a datawarehouse environment.
mview can query tables, views and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a datawarehouse term).

Whenever you create a materialized view, regardless of it’s type, always specify the schema name of the table owner in the query for the materialized view.

PREREQUISITES:

To create mviews, the user should have any one of

CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW privileges.

SQL> GRANT CREATE MATERIALIZED VIEW TO user-name;

And

SQL> GRANT QUERY REWRITE TO user-name;

And following init parameters should be set

query_rewrite_enabled = true (default)

query_rewrite_integrity = enforced|trusted|stale_tolerated

The background process responsible for these materialized view refreshes are the coordinated job queue (CJQ) processes.

job_queue_processes=n

SYNTAX:

CREATE MATERIALIZED VIEW mview-name
[partitioning-options]
[storage-parameters]
[TABLESPACE tablespace-name]
[OF object-type]
[FOR UPDATE]
[BUILD IMMEDIATE|BUILD DEFFERED|ON PREBUILT TABLE]
[REFRESH [FAST|COMPLETE|FORCE|NEVER]
 [ON DEMAND|COMMIT]
 [START WITH date]
 [NEXT date]
 [WITH PRIMARY KEY|ROWID]]
[DISABLE|ENABLE QUERY REWRITE]
AS select-query;

REFRESH TYPES:

Oracle can refresh a materialized view using either a fast, complete or force refresh.

Whether the view is primary key based or row-id based. PRIMARY KEY is the default option.

Time and interval at which the view is to be refreshed.

COMPLETE REFRESH:

To perform COMPLETE refresh of a materialized view, the server that manages the materialized view executes the materialized view’s defining query, which essentially recreates the materialized view. To refresh the materialized view, the result set of the query replaces the existing materialized view data. Oracle can perform a complete refresh for any materialized view. Depending on the amount of data that satisfies the defining query, a complete refresh can take a substantially longer amount of time to perform than a fast refresh.

Note: If a materialized view is complete refreshed, then set it’s PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.

SQL> CREATE MATERIALIZED VIEW mv_emp
 REFRESH COMPLETE
 START WITH SYSDATE NEXT SYSDATE + 1
 WITH PRIMARY KEY
 AS SELECT * FROM emp@remote_db;

To refresh this mview,

SQL> EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C');

FAST REFRESH:

To perform FAST refresh, the master that manages the materialized view first identifies the changes that occurred in the master since the most recent refresh of the materialized view and then applies these changes to the materialized view. Fast refreshes are more efficient than complete refreshes when there are few changes to the master because the participating server and network replicate a smaller amount of data.

We can perform fast refreshes of materialized views only when the master table or master materialized view has a materialized view log. Also, for fast refreshes to be faster than complete refreshes, each join column in the CREATE MATERIALIZED VIEW statement must have an index on it.

SQL> CREATE MATERIALIZED VIEW mv_emp
 BUILD IMMEDIATE
 REFRESH FAST
 START WITH SYSDATE NEXT SYSDATE + 2
 WITH PRIMARY KEY
 ENABLE QUERY REWRITE
 AS SELECT * FROM emp@remote_db;

MATERIALIZED VIEW LOG:

It is a schema object that records changes to a master table’s data so that a materialized view defined on the master table can be refreshed incrementally.

SQL> CREATE MATERIALIZED VIEW LOG ON emp;

FORCE REFRESH:

To perform FORCE refresh of a materialized view, the server that manages the materialized view attempts to perform a fast refresh. If fast refresh is not possible, then Oracle performs complete refresh. Use the force setting when you want a materialized view to refresh if fast refresh is not possible.

If you do not specify a refresh method, FORCE is the default.

SQL> CREATE MATERIALIZED VIEW mv_emp
 REFRESH FORCE
 START WITH SYSDATE NEXT SYSDATE + 3
 WITH PRIMARY KEY
 DISABLE QUERY REWRITE
 AS SELECT * FROM emp@remote_db;

To refresh this mview,

SQL> EXEC DBMS_MVIEW.REFRESH(LIST =>'mv_emp');

Rahul Subramanian