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 are the types of Materialized Views?
The SELECT clause in the materialized view creation statement represents the data that the materialized view is to include. Some restrictions that limit what can be defined and multiple tables can be joined together. There are several elements including views, inline views (subqueries in the FROM clause of a SELECT statement), subqueries, and materialized views that can all be joined or imputed in the SELECT clause.
There are the various types of Materialised views are as follows −
Materialized Views with Aggregates − In data warehouses, materialized views generally include aggregates. For a quick update to be possible, the SELECT list must include all of the GROUP BY columns (if present), and there should be a COUNT(*) and a COUNT(column) on some aggregated columns.
Materialized view logs should be present on all tables referenced in the query that represents the materialized view. The true aggregate functions are SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the interpretation to be aggregated can be some SQL value expression.
Materialized Views containing only Joins − Some materialized views include only joins and no aggregates where a materialized view is generated that joins the sales table to the times and customer tables. The benefit of making this type of materialized view is that expensive joins will be pre-computed.
Quick update for a materialized view including only joins applies after any type of DML to the base tables (direct-path or conventional INSERT, UPDATE, or DELETE). A materialized view including only joins can be represented to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is implemented at the commit time of the transaction that does DML on the materialized view’s analyze table.
If the materialized view includes only joins, the ROWID columns for each table (and each example of a table that appears several times in the FROM list) should be present in the SELECT list of the materialized view.
If the materialized view has remote tables in the FROM clause, all tables in the FROM clause should be placed on that similar site. In addition, ON COMMIT refresh is not provided for materialized view with remote tables. Materialized view logs should be present on the remote site for each detail table of the materialized view and ROWID columns should be present in the SELECT list of the materialized view.
Nested Materialized Views − A nested materialized view is a materialized view whose description is located on another materialized view. A nested materialized view can reference different relations in the database including referencing materialized views.
In a data warehouse, it can generally create some aggregate views on a single join (for instance, rollups along multiple dimensions). It can incrementally be supporting these distinct materialized aggregate views can take a long time, because the basic join has to be implemented several times.
2K+ Views
