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