MVIEW的刷新也是一个比较难的话题, 尤其是遇到比较复杂的情况下, 如何实现增量刷新, 为什么现在的MVIEW不能实现增量刷新, 一直是我当初在实施MVIEW时遇到的最大问题, 在Oracle中也提供了一个过程可用于分析MVIEW, 这个过程在DBMS_MVIEW这个包中, 过程名为EXPLAIN_MVIEW. 对这个过程有所了解可以帮助你更好地实现MVIEW的应用. 在输出的信息中包括了是否可以实现增量刷新, 同时也列出了这个实体化视图上支持什么样的查询重写(文字匹配, 或一般函义上的重写).
要使用这个功能, 需要建一个名称为MV_CAPABILITIES_TABLE的表, 可以调用@?/rdbms/admin/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.
大家应当好好看一下CAPABILITY_NAME这个列上的说明部份, 就可以对这个过程的功能有所了解了, 下面来看一下用法
PROCEDURE EXPLAIN_MVIEW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MV VARCHAR2 IN
STMT_ID VARCHAR2 IN DEFAULT
PROCEDURE EXPLAIN_MVIEW
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MV VARCHAR2 IN
MSG_ARRAY EXPLAINMVARRAYTYPE IN/OUT
其中第一个参数可以是现有的MVIEW的名称, 也可以是将要创建的MIVEW的SQL语句部份.
SQL> exec dbms_mview.explain_mview('flou.MV_TEST_REWRITE');
PL/SQL procedure successfully completed.
SQL> select seq,CAPABILITY_NAME,POSSIBLE from MV_CAPABILITIES_TABLE order by seq;
1 PCT N
1002 REFRESH_COMPLETE Y
2003 REFRESH_FAST N
3004 REWRITE Y
4005 PCT_TABLE N
5006 REFRESH_FAST_AFTER_INSERT N
6007 REFRESH_FAST_AFTER_ONETAB_DML N
7008 REFRESH_FAST_AFTER_ANY_DML N
8009 REFRESH_FAST_PCT N
9010 REWRITE_FULL_TEXT_MATCH Y
10011 REWRITE_PARTIAL_TEXT_MATCH Y
11012 REWRITE_GENERAL Y
12013 REWRITE_PCT N13 rows selected.
SQL> select seq, msgtxt from MV_CAPABILITIES_TABLE order by seq;
1
1002
2003
3004
4005 relation is not a partitioned table
5006 the detail table does not have a materialized view log
6007 see the reason why REFRESH_FAST_AFTER_INSERT is disabled
7008 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
8009 PCT is not possible on any of the detail tables in the mater
ialized view9010
10011
11012
12013 general rewrite is not possible and PCT is not possible on a
ny of the detail tables
13 rows selected.
现在大家知道了这个功能, 就当去试试.