Analyzing Materialized View Capabilities

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 theSELECT 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 calledMV_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 theadmin directory. It is to create the MV_CAPABILITIES_TABLE in the current schema. An explanation of the various capabilities is inTable 8-7, and all the possible messages are listed in Table 8-8.

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 ofEXPLAIN_MVIEW.

• mv

  The name of an existing materialized view or the query definition or the entireCREATE 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) intoMV_CAPABILITIES_TABLE or MSG_ARRAY.

See Also:

Oracle Database PL/SQL Packages and Types Reference for further information about the DBMS_MVIEW package

DBMS_MVIEW.EXPLAIN_MVIEW Declarations

The following PL/SQL declarations that are made for you in theDBMS_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 aVARRAY.

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 createMV_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 itsSELECT statement or the complete CREATE MATERIALIZEDVIEW 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 theSEQ 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 theMSGTXT 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

See Also:

Chapter 15, "Maintaining the Data Warehouse" and Chapter 18, "Advanced Query Rewrite" for further details about PCT

MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details

Table 8-7 lists explanations for values in theCAPABILITY_NAME column.

Table 8-7 CAPABILITY_NAME Column Details

CAPABILITY_NAME	Description
PCT	If this capability is possible, Partition Change Tracking (PCT) is possible on at least one detail relation. If this capability is not possible, PCT is not possible with any detail relation referenced by the materialized view.
REFRESH_COMPLETE	If this capability is possible, complete refresh of the materialized view is possible.
REFRESH_FAST	If this capability is possible, fast refresh is possible at least under certain circumstances.
REWRITE	If this capability is possible, at least full text match query rewrite is possible. If this capability is not possible, no form of query rewrite is possible.
PCT_TABLE	If this capability is possible, it is possible with respect to a particular partitioned table in the top levelFROM list. When possible, PCT applies to the partitioned table named in theRELATED_TEXT column. PCT is needed to support fast fresh after partition maintenance operations on the table named in theRELATED_TEXT column. PCT may also support fast refresh with regard to updates to the table named in theRELATED_TEXT column when fast refresh from a materialized view log is not possible. PCT is also needed to support query rewrite in the presence of partial staleness of the materialized view with regard to the table named in theRELATED_TEXT column. When disabled, PCT does not apply to the table named in the RELATED_TEXT column. In this case, fast refresh is not possible after partition maintenance operations on the table named in theRELATED_TEXT column. In addition, PCT-based refresh of updates to the table named in theRELATED_TEXT column is not possible. Finally, query rewrite cannot be supported in the presence of partial staleness of the materialized view with regard to the table named in theRELATED_TEXT column.
PCT_TABLE_REWRITE	If this capability is possible, it is possible with respect to a particular partitioned table in the top levelFROM list. When possible, PCT applies to the partitioned table named in theRELATED_TEXT column. This capability is needed to support query rewrite against this materialized view in partial stale state with regard to the table named in theRELATED_TEXT column. When disabled, query rewrite cannot be supported if this materialized view is in partial stale state with regard to the table named in theRELATED_TEXT column.
REFRESH_FAST_AFTER_INSERT	If this capability is possible, fast refresh from a materialized view log is possible at least in the case where the updates are restricted toINSERT operations; complete refresh is also possible. If this capability is not possible, no form of fast refresh from a materialized view log is possible.
REFRESH_FAST_AFTER_ONETAB_DML	If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation, provided all update operations are performed on a single table. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations are performed on multiple tables.
REFRESH_FAST_AFTER_ANY_DML	If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation or the number of tables updated. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations (other than INSERT) affect multiple tables.
REFRESH_FAST_PCT	If this capability is possible, fast refresh using PCT is possible. Generally, this means that refresh is possible after partition maintenance operations on those detail tables where PCT is indicated as possible.
REWRITE_FULL_TEXT_MATCH	If this capability is possible, full text match query rewrite is possible. If this capability is not possible, full text match query rewrite is not possible.
REWRITE_PARTIAL_TEXT_MATCH	If this capability is possible, at least full and partial text match query rewrite are possible. If this capability is not possible, at least partial text match query rewrite and general query rewrite are not possible.
REWRITE_GENERAL	If this capability is possible, all query rewrite capabilities are possible, including general query rewrite and full and partial text match query rewrite. If this capability is not possible, at least general query rewrite is not possible.
REWRITE_PCT	If this capability is possible, query rewrite can use a partially stale materialized view even inQUERY_REWRITE_INTEGRITY = ENFORCED or TRUSTED modes. When this capability is not possible, query rewrite can use a partially stale materialized view only inQUERY_REWRITE_INTEGRITY = STALE_TOLERATED mode.

MV_CAPABILITIES_TABLE Column Details

Table 8-8 lists the semantics forRELATED_TEXT and RELATED_NUM columns.

Table 8-8 MV_CAPABILITIES_TABLE Column Details

MSGNO	MSGTXT	RELATED_NUM	RELATED_TEXT
NULL	NULL		For PCT capability only: [owner.]name of the table upon which PCT is enabled
2066	This statement resulted in an Oracle error	Oracle error number that occurred
2067	No partition key or PMARKER or join dependent expression in SELECT list		[owner.]name of relation for which PCT is not supported
2068	Relation is not partitioned		[owner.]name of relation for which PCT is not supported
2069	PCT not supported with multicolumn partition key		[owner.]name of relation for which PCT is not supported
2070	PCT not supported with this type of partitioning		[owner.]name of relation for which PCT is not supported
2071	Internal error: undefined PCT failure code	The unrecognized numeric PCT failure code	[owner.]name of relation for which PCT is not supported
2072	Requirements not satisfied for fast refresh of nested materialized view
2077	Materialized view log is newer than last full refresh		[owner.]table_name of table upon which the matrialized view log is needed
2078	Materialized view log must have new values		[owner.]table_name of table upon which the materialized view log is needed
2079	Materialized view log must have ROWID		[owner.]table_name of table upon which the materialized view log is needed
2080	Materialized view log must have primary key		[owner.]table_name of table upon which the materialized view log is needed
2081	Materialized view log does not have all necessary columns		[owner.]table_name of table upon which the materialized view log is needed
2082	Problem with materialized view log		[owner.]table_name of table upon which the materialized view log is needed
2099	Materialized view references a remote table or view in the FROM list	Offset from the SELECT keyword to the table or view in question	[owner.]name of the table or view in question
2126	Multiple master sites		Name of the first different node, or NULL if the first different node is local
2129	Join or filter condition(s) are complex		[owner.]name of the table involved with the join or filter condition (orNULL when not available)
2130	Expression not supported for fast refresh	Offset from the SELECT keyword to the expression in question	The alias name in the SELECT list of the expression in question
2150	SELECT lists must be identical across the UNION operator	Offset from the SELECT keyword to the first different select item in theSELECT list	The alias name of the first different select item in the SELECT list
2182	PCT is enabled through a join dependency		[owner.]name of relation for whichPCT_TABLE_REWRITE is not enabled
2183	Expression to enable PCT not in PARTITION BY of analytic function or model	The unrecognized numeric PCT failure code	[owner.]name of relation for which PCT is not enabled
2184	Expression to enable PCT cannot be rolled up		[owner.]name of relation for which PCT is not enabled
2185	No partition key or PMARKER in the SELECT list		[owner.]name of relation for whichPCT_TABLE_REWRITE is not enabled
2186	GROUP OUTER JOIN is present
2187	Materialized view on external table

Analyzing Materialized View Capabilities

http://docs.oracle.com/cd/B19306_01/server.102/b14223/basicmv.htm#sthref560