Materialized Views

 When the results of a view expression are stored in a database system, they are called materialized views. SQL does not provides any standard way of defining materialized view, however some database management system provides custom extensions to use materialized views. The process of keeping the materialized views updated is know as view maintenance.

Database system uses one of the three ways to keep the materialized view updated:

• Update the materialized view as soon as the relation on which it is defined is updated.
• Update the materialized view every time the view is accessed.
• Update the materialized view periodically.

Materialized view is useful when the view is accessed frequently, as it saves the computation time, as the result are stored in the database before hand. Materialized view can also be helpful in case where the relation on which view is defined is very large and the resulting relation of the view is very small. Materialized view has storage cost and updation overheads associated with it.

Command

Create materialized view View_Name

Build [Immediate/Deffered]

Refresh [Fast/Complete/Force]

on [Commit/Demand]

as Select ..........;

Example:

CREATE MATERIALIZED VIEW MV_Employee BUILD immediate
REFRESH complete
on commit SELECT * FROM Employee;

Source : https://www.geeksforgeeks.org/differences-between-views-and-materialized-views-in-sql/