difference between view and materialized view by manish sharma

Differences between View & Materialized View!

If you are wondering the difference between view and materialized view then read along. 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. It is a common part of database warehousing. It is 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 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. This is because 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 the 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.

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

  1. Query rewrites improve the performance of SQL execution. They are also 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 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.

So that’s it, guys. Hope this article on the differences between Views & Materialized Views was helpful. Do connect with us on Facebook & Twitter for more such interesting trivia.

Thanks & have a great day!