Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Difference Between View and Materialized View
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.
1K+ Views
