Analyzing Materialized View Capabilities in Data Warehousing

You can use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to learn what is possible with a materialized view or potential materialized view. In particular, this procedure enables you to determine:

  • If a materialized view is fast refreshable
  • What types of query rewrite you can perform with this materialized view
  • Whether PCT refresh is possible

Using this procedure is straightforward. You simply call DBMS_MVIEW.EXPLAIN_MVIEW, passing in as a single parameter the schema and materialized view name for an existing materialized view. Alternatively, you can specify the SELECT string for a potential materialized view or the complete CREATE MATERIALIZED VIEW statement. The materialized view or potential materialized view is then analyzed and the results are written into either a table called MV_CAPABILITIES_TABLE, which is the default, or to an array called MSG_ARRAY.

Note that you must run the utlxmv.sql script prior to calling EXPLAIN_MVIEW except when you are placing the results in MSG_ARRAY. The script is found in the admin directory. It is to create the MV_CAPABILITIES_TABLE in the current schema.

Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure

The EXPLAIN_MVIEW procedure has the following parameters:

  • stmt_id

An optional parameter.A client-supplied unique identifier to associate output rows with specific invocations of EXPLAIN_MVIEW.

  • mv

The name of an existing materialized view or the query definition or the entire CREATE MATERIALIZED VIEW statement of a potential materialized view you want to analyze.

  • msg-array

The PL/SQL varray that receives the output.

EXPLAIN_MVIEW analyzes the specified materialized view in terms of its refresh and rewrite capabilities and inserts its results (in the form of multiple rows)into MV_CAPABILITIES_TABLE or MSG_ARRAY.

DBMS_MVIEW.EXPLAIN_MVIEW Declarations
The following PL/SQL declarations that are made for you in the DBMS_MVIEW package show the order and datatypes of these parameters for explaining an existing materialized view and a potential materialized view with output to a table and to a VARRAY.

Explain an existing or potential materialized view with output to MV_CAPABILITIES_TABLE:

Explain an existing or potential materialized view with outputto a VARRAY:

DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2,
msg_array OUT SYS.ExplainMVArrayType);

Using MV_CAPABILITIES_TABLE
One of the simplest ways to use DBMS_MVIEW.EXPLAIN_MVIEW is with the MV_CAPABILITIES_TABLE, which has the following structure:

you can use the utlxmv.sql script found in the admin directory to create MV_CAPABILITIES_TABLE.

Example DBMS_MVIEW.EXPLAIN_MVIEW

First, create the materialized view. Alternatively, you can use EXPLAIN_MVIEW on a potential materialized view using its SELECT statement or the complete CREATE MATERIALIZED VIEW statement.

Then, you invoke EXPLAIN_MVIEW with the materialized view to explain. You need to use the SEQ column in an ORDER BY clause so the rows will display in a logical order. If a capability is not possible, N will appear in the P column and an explanation in the MSGTXT column. If a capability is not possible for more than one reason, a row is displayed for each reason.


MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details
The following table lists explanations for values in the CAPABILITY_NAME column.

CAPABILITY_NAME Column Details

MV_CAPABILITIES_TABLE Column Details
The following table lists the semantics for RELATED_TEXT and RELATED_NUM columns
MV_CAPABILITIES_TABLE Column Details



All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Data Warehousing Topics