循序渐进Materialized Views.doc

對應網址：/category/oracle/materialized-view循序渐进MView（一） 什么是MView马齿苋发表评论阅读评论1,763 次查看前言几个术语的对应什么是MViewMView的基本概念一个简单的例子MView的作用MView的刷新MView刷新的分类指定刷新方式什么时候刷新MView的基本构成前言MView的两大应用方向 一是用于数据库的复制，这个在Oracle文档Advanced Replication中有详细的描述。 另一个是用于数据仓库，同样Oracle文档Oracle Database Data Warehousing Guide中也有详细的描述。本系列文章以实例的方式一步步的说是什么是MView、MView的结构、MView的基本工作过程，以及MView的管理方法。几个术语的对应基表指的是英文里面的Master Table和Master Materialized View，并不只是只一个表，而是创建MView的时候所需要用到的n个表或者是相关的上一级的MView。MView就是Materialized View了，物化视图。源数据库端Master Site和Master Materialized View Site，指的是基表所在的数据库MView端Materialized View Site，MView所在的数据库什么是MViewMView的基本概念MView中文名称为物化视图（Materialized View），相对于不同的视图来说，MView的不同之处在于MView的结果会保存在一个普通的数据表中，在对MView进行查询的时候不再会对创建MView的基表进行查询，而是直接查询MView对应的结果表，然后通过定期的刷新机制来更新MView表中的数据。一个简单的例子- 创建一个测试用的表TUSERorclcreatetablet(aint,bvarchar2(50),constraintpk_tprimarykey(a);Tablecreated.- 创建对应的MV名为MVTUSERorclcreatematerializedviewmvtasselect*fromt;Materializedviewcreated.- 现在往表里面插入一些个数据USERorclinsertintotselectrownum,object_namefromall_objects;11449rowscreated.USERorclcommit;Commitcomplete.- 现在我们看一下数据的情况USERorclselectcount(*)fromt;COUNT(*)- 11449- mvt还是没有数据的USERorclselectcount(*)frommvt;COUNT(*)- 0- 下面对mview做一次刷新看看USERorclexecdbms_mview.refresh(mvt);-在pl/sql中的command窗口執行PL/SQLproceduresuccessfullycompleted.- 现在能看到mv里面已经有数据了USERorclselectcount(*)frommvt;COUNT(*)- 11449- 然后我们在看一下表T和MVT的结构可以发现他们两个是一样的USERorcldesct;Name Null? Type- - -A NOTNULLNUMBER(38)B VARCHAR2(50)USERorcldescmvt;Name Null? Type- - -A NOTNULLNUMBER(38)B VARCHAR2(50)MView的作用从上面的例子可以看到我们利用MV创建了一个现有数据表的一个复制，同时通过刷新的方式将两个数据表的数据进行了同步。这个就是我们在复制中利用MView的最主要的用法，所不同的是在实际应用的环境中表T和MView MVT并不是在同一个机器上，而是分散在两个以上的机器上，同时基表也可能不止一个，可能存在多个。下面列举了MView在实际中的主要作用： 减轻网络负担：通过MV将数据从一个数据库分发到多个不同的数据库上，通过对多个数据库访问来减轻对单个数据库的网络负担。 搭建分发环境：通过从一个中央数据库将数据分发到多个节点数据库，达到分发数据的目的。 复制数据子集：MV可以进行行级/列级的筛选，这样可以复制需要的那一部分数据。 支持离线计算：MV不需要专用的数据库连接，用户可以按照自己的需求来复制所需要的那一部分数据。MView的刷新下面接着说说MView刷新这个事。MView里面的数据是不会和基表保持实时的同步的，它只是基表在某时时间点（刷新的时间点）的一个一致性的数据的镜像，因此，要保持MView尽可能的和基表同步的话就需要我们定期的对MView进行刷新。MView刷新的分类Oracle支持三种种方式的刷新：完全刷新和快速刷新以及强制刷新。完全刷新（complete refresh）对一个MView进行全部刷新的时候差不多是将MView重建了，在进行MView全部刷新的时候会现将MView中现有的数据删除（版本在10G或以上）或者TRUNCATE（版本低于9i），然后在根据创建MView时候的查询生成数据插入到MView中。对于多层的MView来说，当master MView全部刷新之后对应的下一级的MView也需要全部刷新，否则将会收到ORA-12034的错误。快速刷新（fast refresh）快速刷新是一种比完全刷新快的多的刷新方式，快速刷新只刷新自上次刷新以来修改的数据，因为快速刷新所要操作的数据量少，使用这种方法能大大的节省带宽.快速刷新要求在基表上面有MView Log，接着上面给出的例子，我们来给MVT做一个快速刷新。- 现在表T上面创建MView Log- 如果没有建立MView Log的话要进行快速刷新的话会报错的，大家可以试试USERorclcreatematerializedviewlogont;Materializedviewlogcreated.- 先看一下我们要修改的数据USERorclselect*fromtwherea=936; AB- - 936v_$fast_start_serversUSERorclselect*frommvtwherea=936; AB- - 936v_$fast_start_servers- 接着对表T数据进行一些修改USERorclupdatetsetb=upper(b)wherea=936;1rowupdated.- 现在T里面的这条数据变成了这样USERorclcommit;Commitcomplete.USERorclselect*fromtwherea=936; AB- - 936V_$FAST_START_SERVERS- 接下来就是进行快速刷新了USERorclexecdbms_mview.refresh(mvt,F);PL/SQLproceduresuccessfullycompleted.- 现在看MVT里面的结果USERorclselect*frommvtwherea=936; AB- - 936V_$FAST_START_SERVERS快速刷新最大的问题在于如果保证能进行快速刷新，这是对与复杂的查询来说是有些挑战的问题，以后会慢慢的探讨。强制刷新（force refresh）当进行强制刷新的时候系统会首先尝试进行快速刷新，如果快速刷新无法进行的时候系统将会进行完全刷新。其实就是一个快速刷新和完全刷新的结合体。指定刷新方式既然有那么多种的刷新方式那我们怎么指定他们呢？在Oracle中有两种方法来制定所用的刷新方式，第一种在上面我们已经看过了，就是在执行刷新MView语句的时候制定刷新方式，比如说execdbms_mview.refresh(mvt,F);用来指定对MView mvt进行快速刷新，将其中的”F”改成”C”就是指定对mvt进行完全刷新了。execdbms_mview.refresh(mvt,C);另外一种方法是直接执行execdbms_mview.refresh(mvt);就是不指定刷新的参数，这个时候MView的刷新方式将是根据创建时候由REFRESH语句指定的刷新方法来进行刷新了，REFRESH语句一共有下面几种使用方法refreshfast|complete|force . FAST: 采用增量刷新，只刷新自上次刷新以后进行的修改 COMPLETE: 对整个实体化视图进行完全的刷新 FORCE(默认): Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用Fast方式，否则采用Complete的方式，Force选项是默认选项什么时候刷新现在怎么刷新的问题解决了，接下来就是考虑我们什么时候进行刷新了。我们从上面已经知道我们需要定期的对MView进行刷新以保证基表和MView的数据同步，这个定期的方法就是使用job，同样我们有两种方法来创建刷新MView的job。使用DBMS_JOB包来创建- 提交一个JOB用来刷新MViewUSERorclvariablejob1number;USERorclexecdbms_job.submit(:job1,dbms_mview.refresh(user.mvt);,sysdate,sysdate+1/24);PL/SQLproceduresuccessfullycompleted.USERorclcommit;Commitcomplete.- 查看一下结果USERorclselectJOB,NEXT_DATE,NEXT_SEC,INTERVAL,WHATfromuser_jobs; JOBNEXT_DATE NEXT_SEC INTERVAL WHAT- - - - - 232009-01-1313:52:1413:52:14 sysdate+1/24 dbms_mview.refresh(user.mvt);使用这种方法相对下面的方法来说不同之处在于这种方法可以自己指定刷新的语句，这样灵活性相对高一些。在创建MView的时候指定REFRESH语句没错，还是REFRESH语句，这个语句的用法还是挺多的，下面列出这个语句的其他用法：refresh. startwithdate nextdate . START WITH: 第一次刷新时间 NEXT: 刷新时间间隔说明：指定上面两个选项的任意一个都将会在系统中产生一个新的JOB，用来对所建立的MV进行刷新，这个JOB可以从DBA_JOBS查到，同时删除MV之后该JOB也会被删除。下面我们看一个例子：- 创建一个MView，并指定刷新时间为sysdate和刷新间隔为一个小时USERorclcreatematerializedviewmvt2refreshstartwithsysdatenextsysdate+1/24asselect*fromt;Materializedviewcreated.- 现在我们看一下job- 请注意这里的WHAT那一栏中MView刷新的代码是不带刷新方式的，也就是说按照创建时候的刷新方式进行刷新USERorclselectJOB,NEXT_DATE,NEXT_SEC,INTERVAL,WHATfromuser_jobs;JOBNEXT_DATE NEXT_SECINTERVAL WHAT- - - - -212009-01-1313:20:2013:20:20sysdate+1/24dbms_refresh.refresh(USER.MVT2);另外这里所说的JOB定时刷新只是针对于单个MView来说的，而对于存在多个MView要刷新的时候我们就要开始考虑刷新组了，这个部分以后再慢慢说明。MView的基本构成说了这么多的MView的例子，那么MView的结构到底是怎么样的呢？我们先看一副Oracle给出的MView的结构图：MView架构图（来自Oracle Advance Replication）现在我们只对一些当前接触到的相关的对象进行说明，其他的部分将在后面的文章中陆续说明：必要组成部分 基表（MASTER TABLE）：这里说的基表不一定只有一个表，也有可能是一个复杂的查询，涉及很多的表，也可能是一个MView。 隐藏的MView对象：对象类型为Materialized View的一个对象，用来维护MView的创建信息。 MView表：MView所对应的数据表，这个表也就是一个普通的表，不同的就是这个表是和一个MView相关联的表。 基表的INDEX MView的INDEX：对以用PK建立的MView将会有一个与基表结果一样名字类似的索引，对以使用ROWID建立的MView将有一个以I_SNAP$_materialized_view_name格式命名的建立在ROWID列上的INDEX。当然你也可以根据需要自己再创建一些INDEX。对于需要快速刷新的物化视图 MView LOG：要使MView能够被快速刷新的话必须在基表上面创建MView Log，创建MView Log的语法为CREATE MATERIALIZED VIEW LOG ON master_name，MView Log表为Oracle默认以MLOG$_master_name的命名格式建立。 维护MLOG$_的内部触发器：在基表上面的所有DML操作都有一个在基表上的内部触发器记录到MLOG$_master_name中，这个触发器在USER_TRIGGERS是看不到的，不过能够用USER_INTERNAL_TRIGGERS看到。下面用一个例子来看一下这些个对象- 创建一个基表SQLCREATETABLETTT1(AINTPRIMARYKEY,BINT);Tablecreated.- 以TTT1为基表创建一个可更新的MViewSQLCREATESNAPSHOTMV_TTT1FORUPDATEASSELECT*FROMTTT1;Materializedviewcreated.- 创建MView LogSQLCREATEMATERIALIZEDVIEWLOGONTTT1;Materializedviewlogcreated.- 检查MView的对象SQLSELECTOBJECT_NAME,OBJECT_TYPEFROMALL_OBJECTSWHEREOBJECT_NAMELIKE%TTT1%;OBJECT_NAME OBJECT_TYPE- -TTT1 TABLEMV_TTT1 MATERIALIZEDVIEWMV_TTT1 TABLEMLOG$_TTT1 TABLEUSLOG$_MV_TTT1 TABLE- 查看MView相关的INDEXSQLSELECTINDEX_NAME,TABLE_NAMEFROMUSER_INDEXESWHERETABLE_NAMELIKE%TTT1%;INDEX_NAME TABLE_NAME- -PK_TTT11 MV_TTT1PK_TTT1 TTT1- 查看内部触发器SQLSELECT*FROMUSER_INTERNAL_TRIGGERSWHERETABLE_NAMELIKE%TTT1%;TABLE_NAME INTERNAL_TRIGGER_TY- -MV_TTT1 UPDATABLEMVIEWLOGTTT1 MVIEWLOG循序渐进MView（二） MView Log的结构与快速刷新马齿苋发表评论阅读评论985 次查看前言MView Log的结构刷新的过程完全刷新的过程快速刷新过程参考资料前言现在我们通过一些例子来说明一下MView Log的基本结构以及MView快速刷新的过程。在这一部分里面，我们还是利用上一部分提供的例子先建立一个MView，同时也创建该MView基表的MView Log。- 创建一个测试用的表TUSERorclcreatetablet(aint,bvarchar2(50),constraintpk_tprimarykey(a);Tablecreated.- 创建对应的MV名为MVTUSERorclcreatematerializedviewmvtasselect*fromt;Materializedviewcreated.- 现在往表里面插入一些个数据USERorclinsertintotselectrownum,object_namefromall_objects;11449rowscreated.USERorclcommit;Commitcomplete.- 下面对mview做一次刷新看看USERorclexecdbms_mview.refresh(mvt);PL/SQLproceduresuccessfullycompleted.- 创建MView LogUSERorclcreatematerializedviewlogont;Materializedviewlogcreated.- 接下来就是进行快速刷新了USERorclexecdbms_mview.refresh(mvt,F);PL/SQLproceduresuccessfullycompleted.MView Log的结构我们先看一下两个测试表的MView Log的结构：- 含有PK的MViewUSERorcldescmlog$_t;Name Null? Type- - -A NUMBERSNAPTIME$ DATEDMLTYPE$ VARCHAR2(1)OLD_NEW$ VARCHAR2(1)CHANGE_VECTOR$ RAW(255)现在我们挨个说一下各列的含义：A这个我们基表T的主键列，如果基表的主键是一个复合索引的话那这里也就存在多个和基表定义一样的列，当基表被修改以后，基表的主键就会被记录到这个列里面。SNAPTIME$用来记录MView刷新的时间，这个字段只有在一个基表对应一个以上的MView的时候才有意义，因为对于一个MView Log来说，只有当所有的MView都刷新完了以后才能把MView Log里面的记录删除，当一个MView刷新的时候，它会把此列置成该MView刷新的时候，在这个MView再次刷新的时候，那些上次刷新过的列就不用再次被刷新了。下面我们用一个例子来说明一下：- 在建立一个基于表T的MView MVT2USERorclcreatematerializedviewmvt2asselect*fromt;Materializedviewcreated.- 先做一次刷新USERorclexecdbms_mview.refresh(mvt2);PL/SQLproceduresuccessfullycompleted.- 现在对表T做一些修改USERorclupdatetsetb=upper(b)whererownumselect*frommlog$_t; ASNAPTIME$ DOCHANGE_VEC- - - - - 9364000-01-0100:00:00UU04 9374000-01-0100:00:00UU04 9384000-01-0100:00:00UU04 9394000-01-0100:00:00UU04- 现在我们刷新MVTUSERorclexecdbms_mview.refresh(mvt,f);PL/SQLproceduresuccessfullycompleted.- 我们可以看到SNAPTIME$时间变了USERorclselect*frommlog$_t; ASNAPTIME$ DOCHANGE_VEC- - - - - 9362009-02-1807:28:09UU04 9372009-02-1807:28:09UU04 9382009-02-1807:28:09UU04 9392009-02-1807:28:09UU04- 对比一下MVT的LAST_REFRESH_DATE，我们可以发现这个是一样的USERorclselectMVIEW_NAME,LAST_REFRESH_DATEfromdba_mviewswheremview_namein(MVT,MVT2);MVIEW_NAME LAST_REFRESH_DATE- -MVT 2009-02-1807:28:09MVT2 2009-02-1807:27:19DMLTYPE$用于表示DML操作类型，I表示INSERT，D表示DELETE，U表示UPDATE，这个我们可以从上面的例子里面得到验证。OLD_NEW$用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD）表示旧值，U表示UPDATE操作。CHANGE_VECTOR$表示修改矢量，用来表示被修改的是哪个或哪几个字段，用二进制的方式来保存修改列的结果。USERorclcreatetablettt(aint,bvarchar(40),cvarchar(40);Tablecreated.USERorclaltertabletttaddconstraintpk_tttprimarykey(a);Tablealtered.USERorclinsertintotttselectrownum,object_name,object_namefromall_objects;11488rowscreated.USERorclcommit;Commitcomplete.USERorclcreatesnapshotmvtttasselect*fromttt;Materializedviewcreated.USERorclcreatesnapshotlogonttt;Materializedviewlogcreated.USERorclcommit;Commitcomplete.USERorclupdatetttsetb=upper(b)whererownumupdatetttsetc=upper(c)whererownumupdatetttsetb=upper(b),c=upper(c)whererownumselect*frommlog$_ttt; ASNAPTIME$ DOCHANGE_VEC- - - - - 2534000-01-0100:00:00UU04 2544000-01-0100:00:00UU04 2554000-01-0100:00:00UU04 2564000-01-0100:00:00UU04 2534000-01-0100:00:00UU08 2544000-01-0100:00:00UU08 2554000-01-0100:00:00UU08 2564000-01-0100:00:00UU08 2534000-01-0100:00:00UU0C 2544000-01-0100:00:00UU0C 2554000-01-0100:00:00UU0C 2564000-01-0100:00:00UU0C从上面的例子我们可以看出，修改列B的CHANGE_VECTOR$是04(0100)，修改列C的CHANGE_VECTOR$值是08(1000)，同时修改这两个列就变成了0C(1100)。刷新的过程完全刷新的过程在Oracle 9i以及以前版本中，MView的完全刷新是先对数据库基表做一个truncate操作然后再将基表的数据全部插入到MView中，而Oracle 10g中，MView完全刷新之前并不对MView进行truncate的操作，取而代之的是delete操作，这个在操作大量的数据的时候会有很大的影响，这一点可以通过开trace来看到，这里就不在贴实验的具体过程了。快速刷新过程看了MView Log的结构之后我们很容易就能理解MView快速刷新的基本原理了：首先在对基表做update、delete、insert操作之后，隐藏的触发器会把基表的修改记录到MView Log中，在MView Log中基表的主键会被记录（仅对于基于PK的MView来说，其他类型的MView随后介绍），这个记录的pk会在MView做快速刷新的时候被用来定位被操作的数据行，同时还有一些其他的数据会被记录。在MView做快速刷新的时候，对于不同的操作语句会有一点点不一样的地方：insert操作通过MView Log记录的基表的主键数据以及DMLTYPE$字段我们知道那些行是行是新插入的，只要将这些新数据导入到MView中即可。delete操作同样通过MView Log记录的基表的主键数据以及DMLTYPE$字段我们知道那些行是行是被删除了的，只要将这些在基表被删除掉的数据在MView中删除掉即可。update操作通过MView Log记录的基表的主键，DMLTYPE$字段以及CHANGE_VECTOR$我们知道那些行的那些列进行了更新的操作，然后再依照基表的数据对MView中相应的列挨个进行刷新操作。下面我们利用上面的MVTTT进行一下update操作的实验，主要是验证update的时候Oracle是否只会update基表中被修改的列：首先我们建立一个对MView MVTTT中列C的触发器，在当C列被修改以后在一个新表中插入一条记录。- 创建一个记录表USERorclcreatetablettt_t(atimestampdefaultsysdate,bvarchar(10);Tablecreated.- 创建相应的触发器USERorcl CREATEORREPLACETRIGGERtri_mvttt BEFOREINSERTORDELETEORUPDATEOFcONmvttt FOREACHROW BEGIN INSERTINTOttt_tVALUES(SYSDATE,c); ENDtri_mvttt; /Triggercreated.现在我们对基表的列b做一次更新的操作，看看刷新MView以后表TTT_T中是否有数据USERorclupdatetttsetb=upper(b)whererownumselect*frommlog$_ttt; ASNAPTIME$ DOCHANGE_VEC- - - - - 2534000-01-0100:00:00UU04 2544000-01-0100:00:00UU04 2554000-01-0100:00:00UU04 2564000-01-0100:00:00UU04- 做一次快速刷新USERorclexecdbms_mview.refresh(mvttt,f);PL/SQLproceduresuccessfullycompleted.- 我们可以看到TTT_T里面是没有数据的USERorclselect*fromttt_t;norowsselected接着我们再对对基表的列c做一次更新的操作，看看刷新MView以后表TTT_T中是否有数据USERorclupdatetttsetc=upper(c)whererownumselect*frommlog$_ttt; ASNAPTIME$ DOCHANGE_VEC- - - - - 2534000-01-0100:00:00UU08 2544000-01-0100:00:00UU08 2554000-01-0100:00:00UU08 2564000-01-0100:00:00UU08- 做一次快速刷新USERorclexecdbms_mview.refresh(mvttt,f);PL/SQLproceduresuccessfullycompleted.- 这回我们可以看到数据了USERorclselect*fromttt_t;A B- -18-FEB-09000000PM c18-FEB-09000000PM c18-FEB-09000000PM c18-FEB-09000000PM c注意：对于基表的每一次update操作都会被记录到MView Log中，不管更新的是否是同一行的同一列，而这些操作也会在MView中被一一的推一遍，一个不拉落的，这样如果一个MView操作很频繁而且刷新的间隔太长了的话快速刷新也是很痛苦的一件事的。循序渐进MView（三） 基于ROWID的MView马齿苋发表评论阅读评论1,055 次查看前言基于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的表TTUSERorclcreatetableT4(aint,bvarchar(40);Tablecreated.- 建立一个MView，这是Oracle报错了USERorclcreatematerializedviewmvt4asselect*fromt4;creatematerializedviewmvt4asselect*fromt4 *ERRORatline1:ORA-12014:tableT4doesnotcontainaprimarykeyconstraint默认情况下Oracle是按照基于PK的方式来建立MView的，上面我们建立的测试表是没有PK的，这样Oracle就开始报错了，解决的方法也很简单：在建立MView的时候指定REFRESH WITH ROWID参数，如下USERorclcreatesnapshotmvt4refreshwithrowidasselect*fromt4;Materializedviewcreated.接下来我们往里面增加些数据并测试下：- 添加些测试数据USERorclinsertintot4selectrownum,object_namefromall_objects;11501rowscreated.USERorclcommit;Commitcomplete.- 做一次刷新，OK是没有问题的USERorclexecdbms_mview.refresh(mvt4,c);PL/SQLproceduresuccessfullycompleted.基于ROWID MView的快速刷新为了保证可以快速刷新，我们还需要给表TT建立MView Log，同样建立MView Log的时候我们一样是要制定WITH ROWID参数的。USERorclcreatesnapshotlogon