Materialized View Enabling Query Rewrite

Before creating a materialized view, you can verify what types of query rewrite are
possible by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW, or use DBMS_
ADVISOR.TUNE_MVIEW to optimize the materialized view so that many types of
query rewrite are possible. Once the materialized view has been created, you can use
DBMS_MVIEW.EXPLAIN_REWRITE to find out if (or why not) it will rewrite a specific
query.

Even though a materialized view is defined, it will not automatically be used by the
query rewrite facility. Even though query rewrite is enabled by default, you also must specify the ENABLE QUERY REWRITE clause if the materialized view is to be
considered available for rewriting queries.

If this clause is omitted or specified as DISABLE QUERY REWRITE when the
materialized view is created, the materialized view can subsequently be enabled for
query rewrite with the ALTER MATERIALIZED VIEW statement.
If you define a materialized view as BUILD DEFERRED, it is not eligible for query
rewrite until it is populated with data through a complete refresh.

Query Rewrite Restrictions
Query rewrite is not possible with all materialized views. If query rewrite is not
occurring when expected, DBMS_MVIEW.EXPLAIN_REWRITE can help provide
reasons why a specific query is not eligible for rewrite. If this shows that not all types of query rewrite are possible, use the procedure DBMS_ADVISOR.TUNE_MVIEW to see
if the materialized view can be defined differently so that query rewrite is possible.

Also, check to see if your materialized view satisfies all of the following conditions.

Materialized View Restrictions
You should keep in mind the following restrictions:
■ The defining query of the materialized view cannot contain any non-repeatable
expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
■ The query cannot contain any references to RAW or LONG RAW datatypes or object
REFs.
■ If the materialized view was registered as PREBUILT, the precision of the columns
must agree with the precision of the corresponding SELECT expressions unless
overridden by the WITH REDUCED PRECISION clause.

General Query Rewrite Restrictions
You should keep in mind the following restrictions:
■ If a query has both local and remote tables, only local tables will be considered for potential rewrite.
■ Neither the detail tables nor the materialized view can be owned by SYS.
If a column or expression is present in the GROUP BY clause of the materialized
view, it must also be present in the SELECT list.
■ Aggregate functions must occur only as the outermost part of the expression. That
is, aggregates such as AVG(AVG(x)) or AVG(x)+ AVG(x) are not allowed.
■ CONNECT BY clauses are not allowed.

Posted in: Materialized Views