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
What is a materialized view in DBMS?
A materialized view is a view whose contents are computed and stored. Materialized view is also a logical virtual table, but in this case the result of the query is stored in the table or the disk. The performance of the materialized view is better than normal view since the data is stored in the disk.
It's also called indexed views since the table created after the query is indexed and can be accessed faster and efficiently.
Example
Consider the view given below −
Create view branchloan(branch-name, total-loan) as select branch-name , sum(amount) from loan groupby branch-name;
Materializing the above view would be especially useful if the total loan amount is required frequently.
It saves the effort of finding multiple tuples and adding up their amounts.
The task of keeping a materialized view up-to-date with the underlying data is known as materialised view maintenance. It can be maintained by recompilation on every update.
A better option is to use incremental view maintenance. It changes to database relations are used to compute changes to materialized view, which is then updated.
View maintenance can be done by following −
Manually defining triggers on insert, delete, and update of each relation in the view definition.
Manually written code to update the view whenever database relations are updated.
Supported directly by the database.
Incremental view maintenance
The changes like insert and delete operations to a relation or expressions are referred to as its differential, the set of tuples inserted to and deleted from r are denoted ir and dr.
To simplify our description, let’s consider inserts and deletes, we replace updates to a tuple by deletion of the tuple followed by insertion of the updated tuple.
We describe how to compute the change to the result of each relational operation, given changes to its inputs. We then outline how to handle relational algebra expressions.
Materialized view selection
The materialized view selection decision must be made on the basis of the system workload. Indices are just like materialized views, the problem of index selection is closely related to that of materialized view selection, although it is simpler.
Some database systems provide tools to help the database administrator with index and materialized view selection.
6K+ Views
