Oracle: Materialized views

The tables, views and materialized view that provide the data for a materialized view (that is: the objects that the materialized view selects from) are referred to as master tables (sometimes also as detail tables).

Materialized views were previously referred to as snapshots. Therefore, for backward compatibility, it is possible to use the term snapshot instead of materialized view.

The apparent package name dbms_mview is in fact a synonym for dbms_snapshot.

MOS note 101705.1 lists a few requirements/restrictions for ON COMMIT and fast refresh materialized views

ON COMMIT:

• The materialized view should have a single table aggregate or
• it should have a join only.
• count(*) must be present for Single-Table Aggregates
• count() should be present.
• It should be possible perform a fast refresh on the materialized view.

Fast refresh:

• The from list must contain base tables only (i.e. it must not select from views).
• Expressions must be constant (So, expressions involving sysdate or rownum etc. are not permitted)
• It cannot contain references to raw or long raw data types.
• It cannot contain having or connect by clauses.
• The where clause can contain only joins and they must be equi-joins (inner or outer) and all join predicates must be connected with ANDs. No selection predicates on individual tables are allowed
• It cannot have subqueries, inline views or set functions like union or minus.

Further stipulations for fast refreshability seem to be:

• No select list subquery
• No Analytic functions in the select clause.
• Referencing tables must not have an XMLindex index.
• No model clause
• No Nested queries with any, all or not exists
• Mutliple detail tables at different sites / no remote detail tables
• Index organized tables when using join or aggregates with group by

In addition, nested materialized views must have a join or aggregate function.

The init parameters query_rewrite_enabled and query_rewrite_integrity.

DBA_MVIEWS lists all materialized views in a database.

DBA_BASE_TABLE_MVIEWS lists magterialized views with logs.

Errors that occur when materialized views are automatically refreshed are recored in the alert log.

The rewrite hint

dbms_advisor.tune_mview optimizes materialized view creation statements to achieve refresh fast and enable query rewrite goals

Prebuilt tables

fast refresh

The query rewriting query transformation

Compare with the materialize hint.

The error messages

• ORA-12008: error in materialized view refresh path
• ORA-12051: ON COMMIT attribute is incompatible with other options
• ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view