Analyzing Materialized View Capabilities

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:
DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2,
stmt_id IN VARCHAR2:= NULL);
Explain an existing or potential materialized view with output to 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:
CREATE TABLE MV_CAPABILITIES_TABLE
(STMT_ID VARCHAR(30), -- client-supplied unique statement identifier
MV VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
CAPABILITY_NAME VARCHAR(30), -- A descriptive name of particular
-- capabilities, such as REWRITE.
-- See Table 8–7
POSSIBLE CHARACTER(1), -- Y = capability is possible
-- N = capability is not possible
RELATED_TEXT VARCHAR(2000), -- owner.table.column, and so on related to
-- this message
RELATED_NUM NUMBER, -- When there is a numeric value
-- associated with a row, it goes here.
MSGNO INTEGER, -- When available, message # explaining
-- why disabled or more details when
-- enabled.
MSGTXT VARCHAR(2000), -- Text associated with MSGNO
SEQ NUMBER); -- Useful in ORDER BY clause when
-- selecting from this table.
You can use the utlxmv.sql script found in the admin directory to create MV_
CAPABILITIES_TABLE.


Example 8–8 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.
CREATE MATERIALIZED VIEW cal_month_sales_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;
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.

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');


SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
CAPABILITY_NAME P REL_TEXT MSGTXT
--------------- - -------- ------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N SALES no partition key or PMARKER in select list
PCT_TABLE N TIMES relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have new values
REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have ROWID
REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log does not have all necessary columns
REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have new values
REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have ROWID
REFRESH_FAST_AFTER_INSERT N SH.SALES mv log does not have all necessary columns
REFRESH_FAST_AFTER_ONETAB_DML N DOLLARS SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why
REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why
REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_ANY_DML N SH.TIMES mv log must have sequence
REFRESH_FAST_AFTER_ANY_DML N SH.SALES mv log must have sequence
REFRESH_PCT N PCT is not possible on any of the detail
tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N PCT is not possible on any detail tables

Posted in: Materialized Views