- Trending Categories
- Data Structure
- Operating System
- C Programming
- 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.
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.
- Difference Between View and Materialized View
- What is a database(dbms)?
- What is a multimedia database (DBMS)?
- What is a Data Model in DBMS?
- What is a composite attribute in DBMS?
- What is a query language in DBMS?
- What is Decomposition in DBMS?
- What is 4NF in DBMS?
- What is RAID in DBMS?
- What are the types of Materialized Views?
- What is dirty read in a transaction(DBMS)?
- What is Multivalued Dependency (DBMS)?
- What is an instance in DBMS?
- What is Hierarchical model in DBMS?
- What is functional dependency in DBMS?