Differences between Views and Materialized Views in SQL

Views:
A View is a virtual relation that acts as an actual relation. It is not a part of logical relational model of the database system. Tuples of the view are not stored in the database system and tuples of the view are generated every time the view is accessed. Query expression of the view is stored in the databases system.

Views can be used everywhere were we can use the actual relation. Views can be used to create custom virtual relations according to the needs of a specific user. We can create as many views as we want in a databases system.

Materialized Views:
When the results of a view expression are stored in a database system, they are called materialized views. SQL does not provides any standard way of defining materialized view, however some database management system provides custom extensions to use materialized views. The process of keeping the materialized views updated is know as view maintenance.

Database system uses one of the three ways to keep the materialized view updated:



  • Update the materialized view as soon as the relation on which it is defined is updated.
  • Update the materialized view every time the view is accessed.
  • Update the materialized view periodically.

Materialized view is useful when the view is accessed frequently, as it saves the computation time, as the result are stored in the database before hand. Materialized view can also be helpful in case where the relation on which view is defined is very large and the resulting relation of the view is very small. Materialized view has storage cost and updation overheads associated with it.

Differences between Views and Materialized Views:

Views Materialized Views
Query expression are stored in the databases system, and not the resulting tuples of the query expression. Resulting tuples of the query expression are stored in the databases system.
Views needs not to be updated every time the relation on which view is defined is updated, as the tuples of the views are computed every time when the view is accessed. Materialized views are updated as the tuples are stored in the database system. It can be updated in one of three ways depending on the databases system as mentioned above.
It does not have any storage cost associated with it. It does have a storage cost associated with it.
It does not have any updation cost associated with it. It does have updation cost associated with it.
There is an SQL standard of defining a view. There is no SQL standard for defining a materialized view, and the functionality is provided by some databases systems as an extension.
Views are useful when the view is accessed infrequently. Materialized views are efficient when the view is accessed frequently as it saves the computation time by storing the results before hand.



My Personal Notes arrow_drop_up

Student, BCA

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.