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 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) intoMV_CAPABILITIES_TABLE
or MSG_ARRAY
.
See Also:
Oracle Database PL/SQL Packages and Types Reference for further information about theDBMS_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
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 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 PCTMV_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 |
---|---|
| 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. |
| If this capability is possible, complete refresh of the materialized view is possible. |
| If this capability is possible, fast refresh is possible at least under certain circumstances. |
| 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. |
| If this capability is possible, it is possible with respect to a particular partitioned table in the top level PCT is needed to support fast fresh after partition maintenance operations on the table named in the PCT may also support fast refresh with regard to updates to the table named in the 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 the When disabled, PCT does not apply to the table named in the |
| If this capability is possible, it is possible with respect to a particular partitioned table in the top level This capability is needed to support query rewrite against this materialized view in partial stale state with regard to the table named in the When disabled, query rewrite cannot be supported if this materialized view is in partial stale state with regard to the table named in the |
| If this capability is possible, fast refresh from a materialized view log is possible at least in the case where the updates are restricted to |
| 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. |
| 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 |
| 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. |
| 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. |
| 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. |
| 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. |
| If this capability is possible, query rewrite can use a partially stale materialized view even in |
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 |
---|---|---|---|
|
| For PCT capability only: | |
2066 | This statement resulted in an Oracle error | Oracle error number that occurred | |
2067 | No partition key or |
| |
2068 | Relation is not partitioned |
| |
2069 | PCT not supported with multicolumn partition key |
| |
2070 | PCT not supported with this type of partitioning |
| |
2071 | Internal error: undefined PCT failure code | The unrecognized numeric PCT failure code |
|
2072 | Requirements not satisfied for fast refresh of nested materialized view | ||
2077 | Materialized view log is newer than last full refresh |
| |
2078 | Materialized view log must have new values |
| |
2079 | Materialized view log must have |
| |
2080 | Materialized view log must have primary key |
| |
2081 | Materialized view log does not have all necessary columns |
| |
2082 | Problem with materialized view log |
| |
2099 | Materialized view references a remote table or view in the | Offset from the |
|
2126 | Multiple master sites | Name of the first different node, or | |
2129 | Join or filter condition(s) are complex |
| |
2130 | Expression not supported for fast refresh | Offset from the | The alias name in the |
2150 |
| Offset from the | The alias name of the first different select item in the |
2182 | PCT is enabled through a join dependency |
| |
2183 | Expression to enable PCT not in | The unrecognized numeric PCT failure code |
|
2184 | Expression to enable PCT cannot be rolled up |
| |
2185 | No partition key or |
| |
2186 |
| ||
2187 | Materialized view on external table |
Analyzing Materialized View Capabilities
http://docs.oracle.com/cd/B19306_01/server.102/b14223/basicmv.htm#sthref560