Interview Questions

Materialized Views

In this article let’s discuss about using Materialized views in Oracle.

What is Materialized View?

Materialized view is similar to a view but the actual table data will exist in the Database. These views will be refreshed automatically when the master tables are updated.

CREATE MATERIALIZED VIEW mv_employee
REFRESH COMPLETE START WITH SYSDATE
AS SELECT EMP_ID,EMP_NAME,DEPT_ID,MAX(SALARY) FROM EMPLOYEES@orcl GROUP BY EMP_ID,EMP_NAME,DEPT_ID;

The above create statement creates a materialized view for the given query with complete refresh rate.

There are three refresh rates for Materialized view.

COMPLETE - it refreshes the entire table (or query)
FAST - it refreshes only the updated (UPDATE/DELETE/INSERT) changes.
FORCE - The system will try for FAST refresh, if it is not possible then it will go with COMPLETE refresh.

Mostly Materialized views are created to have a snapshot of a table or view (select query) from a remote database. Since it stores the physical data, it can increase the query running time or performance compared to normal views.

In this way we can use materialized views in oracle. Materialized views are available for Oracle version 9.0.