Here are listed the key distinctions between Views and Materialized Views. This material has been gathered throughout time from a variety of sources and books.
- A Materialized View is always stored on disk, whereas a View is never.
- A Materialized view is a physical replica of a base table, whereas a View is a virtual table created from one or more base tables or views.
- Every time we SELECT the View, the underlying query is executed. The effect is that the user always gets the most recent or updated data from the source tables. Either manually updating Materialized views by creating and using a stored method, or enforcing DML triggers. This means, in order to prevent users from receiving the most recent updated value if it changes in the database, the query expression is not executed every time a user tries to collect the data because the result of the query is saved on the disk.
- The virtual table (View) is updated each time it is used, while Materialized View must be updated manually or with triggers.
- Views process information slowly, but Materialized views process information quickly.
- Memory storage is not needed for Views, but it is for Materialized views.
- Views have no storage costs and no update costs, however, Materialized Views do have both storage and update costs.
Big Data and SQL 