What is the difference between View and Materialized View.

A structured query language is a vast topic with hell lot of concepts. Today we are going to know the difference between view and materialized view. Before knowing the difference between view and materialized view let us first know what are we talking about exactly.

What is a view?

Views are a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. 

The advantage of a view is that it will always return the latest data to you. The disadvantage of a view is that 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.

What is a materialized view?

Materialized views are similar to regular views, just that they are the logical view of your data but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will be stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in the case of view.

First Difference between view and materialized view:

  • In Views, the query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

  • When we create the view using any table,  rowid of view is same as the original table but in the case of Materialized view rowid is different.

  • In the case of View, we always get latest data but in a case of Materialized view, we need to refresh the view for getting latest data.

  • The performance of View is less than Materialized view.

  • In the case of a view, it’s only the logical view of the table and no separate copy of table but in the case of Materialized view, we get a physically separate copy of the table.

  • In the case of Materialized view, we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database.

Conclusion:

We use views because they are more feasible as we need only logical representation of table data and no extra space is needed. We easily get a replica of data and we can perform our operation on that data without affecting actual table data but when we see a performance which is crucial for the large application we use materialized view where Query Response time matters so Materialized views are used mostly with data warehousing or business intelligence application.

So, now you know the difference between view and materialized view. Do share the knowledge.

  • Add Your Comment