DBMS_MVIEW.EXPLAIN_MVIEW

admin

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

1.   1.定义的查询
   
2.   2.一个CREATE MATERIALIZED VIEW的语句
   
3.   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物化视图语句。

1. SYS@ora19> EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('ZHYU.MV_T');

复制代码

4、查看分析结果或错误报告

1. SELECT capability_name, possible, SUBSTR(related_text,1,8)
   
2.   AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
   
3.   FROM MV_CAPABILITIES_TABLE
   
4. ORDER BY seq;
   
5. CAPABILITY_NAME            P REL_TEXT              MSGTXT
   
6. ------------------------------ - -------------------------------- ------------------------------------------------------------
   
7. PCT                   N
   
8. REFRESH_COMPLETE           Y
   
9. REFRESH_FAST               Y
   
10. REWRITE                N
    
11. PCT_TABLE               N T                  relation is not a partitioned table
    
12. REFRESH_FAST_AFTER_INSERT      Y
    
13. REFRESH_FAST_AFTER_ONETAB_DML  Y
    
14. REFRESH_FAST_AFTER_ANY_DML     Y
    
15. REFRESH_FAST_PCT           N                  PCT is not possible on any of the detail tables in the mater
    
16. REWRITE_FULL_TEXT_MATCH        N                  query rewrite is disabled on the materialized view
    
17. REWRITE_PARTIAL_TEXT_MATCH     N                  query rewrite is disabled on the materialized view
    
18. REWRITE_GENERAL            N                  query rewrite is disabled on the materialized view
    
19. REWRITE_PCT               N                  general rewrite is not possible or PCT is not possible on an
    
20. PCT_TABLE_REWRITE           N T                  relation is not a partitioned table
    
21. 
    
22. 14 rows selected.
    
23. 
    

复制代码

(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;