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 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 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
|CREATE MATERIALIZED VIEW <View Name>
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
<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|
ON demand AS
SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
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:
Refresh one or more materialized views.
Refresh all materialized views.
Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.
Here C stands for Complete refresh
If you try to do fast refresh ,using the command below
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.
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
CREATE MATERIALIZED VIEW mv_test
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
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’, –
next_date => sysdate, –
interval => ‘null’);
How to check the status of the Materialized view
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 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