Difference Between View and Materialized View

MaterializeData StorageSQL

In this post, we will understand the difference between a view and a materialized view.

Views

  • It is a logical and virtual copy of a table that is created by executing a ‘select query’ statement.

  • This result isn’t stored anywhere on the disk.

  • Hence, every time, a query needs to be executed when certain data is needed.

  • This way, the most recently updated data would be available from the tables.

  • The tuple/result of the query doesn’t get stored.

  • Instead, the query expression is stored on the disk.

  • The query expression is stored, due to which the last updated data is obtained.

  • They don’t have a storage/update cost associated with it.

  • They are designed with a specific architecture.

  • This means there is a SQL standard to define a view.

  • They are used when data has to be accessed infrequently, but data gets updated frequently.

Materialized view

  • It is a logical and virtual copy of data.

  • It is the result of a ‘select query’, given that the query is stored in the table or disk.

  • The query expression and the resultant tuple are stored on the disk.

  • The query expression isn’t executed every time the user tries to fetch data.

  • This means, the user doesn’t get the most recently updated values of a table in the database.

  • It has a storage and update cost associated with it.

  • They are designed with a generic architecture, hence there is no SQL standard to define it.

  • Its functionality is provided by certain databases as an extension.

  • It is used when data has to be accessed frequently but the data in the table isn’t updated frequently.

raja
Published on 15-Apr-2021 07:36:43
Advertisements