1、對 應 網 址 :http:/ 序 渐 进 MView( 一 ) 什 么 是 MView马齿苋发表评论阅读评论 1,763 次查看前言几个术语的对应什么是 MViewMView 的基本概念一个简单的例子MView 的作用MView 的刷新MView 刷新的分类指定刷新方式什么时候刷新MView 的基本构成前 言MView 的两大应用方向 一是用于数据库的复制,这个在 Oracle 文档 Advanced Replication中有详细的描述。 另一个是用于数据仓库,同样 Oracle 文档 Oracle Database Data Warehousing Guide 中也有详细的描述。本系列文
2、章以实例的方式一步步的说是什么是 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,
3、MView 所在的数据库什 么 是 MViewMView 的 基 本 概 念MView 中文名称为物化视图(Materialized View),相对于不同的视图来说, MView 的不同之处在于 MView 的结果会保存在一个普通的数据表中,在对 MView 进行查询的时候不再会对创建 MView 的基表进行查询,而是直接查询 MView 对应的结果表,然后通过定期的刷新机制来更新 MView 表中的数据。一 个 简 单 的 例 子- 创建一个测试用的表 TUSERorcl create table t (a int, b varchar2(50), constraint pk_tprima
4、ry key(a);Table created.- 创建对应的 MV名为 MVTUSERorcl create materialized view mvt as select * from t;Materialized view created.- 现在往表里面插入一些个数据USERorcl insert into t select rownum, object_name fromall_objects;11449 rows created.USERorcl commit;Commit complete.- 现在我们看一下数据的情况USERorcl select count(*) from t
5、;COUNT(*)-11449- mvt还是没有数据的USERorcl select count(*) from mvt;COUNT(*)-0- 下面对 mview做一次刷新看看USERorcl exec dbms_mview.refresh(mvt);-在 pl/sql 中的 command 窗口執行PL/SQL procedure successfully completed.- 现在能看到 mv里面已经有数据了USERorcl select count(*) from mvt;COUNT(*)-11449- 然后我们在看一下表 T和 MVT的结构可以发现他们两个是一样的USERorcl
6、desc t;Name Null? Type- - -A NOT NULL NUMBER(38)B VARCHAR2(50)USERorcl desc mvt;Name Null? Type- - -A NOT NULL NUMBER(38)B VARCHAR2(50)MView 的 作 用从上面的例子可以看到我们利用 MV 创建了一个现有数据表的一个复制,同时通过刷新的方式将两个数据表的数据进行了同步。这个就是我们在复制中利用 MView 的最主要的用法,所不同的是在实际应用的环境中表 T 和 MView MVT 并不是在同一个机器上,而是分散在两个以上的机器上,同时基表也可能不止一个,可能
7、存在多个。下面列举了 MView 在实际中的主要作用: 减轻网络负担:通过 MV 将数据从一个数据库分发到多个不同的数据库上,通过对多个数据库访问来减轻对单个数据库的网络负担。 搭建分发环境:通过从一个中央数据库将数据分发到多个节点数据库,达到分发数据的目的。 复制数据子集:MV 可以进行行级/列级的筛选,这样可以复制需要的那一部分数据。 支持离线计算:MV 不需要专用的数据库连接,用户可以按照自己的需求来复制所需要的那一部分数据。MView 的 刷 新下面接着说说 MView 刷新这个事。MView 里面的数据是不会和基表保持实时的同步的,它只是基表在某时时间点(刷新的时间点)的一个一致性的
8、数据的镜像,因此,要保持 MView 尽可能的和基表同步的话就需要我们定期的对 MView 进行刷新。MView 刷 新 的 分 类Oracle 支持三种种方式的刷新:完全刷新和快速刷新以及强制刷新。完 全 刷 新 ( complete refresh)对一个 MView 进行全部刷新的时候差不多是将 MView 重建了,在进行 MView 全部刷新的时候会现将 MView 中现有的数据删除(版本在 10G 或以上)或者 TRUNCATE(版本低于9i),然后在根据创建 MView 时候的查询生成数据插入到 MView 中。对于多层的 MView 来说,当 master MView 全部刷新之
9、后对应的下一级的 MView 也需要全部刷新,否则将会收到 ORA-12034 的错误。快 速 刷 新 ( fast refresh)快速刷新是一种比完全刷新快的多的刷新方式,快速刷新只刷新自上次刷新以来修改的数据,因为快速刷新所要操作的数据量少,使用这种方法能大大的节省带宽.快速刷新要求在基表上面有 MView Log,接着上面给出的例子,我们来给 MVT 做一个快速刷新。- 现在表 T上面创建 MView Log- 如果没有建立 MView Log的话要进行快速刷新的话会报错的,大家可以试试USERorcl create materialized view log on t;Materia
10、lized view log created.- 先看一下我们要修改的数据USERorcl select * from t where a=936;A B- -936 v_$fast_start_serversUSERorcl select * from mvt where a=936;A B- -936 v_$fast_start_servers- 接着对表 T数据进行一些修改USERorcl update t set b=upper(b) where a=936;1 row updated.- 现在 T里面的这条数据变成了这样USERorcl commit;Commit complete.
11、USERorcl select * from t where a=936;A B- -936 V_$FAST_START_SERVERS- 接下来就是进行快速刷新了USERorcl exec dbms_mview.refresh(mvt, F);PL/SQL procedure successfully completed.- 现在看 MVT里面的结果USERorcl select * from mvt where a=936;A B- -936 V_$FAST_START_SERVERS快速刷新最大的问题在于如果保证能进行快速刷新,这是对与复杂的查询来说是有些挑战的问题,以后会慢慢的探讨。强
12、 制 刷 新 ( force refresh)当进行强制刷新的时候系统会首先尝试进行快速刷新,如果快速刷新无法进行的时候系统将会进行完全刷新。其实就是一个快速刷新和完全刷新的结合体。指 定 刷 新 方 式既然有那么多种的刷新方式那我们怎么指定他们呢?在 Oracle 中有两种方法来制定所用的刷新方式,第一种在上面我们已经看过了,就是在执行刷新 MView 语句的时候制定刷新方式,比如说exec dbms_mview.refresh(mvt, F);用来指定对 MView mvt 进行快速刷新,将其中的 ”F”改成”C”就是指定对 mvt 进行完全刷新了。exec dbms_mview.refr
13、esh(mvt, C);另外一种方法是直接执行exec dbms_mview.refresh(mvt);就是不指定刷新的参数,这个时候 MView 的刷新方式将是根据创建时候由 REFRESH 语句指定的刷新方法来进行刷新了,REFRESH 语句一共有下面几种使用方法refresh fast | complete | force FAST: 采用增量刷新,只刷新自上次刷新以后进行的修改 COMPLETE: 对整个实体化视图进行完全的刷新 FORCE(默认): Oracle 在刷新时会去判断是否可以进行快速刷新,如果可以则采用Fast 方式,否则采用 Complete 的方式,Force 选项是
14、默认选项什 么 时 候 刷 新现在怎么刷新的问题解决了,接下来就是考虑我们什么时候进行刷新了。我们从上面已经知道我们需要定期的对 MView 进行刷新以保证基表和 MView 的数据同步,这个定期的方法就是使用 job,同样我们有两种方法来创建刷新 MView 的 job。使 用 DBMS_JOB 包 来 创 建- 提交一个 JOB用来刷新 MViewUSERorcl variable job1 number;USERorcl execdbms_job.submit(:job1,dbms_mview.refresh(“user“.“mvt“);,sysdate,sysdate+1/24);PL
15、/SQL procedure successfully completed.USERorcl commit;Commit complete.- 查看一下结果USERorcl select JOB,NEXT_DATE,NEXT_SEC,INTERVAL,WHAT fromuser_jobs;JOB NEXT_DATE NEXT_SEC INTERVAL WHAT- - - - -23 2009-01-13 13:52:14 13:52:14 sysdate+1/24 dbms_mview.refresh(“user“.“mvt“);使用这种方法相对下面的方法来说不同之处在于这种方法可以自己指定刷
16、新的语句,这样灵活性相对高一些。在 创 建 MView 的 时 候 指 定 REFRESH 语 句没错,还是 REFRESH 语句,这个语句的用法还是挺多的,下面列出这个语句的其他用法:refresh start with datenext date START WITH: 第一次刷新时间 NEXT: 刷新时间间隔说明:指定上面两个选项的任意一个都将会在系统中产生一个新的 JOB,用来对所建立的 MV 进行刷新,这个 JOB 可以从 DBA_JOBS 查到,同时删除 MV 之后该 JOB 也会被删除。下面我们看一个例子:- 创建一个 MView,并指定刷新时间为 sysdate和刷新间隔为一个
17、小时USERorcl create materialized view mvt2 refresh start with sysdatenext sysdate+1/24 as select * from t;Materialized view created.- 现在我们看一下 job- 请注意这里的 WHAT那一栏中 MView刷新的代码是不带刷新方式的,也就是说按照创建时候的刷新方式进行刷新USERorcl select JOB,NEXT_DATE,NEXT_SEC,INTERVAL,WHAT fromuser_jobs;JOB NEXT_DATE NEXT_SEC INTERVAL WH
18、AT- - - - -21 2009-01-13 13:20:20 13:20:20 sysdate+1/24 dbms_refresh.refresh(“USER“.“MVT2“);另外这里所说的 JOB 定时刷新只是针对于单个 MView 来说的,而对于存在多个 MView要刷新的时候我们就要开始考虑刷新组了,这个部分以后再慢慢说明。MView 的 基 本 构 成说了这么多的 MView 的例子,那么 MView 的结构到底是怎么样的呢?我们先看一副Oracle 给出的 MView 的结构图:MView 架构图(来自 Oracle Advance Replication)现在我们只对一些当
19、前接触到的相关的对象进行说明,其他的部分将在后面的文章中陆续说明:必要组成部分 基表(MASTER TABLE):这里说的基表不一定只有一个表,也有可能是一个复杂的查询,涉及很多的表,也可能是一个 MView。 隐藏的 MView 对象:对象类型为 Materialized View 的一个对象,用来维护 MView 的创建信息。 MView 表:MView 所对应的数据表,这个表也就是一个普通的表,不同的就是这个表是和一个 MView 相关联的表。 基表的 INDEX MView 的 INDEX:对以用 PK 建立的 MView 将会有一个与基表结果一样名字类似的索引,对以使用 ROWID
20、建立的 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
21、操作都有一个在基表上的内部触发器记录到 MLOG$_master_name 中,这个触发器在 USER_TRIGGERS 是看不到的,不过能够用 USER_INTERNAL_TRIGGERS 看到。下面用一个例子来看一下这些个对象- 创建一个基表SQL CREATE TABLE TTT1(A INT PRIMARY KEY, B INT);Table created.- 以 TTT1为基表创建一个可更新的 MViewSQL CREATE SNAPSHOT MV_TTT1 FOR UPDATE AS SELECT * FROM TTT1;Materialized view created.- 创
22、建 MView LogSQL CREATE MATERIALIZED VIEW LOG ON TTT1;Materialized view log created.- 检查 MView的对象SQL SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHEREOBJECT_NAME LIKE %TTT1%;OBJECT_NAME OBJECT_TYPE- -TTT1 TABLEMV_TTT1 MATERIALIZED VIEWMV_TTT1 TABLEMLOG$_TTT1 TABLEUSLOG$_MV_TTT1 TABLE- 查看 MView相关的
23、 INDEXSQL SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAMELIKE %TTT1%;INDEX_NAME TABLE_NAME- -PK_TTT11 MV_TTT1PK_TTT1 TTT1- 查看内部触发器SQL SELECT * FROM USER_INTERNAL_TRIGGERS WHERE TABLE_NAME LIKE%TTT1%;TABLE_NAME INTERNAL_TRIGGER_TY- -MV_TTT1 UPDATABLE MVIEW LOGTTT1 MVIEW LOG循 序 渐 进 MVi
24、ew( 二 ) MView Log 的 结 构 与 快 速 刷 新马齿苋发表评论阅读评论 985 次查看前言MView Log 的结构刷新的过程完全刷新的过程快速刷新过程参考资料前 言现在我们通过一些例子来说明一下 MView Log 的基本结构以及 MView 快速刷新的过程。在这一部分里面,我们还是利用上一部分提供的例子先建立一个 MView,同时也创建该MView 基表的 MView Log。- 创建一个测试用的表 TUSERorcl create table t (a int, b varchar2(50), constraint pk_tprimary key(a);Table cr
25、eated.- 创建对应的 MV名为 MVTUSERorcl create materialized view mvt as select * from t;Materialized view created.- 现在往表里面插入一些个数据USERorcl insert into t select rownum, object_name fromall_objects;11449 rows created.USERorcl commit;Commit complete.- 下面对 mview做一次刷新看看USERorcl exec dbms_mview.refresh(mvt);PL/SQL
26、procedure successfully completed.- 创建 MView LogUSERorcl create materialized view log on t;Materialized view log created.- 接下来就是进行快速刷新了USERorcl exec dbms_mview.refresh(mvt, F);PL/SQL procedure successfully completed.MView Log 的 结 构我们先看一下两个测试表的 MView Log 的结构:- 含有 PK的 MViewUSERorcl desc mlog$_t;Name Nu
27、ll? Type- - -A NUMBERSNAPTIME$ DATEDMLTYPE$ VARCHAR2(1)OLD_NEW$ VARCHAR2(1)CHANGE_VECTOR$ RAW(255)现在我们挨个说一下各列的含义:A这个我们基表 T 的主键列,如果基表的主键是一个复合索引的话那这里也就存在多个和基表定义一样的列,当基表被修改以后,基表的主键就会被记录到这个列里面。SNAPTIME$用来记录 MView 刷新的时间,这个字段只有在一个基表对应一个以上的 MView的时候才有意义,因为对于一个 MView Log 来说,只有当所有的 MView 都刷新完了以后才能把 MView Log
28、 里面的记录删除,当一个 MView 刷新的时候,它会把此列置成该 MView 刷新的时候,在这个 MView 再次刷新的时候,那些上次刷新过的列就不用再次被刷新了。下面我们用一个例子来说明一下:- 在建立一个基于表 T的 MView MVT2USERorcl create materialized view mvt2 as select * from t;Materialized view created.- 先做一次刷新USERorcl exec dbms_mview.refresh(mvt2);PL/SQL procedure successfully completed.- 现在对表
29、T做一些修改USERorcl update t set b=upper(b) where rownum select * from mlog$_t;A SNAPTIME$ D O CHANGE_VEC- - - - -936 4000-01-01 00:00:00 U U 04937 4000-01-01 00:00:00 U U 04938 4000-01-01 00:00:00 U U 04939 4000-01-01 00:00:00 U U 04- 现在我们刷新 MVTUSERorcl exec dbms_mview.refresh(mvt,f);PL/SQL procedure su
30、ccessfully completed.- 我们可以看到 SNAPTIME$时间变了USERorcl select * from mlog$_t;A SNAPTIME$ D O CHANGE_VEC- - - - -936 2009-02-18 07:28:09 U U 04937 2009-02-18 07:28:09 U U 04938 2009-02-18 07:28:09 U U 04939 2009-02-18 07:28:09 U U 04- 对比一下 MVT的 LAST_REFRESH_DATE,我们可以发现这个是一样的USERorcl select MVIEW_NAME, L
31、AST_REFRESH_DATE from dba_mviewswhere mview_name in (MVT, MVT2);MVIEW_NAME LAST_REFRESH_DATE- -MVT 2009-02-18 07:28:09MVT2 2009-02-18 07:27:19DMLTYPE$用于表示 DML 操作类型,I 表示 INSERT,D 表示 DELETE,U 表示 UPDATE,这个我们可以从上面的例子里面得到验证。OLD_NEW$用于表示这个值是新值还是旧值。N(EW)表示新值,O(LD)表示旧值,U 表示 UPDATE 操作。CHANGE_VECTOR$表示修改矢量,用来
32、表示被修改的是哪个或哪几个字段,用二进制的方式来保存修改列的结果。USERorcl create table ttt (a int, b varchar(40), c varchar(40);Table created.USERorcl alter table ttt add constraint pk_ttt primary key(a);Table altered.USERorcl insert into ttt select rownum, object_name, object_namefrom all_objects;11488 rows created.USERorcl commi
33、t;Commit complete.USERorcl create snapshot mvttt as select * from ttt;Materialized view created.USERorcl create snapshot log on ttt;Materialized view log created.USERorcl commit;Commit complete.USERorcl update ttt set b=upper(b) where rownum update ttt set c=upper(c) where rownum update ttt set b=up
34、per(b), c=upper(c) where rownum select * from mlog$_ttt;A SNAPTIME$ D O CHANGE_VEC- - - - -253 4000-01-01 00:00:00 U U 04254 4000-01-01 00:00:00 U U 04255 4000-01-01 00:00:00 U U 04256 4000-01-01 00:00:00 U U 04253 4000-01-01 00:00:00 U U 08254 4000-01-01 00:00:00 U U 08255 4000-01-01 00:00:00 U U 0
35、8256 4000-01-01 00:00:00 U U 08253 4000-01-01 00:00:00 U U 0C254 4000-01-01 00:00:00 U U 0C255 4000-01-01 00:00:00 U U 0C256 4000-01-01 00:00:00 U U 0C从上面的例子我们可以看出,修改列 B 的 CHANGE_VECTOR$是 04(0100),修改列 C的 CHANGE_VECTOR$值是 08(1000),同时修改这两个列就变成了 0C(1100)。刷 新 的 过 程完 全 刷 新 的 过 程在 Oracle 9i 以及以前版本中,MView
36、的完全刷新是先对数据库基表做一个 truncate 操作然后再将基表的数据全部插入到 MView 中,而 Oracle 10g 中,MView 完全刷新之前并不对MView 进行 truncate 的操作,取而代之的是 delete 操作,这个在操作大量的数据的时候会有很大的影响,这一点可以通过开 trace 来看到,这里就不在贴实验的具体过程了。快 速 刷 新 过 程看了 MView Log 的结构之后我们很容易就能理解 MView 快速刷新的基本原理了:首先在对基表做 update、delete、insert 操作之后,隐藏的触发器会把基表的修改记录到 MView Log中,在 MView
37、 Log 中基表的主键会被记录(仅对于基于 PK 的 MView 来说,其他类型的MView 随后介绍),这个记录的 pk 会在 MView 做快速刷新的时候被用来定位被操作的数据行,同时还有一些其他的数据会被记录。在 MView 做快速刷新的时候,对于不同的操作语句会有一点点不一样的地方:insert 操作通过 MView Log 记录的基表的主键数据以及 DMLTYPE$字段我们知道那些行是行是新插入的,只要将这些新数据导入到 MView 中即可。delete 操作同样通过 MView Log 记录的基表的主键数据以及 DMLTYPE$字段我们知道那些行是行是被删除了的,只要将这些在基表被
38、删除掉的数据在 MView 中删除掉即可。update 操作通过 MView Log 记录的基表的主键,DMLTYPE$字段以及CHANGE_VECTOR$我们知道那些行的那些列进行了更新的操作,然后再依照基表的数据对 MView 中相应的列挨个进行刷新操作。下面我们利用上面的 MVTTT 进行一下 update 操作的实验,主要是验证 update 的时候Oracle 是否只会 update 基表中被修改的列:首先我们建立一个对 MView MVTTT 中列 C 的触发器,在当 C 列被修改以后在一个新表中插入一条记录。- 创建一个记录表USERorcl create table ttt_t
39、(a timestamp default sysdate, bvarchar(10);Table created.- 创建相应的触发器USERorclCREATE OR REPLACE TRIGGER tri_mvtttBEFORE INSERT OR DELETE OR UPDATE OF c ON mvtttFOR EACH ROWBEGININSERT INTO ttt_t VALUES(SYSDATE, c);END tri_mvttt;/Trigger created.现在我们对基表的列 b 做一次更新的操作,看看刷新 MView 以后表 TTT_T 中是否有数据USERorcl u
40、pdate ttt set b=upper(b) where rownum select * from mlog$_ttt;A SNAPTIME$ D O CHANGE_VEC- - - - -253 4000-01-01 00:00:00 U U 04254 4000-01-01 00:00:00 U U 04255 4000-01-01 00:00:00 U U 04256 4000-01-01 00:00:00 U U 04- 做一次快速刷新USERorcl exec dbms_mview.refresh(mvttt, f);PL/SQL procedure successfully c
41、ompleted.- 我们可以看到 TTT_T里面是没有数据的USERorcl select * from ttt_t;no rows selected接着我们再对对基表的列 c 做一次更新的操作,看看刷新 MView 以后表 TTT_T 中是否有数据USERorcl update ttt set c=upper(c) where rownum select * from mlog$_ttt;A SNAPTIME$ D O CHANGE_VEC- - - - -253 4000-01-01 00:00:00 U U 08254 4000-01-01 00:00:00 U U 08255 400
42、0-01-01 00:00:00 U U 08256 4000-01-01 00:00:00 U U 08- 做一次快速刷新USERorcl exec dbms_mview.refresh(mvttt, f);PL/SQL procedure successfully completed.- 这回我们可以看到数据了USERorcl select * from ttt_t;A B- -18-FEB-09 01.16.53.000000 PM c18-FEB-09 01.16.53.000000 PM c18-FEB-09 01.16.53.000000 PM c18-FEB-09 01.16.5
43、3.000000 PM c注意:对于基表的每一次 update 操作都会被记录到 MView Log 中,不管更新的是否是同一行的同一列,而这些操作也会在 MView 中被一一的推一遍,一个不拉落的,这样如果一个MView 操作很频繁而且刷新的间隔太长了的话快速刷新也是很痛苦的一件事的。循 序 渐 进 MView( 三 ) 基 于 ROWID 的 MView马齿苋发表评论阅读评论 1,055 次查看前言基于 ROWID 的 MView基于 ROWID MView 的创建基于 ROWID MView 的快速刷新基于 ROWID MView 的 MView Log 结构怎么把基于 ROWID 的
44、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的表 TTUSERorcl create table T4 (a int, b varchar(40);Table created.- 建立一个 MView,这是 Oracle报错了USERorcl create materialized view mvt4 as select * from t4;create materialized view mvt4 as select