OCM考试中的物化视图的实现

qq489498494

　　Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle如何通过一个物化视图日志就可以支持多个物化视图的快速刷新呢，本文简单的描述一下刷新的原理。
　　首先，看一下物化视图的结构：
　　SQL> create table t (id number, name varchar2(30), num number);
　　表已创建。
　　SQL> create materialized view log on t with rowid, sequence (id, name) including new values ;
　　实体化视图日志已创建。
　　SQL> desc mlog$_t
名称                                     是否为空? 类型
---------------------------------------- -------- ------------
ID                                                NUMBER
NAME                                              VARCHAR2(30)
M_ROW$$                                           VARCHAR2(255)
SEQUENCE$$                                        NUMBER
SNAPTIME$$                                        DATE
DMLTYPE$$                                         VARCHAR2(1)
OLD_NEW$$                                         VARCHAR2(1)
CHANGE_VECTOR$$                                   RAW(255)
　　
ID和NAME是建立物化视图日志时指定的基表中的列，它们记录每次DML操作对应的ID和NAME的值。
　　M_ROW$$保存基表的ROWID信息，根据M_ROW$$中的信息可以定位到发生DML操作的记录。
SEQUENCE$$根据DML操作发生的顺序记录序列的编号，当刷新时，根据SEQUENCE$$中的顺序就可以和基表中的执行顺序保持一致。
　　SNAPTIME$$列记录了刷新操作的时间。
　　DMLTYPE$$的记录值I、U和D，表示操作是INSERT、UPDATE还是DELETE。
　　OLD_NEW$$表示物化视图日志中保存的信息是DML操作之前的值（旧值）还是DML操作之后的值（新值）。除了O和N这两种类型外，对于UPDATE操作，还可能表示为U。
　　CHANGE_VECTOR$$记录DML操作发生在那个或那几个字段上。
　　根据上面的描述，可以发现，当刷新物化视图时，只需要根据SEQUENCE$$列给出的顺序，通过M_ROW$$定位到基表的记录，如果是UPDATE操作，通过CHANGE_VECTOR$$定位到字段，然后根据基表中的数据重复执行DML操作。
　　如果物化视图日志只针对一个物化视图，那么刷新过程就是这么简单，还需要做的不过是在刷新之后将物化视图日志清除掉。
　　但是，Oracle的物化视图日志是可以同时支持多个物化视图的快速刷新的，也就是说，物化视图在刷新时还必须判断哪些物化视图日志记录是当前物化视图刷新需要的，哪些是不需要的。而且，物化视图还必须确定，在刷新物化视图后，物化视图日志中哪些记录是需要清除的，哪些是不需要清除的。
　　回顾一下物化视图日志的结构，发现只剩下一个SHAPTIME$$列，那么Oracle如何仅通过这一列就完成了对多个物化视图的支持呢？下面建立一个小例子，通过例子来进行说明。
　　使用上文中建立的表和物化视图日志，下面对这个表建立三个快速刷新的物化视图。
　　
SQL> create materialized view mv_t_id refresh fast as
    select id, count(*) from t group by id;
　　实体化视图已创建。
　　SQL> create materialized view mv_t_name refresh fast as
     select name, count(*) from t group by name;
　　实体化视图已创建。
　　SQL> create materialized view mv_t_id_name refresh fast as
     select id, name, count(*) from t group by id, name;
　　实体化视图已创建。
　　SQL> insert into t values (1, 'a', 2);
　　SQL> insert into t values (1, 'b', 3);
　　SQL> insert into t values (2, 'a', 5);
　　SQL> insert into t values (3, 'b', 7);
　　SQL> update t set name = 'c' where id = 3;
　　SQL> delete t where id = 2;
　　SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
　　ID NAME       M_ROW$$            SNAPTIME$$          D
---------- ---------- ------------------ ------------------- -
         1 a          AAACJEAAFAAAAD4AAA 4000-01-01 00:00:00 I
         1 b          AAACJEAAFAAAAD4AAB 4000-01-01 00:00:00 I
         2 a          AAACJEAAFAAAAD4AAC 4000-01-01 00:00:00 I
         3 b          AAACJEAAFAAAAD4AAD 4000-01-01 00:00:00 I
         3 b          AAACJEAAFAAAAD4AAD 4000-01-01 00:00:00 U
         3 c          AAACJEAAFAAAAD4AAD 4000-01-01 00:00:00 U
         2 a          AAACJEAAFAAAAD4AAC 4000-01-01 00:00:00 D
　　
