Difference between View and Materialized View in Database or SQL.

By Manish Sharma

Major Differences between Views and Materialized Views!.

Views are the virtual projection of an output query or the dynamic view of the data in a database that is presented to the user whenever requested. Materialized views on the other hand are a non-virtual schema which is a common part of database warehousing, primarily used for summarizing, pre-computing, replicating and distributing data etc. Now having said that, let’s move on to some basic differences between Views and Materialized views in order to gain a better understanding.

Access to the data

The operations performed using Views directly affects the data in the base table. Therefore they are subjected to the integrity constraints and triggers of the base table.

Materialized views provide an indirect access to the data of the base table. This is due to the fact that they create a separate schema to store the results of a query.

Storage space

One of the common differences between views and materialized views is the way they take up storage space. Since Views are the virtual projection of a query result hence they do not take up any storage area. This helps in reduction of memory usage and encourages the use of Shared SQL.

Whereas the schema created by Materialized Views take up some storage space as it is saved in either the same database as its base table or in a different database.

Usage of Views and Materialized Views

Differences between views and materialized views can also be understood in terms of their usages.

Views have a more restrictive behavior towards a base table.

  1. It could be used to isolate an application in order to prohibit any change in base table definition.
  2. It can be used in order to simplify SQL statements for the user.
  3. Views could be used to enhance security by restricting access to a predetermined set of columns and rows

Existence of Materialized Views is transparent to the SQL except when used for query rewrites.

  1. Query rewrites are said to improve the performance of SQL execution and are useful in a data warehouse environment.
  2. Users can insert, delete and update the data by means of updatable materialized views.

Apart from these differences between views and materialized views, some other important points to keep in mind which further clears the concept of views and materialized views are:

  1. Views can be based on each other in addition to operating on base tables. A view can JOIN another view with a table using – GROUP BY or UNION clause.
  2. Materialized views can be defined on a base table, partitioned table or Views whereas indexes are defined on Materialized views.
  3. Ultimately a materialized view log is a schema object which records changes to a master table's data so that the materialized view defined on that master table can be refreshed incrementally.

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn