DBMS_MVIEW.EXPLAIN_MVIEW包的使用

DBMS_MVIEW.EXPLAIN_MVIEW能分析三种不同的物化视图代码，分别是：

1.定义的查询

2.一个CREATE MATERIALIZED VIEW的语句

3.一个存在的物化视图

步骤如下：

1、提前执行dbms_mview.explain_mview用到的表mv_capabilities_table的建表语句

SQL>@F:\oracle\product\10.2.0\db_1\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;