已选择7行。
　　当发生了DML操作后，物化视图日志中的SNAPTIME$$列保持的值是4000-01-01 00:00:00。这个值表示这条记录还没有被任何物化视图刷新过。第一个刷新这些记录的物化视图会将SNAPTIME$$的值更新为物化视图当前的刷新时间。
　　SQL> exec dbms_mview.refresh('MV_T_ID')
　　PL/SQL 过程已成功完成。
　　SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
　　ID NAME       M_ROW$$            SNAPTIME$$          D
---------- ---------- ------------------ ------------------- -
         1 a          AAACJEAAFAAAAD4AAA 2005-03-06 00:56:59 I
         1 b          AAACJEAAFAAAAD4AAB 2005-03-06 00:56:59 I
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         3 c          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 D
　　
已选择7行。
　　Oracle根据数据字典中的信息可以知道表T上建立了三个物化视图，因此，MV_T_ID刷新完之后，不会删除物化视图记录。
　　Oracle的数据字典中还保存着每个物化视图上次刷新的时间和当前的刷新状态。
　　SQL> select name, last_refresh from user_mview_refresh_times;
　　
NAME                           LAST_REFRESH
------------------------------ -------------------
MV_T_ID                        2005-03-06 00:56:59
MV_T_ID_NAME                   2005-03-06 00:46:09
MV_T_NAME                      2005-03-06 00:46:04
　　
SQL> select mview_name, last_refresh_date, staleness from user_mviews;
　　
MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------- -------------------
MV_T_ID                        2005-03-06 00:56:59 FRESH
MV_T_ID_NAME                   2005-03-06 00:46:09 NEEDS_COMPILE
MV_T_NAME                      2005-03-06 00:46:04 NEEDS_COMPILE
　　这些视图中记录了每个物化视图上次执行刷新操作的时间，并且给出每个物化视图中的数据是否是和基表同步的。由于MV_T_ID刚刚进行了刷新，因此状态是FRESH，而另外两个由于在刷新（建立）之后，基表又进行了DML操作，因此状态为NEEDS_COMPILE。如果这时对基表进行DML操作，则MV_T_ID的状态也会变为NEEDS_COMPILE。
　　SQL> insert into t values (4, 'd', 10);
　　已创建 1 行。
　　SQL> commit;
　　提交完成。
　　SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
　　
        ID NAME       M_ROW$$            SNAPTIME$$          D
---------- ---------- ------------------ ------------------- -
         1 a          AAACJEAAFAAAAD4AAA 2005-03-06 00:56:59 I
         1 b          AAACJEAAFAAAAD4AAB 2005-03-06 00:56:59 I
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         3 c          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 D
         4 d          AAACJEAAFAAAAD4AAE 4000-01-01 00:00:00 I
已选择8行。
　　SQL> select mview_name, last_refresh_date, staleness from user_mviews;
　　MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------- -------------------
MV_T_ID                        2005-03-06 00:56:59 NEEDS_COMPILE
MV_T_ID_NAME                   2005-03-06 00:46:09 NEEDS_COMPILE
MV_T_NAME                      2005-03-06 00:46:04 NEEDS_COMPILE
　　下面刷新物化视图MV_T_ID_NAME，刷新操作的判断依据是，只刷新SNAPTIME$$列大于当前物化视图的LAST_REFRESH_DATE的记录，由于物化视图日志中所有记录的SNAPTIME$$的值都比物化视图MV_T_ID_NAME上次刷新的时间点大，因此会刷新所有记录。对于SNAPTIME$$列的值是4000-01-01 00:00:00的记录，物化视图会把SNAPTIME$$列的值更新为当前刷新时间，对于那些已经被更新过的SNAPTIME$$列，则保持原值。
　　
SQL> exec dbms_mview.refresh('MV_T_ID_NAME')
　　PL/SQL 过程已成功完成。
　　
SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
　　
        ID NAME       M_ROW$$            SNAPTIME$$          D
---------- ---------- ------------------ ------------------- -
         1 a          AAACJEAAFAAAAD4AAA 2005-03-06 00:56:59 I
         1 b          AAACJEAAFAAAAD4AAB 2005-03-06 00:56:59 I
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         3 c          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 D
         4 d          AAACJEAAFAAAAD4AAE 2005-03-06 01:18:22 I
　　
已选择8行。
　　SQL> select mview_name, last_refresh_date, staleness from user_mviews;
　　MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------- -------------------
MV_T_ID                        2005-03-06 00:56:59 NEEDS_COMPILE
MV_T_ID_NAME                   2005-03-06 01:18:22 FRESH
MV_T_NAME                      2005-03-06 00:46:04 NEEDS_COMPILE
　　
如果这时再次刷新物化视图MV_T_ID，则只有ID=4的这条记录的SNAPTIME$$的时间点大于MV_T_ID上次刷新的时间点，因此，只刷新这一条记录，且不会改变SNAPTIME$$的值。
　　SQL> exec dbms_mview.refresh('MV_T_ID')
　　PL/SQL 过程已成功完成。
　　SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
　　
        ID NAME       M_ROW$$            SNAPTIME$$          D
