What types of materialized views can be created in an Oracle database?

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