View – View evaluates the data/dataset in the tables underlying the view definition when the view is queried.
The data/dataset of view is not stored anywhere.
View is a logical or virtual memory based.
Materialized view – Materialized View is physical duplication of data of a Table. The data inside the table may come from join quires of other tables.
The Data or Dataset is stored in a table.
Materialized views are disk based.
(Materialized views are schema objects that can be used to summarize precompute replicate and distribute data. E.g. to construct a data warehouse.)
Advantage of using a view
View always returns the latest data. no REFRESH of the view is required.
Drawback of using a view
Since view collects data from join queries of multiple tables and when the view is queried it performs the join operations each time. So its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.
Advantage of using a Materialized view
when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once , rather than each time you select from the materialized view.
An updatable materialized view lets you insert update and delete.
Drawback of using a Materialized view
The data you get back from the materialized view is only as up to date as the last time the materialized view has been REFRESHed.
(Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables)