There are three types of Materialized Views that can be created in an Oracle database:
1. Materialized Views with Aggregates
* The most commonly used materialized view, particularly in Datawarehouse databases.
Example:
CREATE MATERIALIZED VIEW SEQ_AGGR_INFO REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens from DATE_TO_SEQNO group by dato order by 1, 2;
Requirements:
* The SELECT-list must contain all of the GROUP BY columns (if present)
* There must be a COUNT(*) and a COUNT(column) on any aggregated columns.
* A materialized view logs must be present on all tables referenced in the query that defines the materialized view.
* Valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX.
2. Materialized Views containing only joins
* Materialized views contain only joins and no aggregates
Example:
CREATE MATERIALIZED VIEW active_data TABLESPACE data1 CACHE REFRESH FORCE ON COMMIT WITH PRIMARY KEY ENABLE QUERY REWRITE AS SELECT act.q_id, act.doc_type, act.next_q_id, act.prev_q_id, act.created, act.created_by, act.last_modified, act.last_modified_by, act.contents, act.accum_value, act.accum_total FROM active_data act JOIN archived_data arc ON arc.q_id = act.q_id;
An advantage with these types of materialized views is that expensive joins are precalculated.
Only REFRESH ON COMMIT or REFRESH ON DEMAND is legal with mviews containing only joins. When ON COMMIT is used, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.
3. Nested Materialized Views
* A nested materialized view is a materialized view whose definition is based on another materialized view.
I have no personal experience using this type of materialized view, but Oracle has supplied some examples if you follow the link above.
Sources: Oracle 12.2 Data Warehousing Guide
No comments:
Post a Comment