物化视图有两种不同的刷新方式，其中的 FAST REFRESH 对于数据仓库型的数据库相当于有用，它只将上一次刷新之后修改的数据刷新到本地。

       要使用 FAST REFRESH 必须在 MASTER 表上建立物化视图日志，用于记录对 MASTER 表的修改。Oracle 用两种方式来定位被修改的行，一种是 ROWID，一种是 Primary Key。在 Oracle 8 之前，只有 ROWID 这种方式被支持，从 Oracle 8 开始 Primary Key 方式开始被支持，并成为了默认方式。而为了向前兼容，ROWID 的方式也被保留。下面看看两种不同类型物化视图在创建过程中都做了些什么，有什么不同。

基于 ROWID 的物化视图

SQL> create table skytest(a number primary key);

Table created.

SQL> create materialized view log on skytest with rowid;

Materialized view log created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_SKYTEST                  TABLE
SKYTEST                        TABLE

SQL> create materialized view MV_SKYTEST
  2  REFRESH FAST
  3  as
  4  select * from skytest;
select * from skytest
              *
ERROR at line 4:
ORA-23415: snapshot log for "ORACLE"."SKYTEST" does not record the primary key

Oracle 8 开始默认使用 Primary Key 方式。

SQL> create materialized view MV_SKYTEST
  2  REFRESH FAST
  3  with rowid
  4  as
  5  select * from skytest;

Materialized view created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
I_SNAP$_MV_SKYTEST             INDEX
MLOG$_SKYTEST                  TABLE
MV_SKYTEST                     TABLE
MV_SKYTEST                     UNDEFINED
SKYTEST                        TABLE

        可以看到除了多了物化视图 MV_SKYTEST 之外，还多了一个索引 I_SNAP$_MV_SKYTEST 和一个表 MLOG$_SKYTEST

SQL> desc MLOG$_SKYTEST
 Name                                        Null?    Type
 ------------------------------------------- -------- -------------------
 M_ROW$$                                              VARCHAR2(255)
 SNAPTIME$$                                           DATE
 DMLTYPE$$                                            VARCHAR2(1)
 OLD_NEW$$                                            VARCHAR2(1)
 CHANGE_VECTOR$$                                      RAW(255)

       MLOG$_SKYTEST 表中记录了主表上修改的记录，M_ROW$$ 保存主表上修改行的 ROWID，这样可以定位修改的行。

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS,STATUS,GENERATED from dba_indexes
  2  where index_name='I_SNAP$_MV_SKYTEST';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES STATUS   G
------------------------------ --------------------------- ------------------------------ --------- -------- -
I_SNAP$_MV_SKYTEST             NORMAL                      MV_SKYTEST                     UNIQUE    VALID    N

SQL> select table_name,index_name,column_name from dba_ind_columns
  2  where index_name='I_SNAP$_MV_SKYTEST';

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
MV_SKYTEST                     I_SNAP$_MV_SKYTEST             M_ROW$$

SQL> select table_name,column_name from dba_tab_columns
  2  where table_name='MV_SKYTEST';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
MV_SKYTEST                     A

       I_SNAP$_MV_SKYTEST 是建立在物化视图 MV_SKYTEST 中 M_ROW$$ 隐藏列上的唯一索引，该列正是保存了主表上对应行的 ROWID 值，建立该索引应该是为了提高刷新的性能。

       另外一个类型为 UNDEFINED，名字和物化视图一样的东东不知道是干吗的。

基于 Primary Key 的物化视图

SQL> drop materialized view MV_SKYTEST;

Materialized view dropped.

SQL> drop materialized view log on SKYTEST;

Materialized view log dropped.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
SKYTEST                        TABLE

SQL> create materialized view log on SKYTEST;

Materialized view log created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_SKYTEST                  TABLE
RUPD$_SKYTEST                  TABLE
SKYTEST                        TABLE

SQL> create materialized view MV_SKYTEST
  2  REFRESH FAST
  3  as
  4  select * from skytest;

Materialized view created.

SQL> select object_name,object_type from user_objects
  2  where object_name like '%SKYTEST%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MLOG$_SKYTEST                  TABLE
MV_SKYTEST                     TABLE
MV_SKYTEST                     UNDEFINED
RUPD$_SKYTEST                  TABLE
SKYTEST                        TABLE

SQL> desc MLOG$_SKYTEST
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 A                                                                                            NUMBER
 SNAPTIME$$                                                                                   DATE
 DMLTYPE$$                                                                                    VARCHAR2(1)
 OLD_NEW$$                                                                                    VARCHAR2(1)
 CHANGE_VECTOR$$                                                                              RAW(255)

SQL> desc RUPD$_SKYTEST
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 A                                                                                            NUMBER
 DMLTYPE$$                                                                                    VARCHAR2(1)
 SNAPID                                                                                       NUMBER(38)
 CHANGE_VECTOR$$                                                                              RAW(255)

       此时 mlog 中保存了 Primary Key。RUPD$_ 开头的表用于可更新的基于 Primary Key 的物化视图。

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS,STATUS,GENERATED from dba_indexes
  2  where table_name in ('SKYTEST','MV_SKYTEST'); 

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENES STATUS   G
------------------------------ --------------------------- ------------------------------ --------- -------- -
SYS_C001221                    NORMAL                      SKYTEST                        UNIQUE    VALID    Y
SYS_C001224                    NORMAL                      MV_SKYTEST                     UNIQUE    VALID    Y

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,DEFERRABLE,DEFERRED,GENERATED from dba_constraints
  2  where table_name in ('SKYTEST','MV_SKYTEST');

CONSTRAINT_NAME                C DEFERRABLE     DEFERRED  GENERATED
------------------------------ - -------------- --------- --------------
SYS_C001221                    P NOT DEFERRABLE IMMEDIATE GENERATED NAME
SYS_C001224                    P NOT DEFERRABLE IMMEDIATE GENERATED NAME

       同时，Oracle 也会在物化视图上建立相应的 Primary Key。不过对此我比较疑惑，由于刷新机制的缘故，Oracle 是不建议在物化视图上使用 Primary/Unique constraint 的，而建议使用 non unique index + deferred constraint 的形式，参见：Note:284101.1

       最后要说明的是，在创建物化视图日志时可以同时指定 WITH ROWID 和 WITH PRIMARY KEY。此时，在物化视图日志中两者都会保存，但最终物化视图是哪种类型的则是在创建物化视图时决定（只能指定 WITH ROWID 和 WITH PRIMARY KEY 其中一个）。如果所有的物化视图都是同一种类型的，那么建议在创建物化视图时也只指定那一种类型，可以减少空间消耗提高刷新性能。

参考：

Oracle materialized view mlog$ table

Differences Between Rowid & Primary Key Materialized Views
Note:254593.1

Fast Refresh Causing ORA-1/ORA-2291/ORA-2292, Complete Refresh Works Fine
Note:284101.1