Read Using SQL Snippets ™ before using any of this site's sample code or techniques on your own systems.

Materialized Views

DBMS_MVIEW.EXPLAIN_MVIEW

As we saw in the preceding topic, predicting whether or not a materialized view is fast refreshable can be complicated.
The
DBMS_MVIEW.EXPLAIN_MVIEW
utility can simplify this task however.
Full details on how the utility works are available at the preceding link.
The material below will help you use the utility effectively.

MV_CAPABILITIES_TABLE

There are two ways to get the output from DBMS_MVIEW.EXPLAIN_MVIEW, via a table or via a varray.
To use the table method the current schema must contain a table called MV_CAPABILITIES_TABLE.
The full, documented CREATE TABLE command for MV_CAPABILITIES_TABLE can be found on UNIX systems at
$ORACLE_HOME/rdbms/admin/utlxmv.sql.
It is also available in Oracle's documentation at
Oracle Database Data Warehousing Guide - Basic Materialized Views - Using MV_CAPABILITIES_TABLE
(see Gotcha for a related bug).
Here is an abridged version.

This completes our preparations.
Now let's see DBMS_MVIEW.EXPLAIN_VIEW in action.

DBMS_MVIEW.EXPLAIN_MVIEW With a Query

DBMS_MVIEW.EXPLAIN_MVIEW can analyze three different types of materialized view code:

a defining query

a CREATE MATERIALIZED VIEW command

an existing materialized view.

Here is an example that explains a simple query which could appear as the
defining query in a CREATE MATERIALIZED VIEW command.

set long 5000
select my_mv_capabilities( 'SELECT * FROM T', 'REFRESH' ) as mv_report from dual ;

MV_REPORT
--------------------------------------------------------------------------------
Capable of:
REFRESH_COMPLETE
Not Capable of:
REFRESH_FAST
REFRESH_FAST_AFTER_INSERT
SCOTT.T
the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML
see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

The EXPLAIN_MVIEW output above shows that fast refresh is not possible in this case because T has no materialized view log.

Note that DBMS_MVIEW.EXPLAIN_MVIEW can report on a materialized view's refresh, rewrite, and
partition change tracking (PCT) capabilities.
For now we will only examine refresh capabilities.
Rewrite capabilities will be covered in
Query Rewrite Restrictions and Capabilities.

DBMS_MVIEW.EXPLAIN_MVIEW With CREATE MATERIALIZED VIEW

Now let's create a materialized view log on T and then use EXPLAIN_MVIEW to explain the capabilities of an
entire CREATE MATERIALIZED VIEW command.

Here we see that fast refresh is available after inserts, but not other types of DML.
Note also that the "REFRESH_FAST" capability will appear whenever at least one of the other REFRESH_FAST_% capabilities is available.
It does not mean the materialized view is fast refreshable in all cases.