通过DBMS_MVIEW.EXPLAIN_MVIEW分析物化视图的能力

郑全

DBMS_MVIEW.EXPLAIN_MVIEW包的使用

DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码，分别是：
1.定义的查询
2.一个CREATE MATERIALIZED VIEW的语句
3.一个存在的物化视图

步骤如下：
1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句
SQL>@$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
如果未执行此语句，则有运行包的时候会报表不存在的错误。

2、先清空表中记录，便于分析信息的查看
TRUNCATE TABLE mv_capabilities_table;

3、执行包dbms_mview.explain_mview，其中，包里的参数可以是物化视图、查询语句、
CREATE物化视图语句。
begin
dbms_mview.explain_mview('SELECT a.id,b.name,a.rowid a_rowid,b.rowid b_rowid
   FROM comp1 a , comp2 b WHERE a.id=b.id');
end;
/

4、查看分析结果或错误报告
select MSGTXT from mv_capabilities_table where capability_name='REFRESH_FAST_AFTER_INSERT';
或
select * from mv_capabilities_table;
Tips:
(1)、物化视图在判断是否支持刷新时不支持SQL92的标准外联接的写法，需要改成Oracle自己的(+)的方式。
(2)、对于包含外联接的物化视图的快速刷新，无法高效的利用物化视图日志，因此即使是快速刷新，
性能也可能很差。

下面使用物化视图
首先要建表$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
utlxmv.sql的内容如下：

CREATE TABLE MV_CAPABILITIES_TABLE
   (STATEMENT_ID         VARCHAR(30),  -- Client-supplied unique statement identifier
    MVOWNER              VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
    MVNAME               VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
    CAPABILITY_NAME      VARCHAR(30),  -- A descriptive name of the particular
                                       -- capability:
                                       -- REWRITE
                                       --   Can do at least full text match
                                       --   rewrite
                                       -- REWRITE_PARTIAL_TEXT_MATCH
                                       --   Can do at leat full and partial
                                       --   text match rewrite
                                       -- REWRITE_GENERAL
                                       --   Can do all forms of rewrite
                                       -- REFRESH
                                       --   Can do at least complete refresh
                                       -- REFRESH_FROM_LOG_AFTER_INSERT
                                       --   Can do fast refresh from an mv log
                                       --   or change capture table at least
                                       --   when update operations are
                                       --   restricted to INSERT
                                       -- REFRESH_FROM_LOG_AFTER_ANY
                                       --   can do fast refresh from an mv log
                                       --   or change capture table after any
                                       --   combination of updates
                                       -- PCT
                                       --   Can do Enhanced Update Tracking on
                                       --   the table named in the RELATED_NAME
                                       --   column.  EUT is needed for fast
                                       --   refresh after partitioned
                                       --   maintenance operations on the table
                                       --   named in the RELATED_NAME column
                                       --   and to do non-stale tolerated
                                       --   rewrite when the mv is partially
                                       --   stale with respect to the table
                                       --   named in the RELATED_NAME column.
                                       --   EUT can also sometimes enable fast
                                       --   refresh of updates to the table
                                       --   named in the RELATED_NAME column
                                       --   when fast refresh from an mv log
                                       --   or change capture table is not
                                       --   possilbe.
    POSSIBLE             CHARACTER(1), -- T = capability is possible
                                       -- F = capability is not possible
    RELATED_TEXT         VARCHAR(2000),-- Owner.table.column, alias name, etc.
                                       -- related to this message.  The
                                       -- specific meaning of this column
                                       -- depends on the MSGNO column.  See
                                       -- the documentation for
                                       -- DBMS_MVIEW.EXPLAIN_MVIEW() for details
    RELATED_NUM          NUMBER,       -- When there is a numeric value
                                       -- associated with a row, it goes here.
                                       -- The specific meaning of this column
                                       -- depends on the MSGNO column.  See
                                       -- the documentation for
                                       -- DBMS_MVIEW.EXPLAIN_MVIEW() for details
    MSGNO                INTEGER,      -- When available, QSM message #
                                       -- explaining why not possible or more
                                       -- details when enabled.
    MSGTXT               VARCHAR(2000),-- Text associated with MSGNO.
    SEQ                  NUMBER);      
           -- Useful in ORDER BY clause when
                                       -- selecting from this table.


1.先建物化视图 。
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;

2.执行存储过程包，进行分析。
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');

3.执行以下语句，查看分析的结果。
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;

DBMS_MVIEW.EXPLAIN_MVIEW包的使用

DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码，分别是：
1.定义的查询
2.一个CREATE MATERIALIZED VIEW的语句
3.一个存在的物化视图

步骤如下：
1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句
SQL>@$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
如果未执行此语句，则有运行包的时候会报表不存在的错误。

2、先清空表中记录，便于分析信息的查看
TRUNCATE TABLE mv_capabilities_table;

3、执行包dbms_mview.explain_mview，其中，包里的参数可以是物化视图、查询语句、
CREATE物化视图语句。
begin
dbms_mview.explain_mview('SELECT a.id,b.name,a.rowid a_rowid,b.rowid b_rowid
   FROM comp1 a , comp2 b WHERE a.id=b.id');
end;
/

4、查看分析结果或错误报告
select MSGTXT from mv_capabilities_table where capability_name='REFRESH_FAST_AFTER_INSERT';
或
select * from mv_capabilities_table;
Tips:
(1)、物化视图在判断是否支持刷新时不支持SQL92的标准外联接的写法，需要改成Oracle自己的(+)的方式。
(2)、对于包含外联接的物化视图的快速刷新，无法高效的利用物化视图日志，因此即使是快速刷新，
性能也可能很差。

下面使用物化视图
首先要建表$ORACLE_HOME\RDBMS\ADMIN\utlxmv.sql;
utlxmv.sql的内容如下：

CREATE TABLE MV_CAPABILITIES_TABLE
   (STATEMENT_ID         VARCHAR(30),  -- Client-supplied unique statement identifier
    MVOWNER              VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
    MVNAME               VARCHAR(30),  -- NULL for SELECT based EXPLAIN_MVIEW
    CAPABILITY_NAME      VARCHAR(30),  -- A descriptive name of the particular
                                       -- capability:
                                       -- REWRITE
                                       --   Can do at least full text match
                                       --   rewrite
                                       -- REWRITE_PARTIAL_TEXT_MATCH
                                       --   Can do at leat full and partial
                                       --   text match rewrite
                                       -- REWRITE_GENERAL
                                       --   Can do all forms of rewrite
                                       -- REFRESH
                                       --   Can do at least complete refresh
                                       -- REFRESH_FROM_LOG_AFTER_INSERT
                                       --   Can do fast refresh from an mv log
                                       --   or change capture table at least
                                       --   when update operations are
                                       --   restricted to INSERT
                                       -- REFRESH_FROM_LOG_AFTER_ANY
                                       --   can do fast refresh from an mv log
                                       --   or change capture table after any
                                       --   combination of updates
                                       -- PCT
                                       --   Can do Enhanced Update Tracking on
                                       --   the table named in the RELATED_NAME
                                       --   column.  EUT is needed for fast
                                       --   refresh after partitioned
                                       --   maintenance operations on the table
                                       --   named in the RELATED_NAME column
                                       --   and to do non-stale tolerated
                                       --   rewrite when the mv is partially
                                       --   stale with respect to the table
                                       --   named in the RELATED_NAME column.
                                       --   EUT can also sometimes enable fast
                                       --   refresh of updates to the table
                                       --   named in the RELATED_NAME column
                                       --   when fast refresh from an mv log
                                       --   or change capture table is not
                                       --   possilbe.
    POSSIBLE             CHARACTER(1), -- T = capability is possible
                                       -- F = capability is not possible
    RELATED_TEXT         VARCHAR(2000),-- Owner.table.column, alias name, etc.
                                       -- related to this message.  The
                                       -- specific meaning of this column
                                       -- depends on the MSGNO column.  See
                                       -- the documentation for
                                       -- DBMS_MVIEW.EXPLAIN_MVIEW() for details
    RELATED_NUM          NUMBER,       -- When there is a numeric value
                                       -- associated with a row, it goes here.
                                       -- The specific meaning of this column
                                       -- depends on the MSGNO column.  See
                                       -- the documentation for
                                       -- DBMS_MVIEW.EXPLAIN_MVIEW() for details
    MSGNO                INTEGER,      -- When available, QSM message #
                                       -- explaining why not possible or more
                                       -- details when enabled.
    MSGTXT               VARCHAR(2000),-- Text associated with MSGNO.
    SEQ                  NUMBER);      
           -- Useful in ORDER BY clause when
                                       -- selecting from this table.


1.先建物化视图 。
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;

2.执行存储过程包，进行分析。
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');

3.执行以下语句，查看分析的结果。
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 or join dependent expression in
PCT_TABLE                      N CUSTOMER         relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT      N SH.SALES         the detail table does not have a materialized view log
REFRESH_FAST_AFTER_INSERT      N SH.CUSTO         the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N SUM(AMOU         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
CAPABILITY_NAME                P REL_TEXT         MSGTXT
------------------------------ - ---------------- ------------------------------------------------------------
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_PCT               N                  PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N                  general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE              N SALES            no partition key or PMARKER in select list
PCT_TABLE_REWRITE              N CUSTOMER         relation is not a partitioned table
20 rows selected.