what is Oracle materialized view and Materialized View Log Lesson 1



Last updated on August 27th, 2016 at 05:51 pm

What is Oracle materialized view
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects. 

What is Oracle Materialized View Log
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must re-execute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

Refresh Group

A refresh group is a collection of one or more materialized views that Oracle refreshes in an atomic transaction, guaranteeing that relationships among the master tables are preserved

Examples:

CREATE MATERIALIZED VIEW mv_test
TABLESPACE test_data
BUILD IMMEDIATE
REFRESH complete ON COMMIT AS
SELECT a.name,b.salary FROM emp@DB_LINK a, compensation@DB_LINK b where a.emp_id=b.emp_id;

CREATE MATERIALIZED VIEW mv_test
TABLESPACE test_data
BUILD IMMEDIATE
REFRESH force ON COMMIT AS
SELECT a.name,b.salary FROM emp@DB_LINK a, compensation@DB_LINK b where a.emp_id=b.emp_id;

CREATE MATERIALIZED VIEW LOG ON emp;

exec DBMS_REFRESH.MAKE(name=>’test_grp’, –
list=>’test_mv1,test_mv2′,’mv_test’ –
next_date => sysdate, –
interval => ‘null’);

exec DBMS_REFRESH.REFRESH(‘test_grp’);

 

 

Privileges required to create materialized view

User must have CREATE MATERIALIZED VIEW to create materialize view

General Syntax

CREATE MATERIALIZED VIEW <View Name>

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

<Select statement > ;

Explanation of each term

BUILD [IMMEDIATE | DEFERRED]

 

We can specify to populate immediately Or we can specify DEFERRED to populate on the first requested refresh.
REFRESH [FAST | COMPLETE | FORCE ]

 

There are three option here.Each explained below

1) FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

2) COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.

3)FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

ON [COMMIT | DEMAND ] We can specify ON COMMIT so that  refresh is triggered by a committed data change in one of the dependent tables Or we can specify ON DEMAND so that   refresh is initiated by a manual request or a scheduled task.
[[ENABLE | DISABLE] QUERY REWRITE] The view is eligible for query rewrite

Difference between Oracle view and Oracle materialized view

As explained above Materialized views are disk based and are updated periodically based upon the query definition.In materialized view,result set is stored in the materialized view table

Views are virtual only and run the query definition each time they are accessed.In view no result set is stored  and it accesses the underlying table each time view is accessed


Leave a Reply