---------- ---------- ------------------ ------------------- -
         1 a          AAACJEAAFAAAAD4AAA 2005-03-06 00:56:59 I
         1 b          AAACJEAAFAAAAD4AAB 2005-03-06 00:56:59 I
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 I
         3 b          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         3 c          AAACJEAAFAAAAD4AAD 2005-03-06 00:56:59 U
         2 a          AAACJEAAFAAAAD4AAC 2005-03-06 00:56:59 D
         4 d          AAACJEAAFAAAAD4AAE 2005-03-06 01:18:22 I
　　
已选择8行。
　　SQL> select mview_name, last_refresh_date, staleness from user_mviews;
　　MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------- -------------------
MV_T_ID                        2005-03-06 01:25:30 FRESH
MV_T_ID_NAME                   2005-03-06 01:18:22 FRESH
MV_T_NAME                      2005-03-06 00:46:04 NEEDS_COMPILE
　　到目前为止，还没有看到过物化视图日志的清除，其实每次进行完刷新，物化视图日志都会试图删除没有用的物化视图日志记录。物化视图日志记录的删除条件是删除那些SNAPTIME$$列小于等于基表所有物化视图的上次刷新时间。在上面的例子中，由于MV_T_NAME一直没有刷新，因此它的LAST_REFRESH_DATE比物化视图日志中所有记录的值都小，因此，一直没有发生物化视图日志记录清除的现象。
　　SQL> insert into t values (5, 'e', 2);
　　已创建 1 行。
　　SQL> commit;
　　提交完成。
　　SQL> exec dbms_mview.refresh('MV_T_NAME')
　　PL/SQL 过程已成功完成。
　　SQL> select id, name, m_row$$, snaptime$$, dmltype$$ from mlog$_t;
　　
        ID NAME       M_ROW$$            SNAPTIME$$          D
---------- ---------- ------------------ ------------------- -
         5 e          AAACJEAAFAAAAD4AAF 2005-03-06 01:31:33 I
　　SQL> select mview_name, last_refresh_date, staleness from user_mviews;
　　MVIEW_NAME                     LAST_REFRESH_DATE   STALENESS
------------------------------ ------------------- -------------------
MV_T_ID                        2005-03-06 01:25:30 NEEDS_COMPILE
MV_T_ID_NAME                   2005-03-06 01:18:22 NEEDS_COMPILE
MV_T_NAME                      2005-03-06 01:31:33 FRESH
　　物化视图MV_T_NAME刷新了物化视图中的每条记录，更新了ID=5的记录的SNAPTIME$$时间，并清除了其它所有物化视图日志记录。
　　最后，简单总结一下：
　　物化视图在刷新时，会刷新所有SNAPTIME$$大于本物化视图上次刷新时间的记录，并将所有是4000-01-01 00:00:00的记录更新为当前刷新时间。对于其他大于上次刷新时间的记录，只刷新不更改。这样，当刷新执行完以后，数据字典中记录当前物化视图的上次刷新时间为当前时刻，这保证了物化视图日志中目前所有的记录都小于或等于刷新时间。因此，每个物化视图只要刷新大于上次刷新时间的记录，且保证每次刷新后，所有记录的时间都小于等于上次刷新时间，那么无论有多少个物化视图，就可以互不影响的使用同一个物化视图日志进行快速刷新了。当物化视图刷新完之后，会清除那些SNAPTIME$$列小于所有物化视图的上次刷新时间的记录，而这些记录已经被所有的物化视图都刷新过了，保存在物化视图日志中已经没有意义了。
　　注意：如果要基于fast refresh方式进行物化视图的更新，且基表中有主键，则在建立物化视图LOG时要使用如下命令：
　　CREATE MATERIALIZED VIEW LOG ON emp WITH PRIMARY KEY, ROWID;
　　同时如果创建或者更新如下的物化视图，则在更新时会报错：
　　create materialized view emp_mv4
refresh fast on commit
as select deptno,sum(sal) from emp group by deptno;
或者
exec dbms_mview.refresh('EMP_MV4')；
　　ORA-32401: materialized view log on "SCOTT"."EMP" does not have new values
　　原因是采用主键列做物化视图log时，记录的MLOG$_EMP日志表中不保存重复的值，也就是说一有修改就立刻更新物化视图，一更新物化视图，则mlog$_emp表中以前的记录就会被删除不会保留。任何记录。
             版权声明：本文为博主原创文章，未经博主允许不得转载。