4.4 EXPLAIN_MVIEW utility

As we have seen so far, in order to get the most benefit out of a materialized view, the materialized view must conform to some rules. For instance, you may need to create materialized view logs to make fast refresh possible or add a partition marker to allow partition change tracking. The EXPLAIN_MVIEW interface is designed to help you determine what these rules are. Before creating the materialized view, you can run its defining query through the EXPLAIN_MVIEW utility. If a capability such as fast refresh is disallowed, EXPLAIN_MVIEW will point out the offending construct or missing columns. You now have the opportunity to fix these problems before expending precious resources to create the materialized view. This facility is new in Oracle 9 i. EXPLAIN_MVIEW can also tell you what features are available with your existing materialized view.

EXPLAIN_MVIEW is invoked via the PL/SQL API DBMS_MVIEW.EXPLAIN_MVIEW and comes in two flavors. You can provide an existing materialized view name to it and display its capabilities. Alternatively, you can provide a SELECT statement that defines your intended materialized view, and EXPLAIN_MVIEW will project its capabilities. The results of EXPLAIN_MVIEW are placed in the MV_CAPABILITIES_TABLE table, which must be created by running the utlxmv.sql script found in the rdbms/admin directory.

In the following example, we have deliberately introduced problems with the materialized view logs to illustrate how EXPLAIN_MVIEW can be used. We have dropped the materialized view log on product and removed the INCLUDING NEW VALUES clause from...