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  *  from t4 ;
Materialized   view   created .

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

-- 添加些测试数据
USER @ orcl >  insert   into   t4   select   rownum ,  object_name   from all_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   WHERE TABLE_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          371 I_SUMAGG $ _1
AAAIQlAAIAAAgVMAAB               AAAIQgAAIAAAgUgACY          372 SUMJOIN $
AAAIQlAAIAAAgVMAAC               AAAIQgAAIAAAgUgACZ          373 I_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   FROM DBA_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   last refresh
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比通常要大些。

参考资料

• Differences Between Rowid & Primary Key Materialized Views 
  http://www.heysky.net/archives/2007/11/diffs_between_rowid_pk_mviews.html
• Differences Between Rowid & Primary Key Materialized Views 
  Note: 254593.1