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
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
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