Differences between Views and Materialized Views in SQL
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.
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:
|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.|
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.