一、 物化视图日志结构
Oracle 的物化视图的快速刷新要求必须建立物化视图日志，通过物化视图日志可以实现增量刷新功能。
物化视图日志的名称为MLOG$_后面跟基表的名称，如果表名的长度超过20位，则只取前20位，当截短后出现名称重复时，Oracle会自动在物化视图日志名称后面加上数字作为序号。
物化视图日志在建立时有多种选项：可以指定为ROWID、PRIMARY KEY和OBJECT ID几种类型，同时还可以指定SEQUENCE或明确指定列名。上面这些情况产生的物化视图日志的结构都不相同。
WITH 选项
任何物化视图都会包括的4列：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。
CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。
如果WITH后面跟了ROWID，则物化视图日志中会包含：M_ROW$$：用来存储发生变化的记录的ROWID。
如果WITH后面跟了PRIMARY KEY，则物化视图日志中会包含主键列。
如果WITH后面跟了OBJECT ID，则物化视图日志中会包含：SYS_NC_OID$：用来记录每个变化对象的对象ID。
如果WITH后面跟了SEQUENCE，则物化视图日子中会包含：SEQUENCE$$：给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
如果WITH后面跟了一个或多个COLUMN名称，则物化视图日志中会包含这些列。使用with column将自动添加主键,不能再添加primary key选项否则报如下错误:
SQL> create materialized view log on emp with(ename),primary key ;
create materialized view log on emp with(ename),primary key
ORA-00922: 选项缺失或无效
SQL> create materialized view log on emp with(ename) ;
Materialized view log created
OBJECT ID 仅针对object table
NEW VALUES选项
设置 including new values物化视图日志将旧值和新值,update将产生两条记录
二、 通过dbms_mview.explain_mview反向退出物化视图日志需要以上哪些选项
dbms_mview.explain_mview能帮助确认哪些是对物化视图是可行的哪些是不可行的，比如像物化视图是否可以增量刷新。增量刷新还需要什么条件,比如物化视图日志需要添加哪些选项。
dbms_mview.explain_mview支持查询语句,物化视图视图创建语句和物化视图,对于复杂的查询语句,可以先以BUILD DEFERRED方式创建，再使用dbms_mview.explain_mview进行分析
SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql
Table created.
SQL> desc mv_capabilities_table
Name Type Nullable Default Comments
--------------- -------------- -------- ------- --------
STATEMENT_ID VARCHAR2(30) Y
MVOWNER VARCHAR2(30) Y
MVNAME VARCHAR2(30) Y
CAPABILITY_NAME VARCHAR2(30) Y
POSSIBLE CHAR(1) Y
RELATED_TEXT VARCHAR2(2000) Y
RELATED_NUM NUMBER Y
MSGNO INTEGER Y
MSGTXT VARCHAR2(2000) Y
SEQ NUMBER Y
SQL> exec dbms_mview.explain_mview('MV_EMP');
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from mv_capabilities_table;
SQL> select * from MV_CAPABILITIES_TABLE ;
STATEMENT_ID MVOWNER MVNAME CAPABILITY_NAME POSSIBLE RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- -------------------------------------------------------------------------------- ----------- --------------------------------------- -------------------------------------------------------------------------------- ----------
EOSADMIN MV_TCHOSPDAYDETAILH PCT Y 1
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_COMPLETE Y 1002
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST N 2003
EOSADMIN MV_TCHOSPDAYDETAILH REWRITE N 3004
EOSADMIN MV_TCHOSPDAYDETAILH PCT_TABLE Y TCHOSPDAYDETAILH 434 4005
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_INSERT N EOSADMIN.TCHOSPDAYDETAILH 2081 mv log does not have all necessary columns 5006
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_INSERT N 2164 the materialized view is BUILD DEFERRED 5007
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N FSCALESELFEE 393 2143 SUM(expr) without COUNT(expr) 6008
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N FOVERPRICESELFEE 343 2143 SUM(expr) without COUNT(expr) 6009
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N SELFPAYMONEY 227 2143 SUM(expr) without COUNT(expr) 6010
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N TOTALMONEY 193 2143 SUM(expr) without COUNT(expr) 6011
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N SUMAMOUNT 162 2143 SUM(expr) without COUNT(expr) 6012
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled 6013
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr) 6014
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ANY_DML N EOSADMIN.TCHOSPDAYDETAILH 2165 mv log does not have sequence # 7015
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 7016
EOSADMIN MV_TCHOSPDAYDETAILH REFRESH_FAST_PCT N 2164 the materialized view is BUILD DEFERRED 8017
EOSADMIN MV_TCHOSPDAYDETAILH REWRITE_FULL_TEXT_MATCH N 2164 the materialized view is BUILD DEFERRED 9018
EOSADMIN MV_TCHOSPDAYDETAILH REWRITE_PARTIAL_TEXT_MATCH N 2164 the materialized view is BUILD DEFERRED 10019
EOSADMIN MV_TCHOSPDAYDETAILH REWRITE_GENERAL N 2164 the materialized view is BUILD DEFERRED 11020
STATEMENT_ID MVOWNER MVNAME CAPABILITY_NAME POSSIBLE RELATED_TEXT RELATED_NUM MSGNO MSGTXT SEQ
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- -------------------------------------------------------------------------------- ----------- --------------------------------------- -------------------------------------------------------------------------------- ----------
EOSADMIN MV_TCHOSPDAYDETAILH REWRITE_PCT N 2164 the materialized view is BUILD DEFERRED 12021
EOSADMIN MV_TCHOSPDAYDETAILH PCT_TABLE_REWRITE Y TCHOSPDAYDETAILH 434 13022
22 rows selected
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15747463/viewspace-1182449/，如需转载，请注明出处，否则将追究法律责任。
