oracle 物化视图 -循序渐进MView（三） 基于ROWID的MView

http://www.dbabeta.com/2009/mview_step_by_step_03.html

前言

基于ROWID的MView

基于ROWID MView的创建

基于ROWID MView的快速刷新

基于ROWID MView的MView Log结构

怎么把基于ROWID的MView转换成基于PK的MView

转换基于ROWID的MView到基于PK（全刷型）

转换基于ROWID的MView到基于PK（非全刷型）

参考资料

---

前言

前面我们已经说了MView的一些基本知识，我们举得例子都是基于PK的MView，Oracle还支持其他方式的MView，基于ROWID的MView就是其中的一种。在这一部分将讨论如何建立基于ROWID的MView，同时分析下MView Log的结构。

基于ROWID的MView

基于ROWID MView的创建

还是先用一个例子开始：

-- 建一个没有PK的表TT
USER@orcl> create table T4 (a int, b varchar(40));
Table created.
 
-- 建立一个MView，这是Oracle报错了
USER@orcl> create materialized view mvt4 as select * from t4;
create materialized view mvt4 as select * from t4
                                                *
ERROR at line 1:
ORA-12014: table 'T4' does not contain a primary key constraint

默认情况下Oracle是按照基于PK的方式来建立MView的，上面我们建立的测试表是没有PK的，这样Oracle就开始报错了，解决的方法也很简单：在建立MView的时候指定REFRESH WITH ROWID参数，如下

USER@orcl> create snapshot mvt4 refresh with rowid as select * fromt4;
Materialized view created.

接下来我们往里面增加些数据并测试下：

-- 添加些测试数据
USER@orcl> insert into t4 select rownum, object_name fromall_objects;
11501 rows created.
 
USER@orcl> commit;
Commit complete.
 
-- 做一次刷新，OK是没有问题的
USER@orcl> exec dbms_mview.refresh('mvt4', 'c');
PL/SQL procedure successfully completed.

基于ROWID MView的快速刷新

为了保证可以快速刷新，我们还需要给表TT建立MView Log，同样建立MView Log的时候我们一样是要制定WITH ROWID参数的。

USER@orcl> create snapshot log on t4 with rowid;
Materialized view log created.

现在做一下快速刷新的测试

USER@orcl> update t4 set b=upper(b) where rownum<5;
4 rows updated.

快速刷新没有问题

USER@orcl> exec dbms_mview.refresh('mvt4','F');
PL/SQL procedure successfully completed.

基于ROWID MView的MView Log结构

我们先看一下表T4的MView Log的结构

USER@orcl> desc mlog$_t4;
 Name             Null?    Type
 ---------------- -------- --------------
 M_ROW$$                   VARCHAR2(255)
 SNAPTIME$$                DATE
 DMLTYPE$$                 VARCHAR2(1)
 OLD_NEW$$                 VARCHAR2(1)
 CHANGE_VECTOR$$           RAW(255)

从结构中可以看出除了 M_ROW$$列之外其他的和基于PK的MView都是一样的，在这里的M_ROW$$保存的基表里面被操作行的ROWID，作用和PK是一样的，在刷新MView的时候用来定位基表以及MView表中的数据列。因为M_ROW$$记录的是基表的行的ROWID，那在MView表中对应行的ROWID肯定是和基表的不一样的，这点和PK是不一样的，PK在那儿都是一样的，为了使MView在刷新是利用MView Log中的M_ROW$$能找到MView表中的对应行，Oracle在MView表中加入了一个隐藏列，这个列从DBA_TAB_COLUMNS里面是看不到的，要从SYS.COL$里面才能看到，下面我们把这个列查出来看看

-- 从DBA_TAB_COLUMNS里面只能看到两个列
USER@orcl> SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERETABLE_NAME='MVT4';
COLUMN_NAME
------------------------------
A
B
 
-- 从SYS.COL$里面就能看到我们的隐藏列M_ROW$$了
USER@orcl> SELECT NAME FROM SYS.COL$ WHERE OBJ# IN (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'MVT4');
NAME
--------------------
A
B
M_ROW$$

下面来验证一下这个隐藏列中的数据，先修改下表T4的数据

USER@orcl> update t4 set b=upper(b) where rownum<5;
4 rows updated.

接下来我们对比下表T4中被修改的数据的ROWID，MView Log MLOG$_T4中的M_ROW$$列以及MView MVT4中的M_ROW$$数据

USER@orcl> select rowid, a, b from t4 where rownum<5;
ROWID                       A B
------------------ ---------- ----------------------------------------
AAAIQlAAIAAAgVMAAA        371 I_SUMAGG$_1
AAAIQlAAIAAAgVMAAB        372 SUMJOIN$
AAAIQlAAIAAAgVMAAC        373 I_SUMJOIN$_1
AAAIQlAAIAAAgVMAAD        374 SUMDEP$
 
