what is materialized views in oracle?

Table of Contents

Toggle

What is materialized views in oracle

A materialized view is a database object that contains the results of a query. It is different from simple oracle view. These materialized view have data stored and when you query the materialized view,it returns data from the data stored. 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 Oracle 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.

Privileges required to create materialized view

User must have CREATE MATERIALIZED VIEW to create materialize view

General Syntax

CREATE MATERIALIZED VIEW
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
;

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

CREATE MATERIALIZED VIEW mv_test
BUILD IMMEDIATE
REFRESH complete
ON demand AS
SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
FROM EMP,DEPT
where EMP.DEPTNO= DEPT.DEPTNO;

Materialized View Refresh

We can refresh materialized view through the PLSQL package DBMS_MVIEW

The DBMS_MVIEW package contains three APIs for performing refresh operations:

DBMS_MVIEW.REFRESH

Refresh one or more materialized views.

DBMS_MVIEW.REFRESH_ALL_MVIEWS

Refresh all materialized views.

DBMS_MVIEW.REFRESH_DEPENDENT

Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.

EXECUTE DBMS_MVIEW.REFRESH('MV_TEST','C');

Here C stands for Complete refresh

If you try to do fast refresh ,using the command below

EXECUTE DBMS_MVIEW.REFRESH('MV_TEST','F');

Here F stands for fast Refresh

It will throw error, as there is no materialized view log. we will be checking materialized view log below

With Oracle 12c,Oracle has introduced new API DBMS_SYNC_REFRESH for refresh

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');

How to check the status of the Materialized view

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE
FROM USER_MVIEWS ORDER BY MVIEW_NAME;

Out-of-Place Refresh Option for materialized View

With 12c Release 1, a new refresh option is available to improve materialized view refresh performance and availability. This refresh option is called out-of-place refresh because it uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. The out-of-place refresh option works with all existing refresh methods, such as FAST ('F'), COMPLETE ('C'), PCT ('P'), and FORCE ('?').

Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well. It also enables you to achieve a very high degree of availability because the materialized views that are being refreshed can be used for direct access and query rewrite during the execution of refresh statements.

In out-of-place refresh, the entire or affected portions of a materialized view are computed into one or more outside tables.

exec dbms_mview.refresh('MV_TEST', out_of_place=>true, atomic_refresh=>false)
;

Difference Between Materialized View and Oracle 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

Also Reads
Oracle Joins
Oracle subqueries
https://en.wikipedia.org/wiki/Materialized_view