A materialized view is a database object that contains the results of a query.
It is different from the simple oracle view.
These materialized views 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 materialized 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 options 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 the 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, the 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 => 'sysdate + 12/24');
Here the refresh group will refresh every 12 hours
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.
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
How Incremental statistics works Incremental global statistics work by storing a synopsis for each partition in the table. A synopsis is a statistical metadata for…
Hi,
I have used your articles to learn about oracle databases , The breadth of explanation in an easily understandable way is so good and much better than the other sites I had used ! Thanks a lot
I have got a lot of information from your site the best site for all languages. This post is really very helpful. Thanks for sharing.
Hi,
I have used your articles to learn about oracle databases , The breadth of explanation in an easily understandable way is so good and much better than the other sites I had used ! Thanks a lot