USER@orcl> select * from mlog$_t4;
M_ROW$$                        SNAPTIME$$          D O CHANGE_VEC
------------------------------ ------------------- - - ----------
AAAIQlAAIAAAgVMAAA             4000-01-01 00:00:00 U U 04
AAAIQlAAIAAAgVMAAB             4000-01-01 00:00:00 U U 04
AAAIQlAAIAAAgVMAAC             4000-01-01 00:00:00 U U 04
AAAIQlAAIAAAgVMAAD             4000-01-01 00:00:00 U U 04
 
USER@orcl> select M_ROW$$, rowid, a, b from mvt4 where M_ROW$$ IN(SELECT M_ROW$$ FROM mlog$_t4);
M_ROW$$                        ROWID                       A B
------------------------------ ------------------ ---------- ----------------------------------------
AAAIQlAAIAAAgVMAAA             AAAIQgAAIAAAgUgACX        371I_SUMAGG$_1
AAAIQlAAIAAAgVMAAB             AAAIQgAAIAAAgUgACY        372SUMJOIN$
AAAIQlAAIAAAgVMAAC             AAAIQgAAIAAAgUgACZ        373I_SUMJOIN$_1
AAAIQlAAIAAAgVMAAD             AAAIQgAAIAAAgUgACa        374 SUMDEP$

通过实验可以看出来MVT4中的隐藏列M_ROW$$起的作用和PK是一样的。同时为了保证刷新速度，Oracle还在这一列上面建立了一个索引，索引的命名方式为I_SNAP$_mview_name，这个我们可以从DBA_INDEXES和DBA_IND_COLUMNS里面查到：

USER@orcl> SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS,
    STATUS,GENERATED FROM DBA_INDEXES WHERE TABLE_NAME='MVT4';
INDEX_NAME    INDEX_TYPE  TABLE_NAME  UNIQUENES STATUS   G
------------- ----------- ----------- --------- -------- -
I_SNAP$_MVT4  NORMAL      MVT4        UNIQUE    VALID    N
 
USER@orcl> SELECT INDEX_NAME,TABLE_NAME,COLUMN_NAME FROMDBA_IND_COLUMNS WHERE INDEX_NAME='I_SNAP$_MVT4';
INDEX_NAME    TABLE_NAME  COLUMN_NAME
------------- ----------- -----------
I_SNAP$_MVT4  MVT4        M_ROW$$

由于MView Log使用的是ROWID来进行操作，因此当基表的ROWID被改变以后将会导致MView无法快速刷新，发生这种情况只有做全刷了。

USER@orcl> alter table t4 move;
Table altered.
 
USER@orcl> exec dbms_mview.refresh('mvt4','f');
BEGIN dbms_mview.refresh('mvt4','f'); END;
 
*
ERROR at line 1:
ORA-12034: materialized view log on "USER"."T4" younger than lastrefresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1
 
 
USER@orcl> exec dbms_mview.refresh('mvt4','c');
PL/SQL procedure successfully completed.

怎么把基于ROWID的MView转换成基于PK的MView

有时候我们会要求将基于ROWID的MView转换成基于PK的MView，为了达到这个目的有两种方法，第一种方法删除MView Log然后在修改MView属性之后重建MView Log，这种方法要求对MView进行全部刷新；另外一种方法是通过修改MView Log来进行，这种方法可以做到不全刷MView，不过只能在8i以后的版本执行，当然这在现在不是个问题。

转换基于ROWID的MView到基于PK（全刷型）

这里我们先将现有的MView Log删除，然后在对MView做全刷。

首先在将基表上的MView Log删除，然后在使用WITH PRIMARY KEY重新建立MView Log

USER@orcl> alter table t4 add constraint pk_t4 primary key (a);
Table altered.
 
USER@orcl> drop materialized view log on t4;
Materialized view log dropped.
 
USER@orcl> create materialized view log on t4 with primary key;
Materialized view log created.

然后修改我们的MView，完了之后做一次全刷

USER@orcl> alter materialized view mvt4 refresh with primary key;
Materialized view altered.
 
USER@orcl> exec dbms_mview.refresh('mvt4','c');
PL/SQL procedure successfully completed.

现在我们的转换工作已经完成，以后就可以对MView进行快速刷新了

转换基于ROWID的MView到基于PK（非全刷型）

做这种方式的刷新需要保证我们的基表和MView都跑在8i或者以后的版本上。

首先我们修改我们的MView Log，增加Primary Key进去。

USER@orcl> alter materialized view log on t4 add primary key;
Materialized view log altered.

接下来我们对基表的所有的MView都做一次快速刷新。

注意：这个步骤是非常必要的，目的是确保在修改完MView Log之后我们的MView快速刷新能正常工作，否则的话将会出现ORA-12034错误并要求你做全刷，这样就全功尽弃了。

USER@orcl> exec dbms_mview.refresh('mvt4','f');
PL/SQL procedure successfully completed.

然后我们再去修改MView，完了之后可以做快速刷新了

USER@orcl> alter materialized view mvt4 refresh with primary key;
Materialized view altered.
 
USER@orcl> exec dbms_mview.refresh('mvt4','f');
PL/SQL procedure successfully completed.

这种方法还有一个遗留问题就是虽然我们现在不需要ROWID来进行刷新了，但是MView Log还是照样会把ROWID记录在里面的，这个会使MView比通常要大些。