ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 基于预建表的物化视图是否支持快速刷新?

基于预建表的物化视图是否支持快速刷新?

原创 Linux操作系统 作者:redhouser 时间:2012-06-25 14:31:46 0 删除 编辑

问题:
(1)on prebuilt table的物化视图支持快速刷新吗?
(2)如果on prebuilt table内容与视图定义语句不一致时,是否支持快速刷新,刷新时是否报错?

--1,创建基表
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE  10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

drop table test;
drop materialized view mv_test;
drop table mv_test;

--基表
create table test
as
select rownum id,'id '||rownum val from dual
connect by level<=100;

--基表必须有主键,否则创建on prebuilt table refresh table 会报错
alter table test
  add constraint pk_test primary key(id);

--2,创建物化视图日志
create materialized view log on test
with primary key,sequence
including new values;

select log_table from user_mview_logs;
LOG_TABLE
------------------------------
MLOG$_TEST

SQL> select * from MLOG$_TEST;
 
        ID SEQUENCE$$ SNAPTIME$$  DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$
---------- ---------- ----------- --------- --------- ---------------

--3,创建物化视图
--只插入部分数据
create table mv_test
as
select * from test where id<=90;

--插入一条基表中不存在的数据
insert into mv_test(id,val) values(200,'id200');
commit;

--创建物化视图
create materialized view mv_test
on prebuilt table
refresh fast
as
select * from test;


select count(*) from test;
100

select count(*) from mv_test;
91


SELECT mview_name,
       refresh_method,
       build_mode,
       fast_refreshable,
       last_refresh_type,
       last_refresh_date
  FROM user_mviews;

MVIEW_NAME                     REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE   LAST_REFRESH_TYPE LAST_REFRESH_DATE
------------------------------ -------------- ---------- ------------------ ----------------- -----------------
MV_TEST                        FAST           PREBUILT   DML                NA                2014-6-15 16:31:2

begin
  dbms_mview.refresh('mv_test','f');
end;

SELECT mview_name,
       refresh_method,
       build_mode,
       fast_refreshable,
       last_refresh_type,
       last_refresh_date
  FROM user_mviews;
 
MVIEW_NAME                     REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE   LAST_REFRESH_TYPE LAST_REFRESH_DATE
------------------------------ -------------- ---------- ------------------ ----------------- -----------------
MV_TEST                        FAST           PREBUILT   DML                FAST              2014-6-15 16:32:0
 
--4,测试快速刷新
--修改基表
--insert
insert into test(id,val) values(101,'id101');
--插入一条在mv中存在的纪录
insert into test(id,val) values(200,'id200new');

--删除一条记录
delete from test where id=1;

--此记录在mv中不存在,在基表中存在
delete from test where id=100;

--更新一条记录
update test test set val='updated' where id=2;

--此记录在mv中不存在,在基表中存在
update test test set val='updated' where id=91;
commit;

begin
  dbms_mview.refresh('mv_test','f');
end;

SELECT mview_name,
       refresh_method,
       build_mode,
       fast_refreshable,
       last_refresh_type,
       last_refresh_date
  FROM user_mviews;
MVIEW_NAME                     REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE   LAST_REFRESH_TYPE LAST_REFRESH_DATE
------------------------------ -------------- ---------- ------------------ ----------------- -----------------
MV_TEST                        FAST           PREBUILT   DML                FAST              2014-6-15 16:34:2


SQL> select * from mv_test where id in(1,2,91,100,101,200);
        ID VAL
---------- -------------------------------------------
         2 updated
       200 id200new
        91 updated
       101 id101
--删除
--id=1,在基表,MV中都存在-->在基表中删除,快速刷新后MV中删除
--id=100,在基表存在,MV中不存在-->在基表中删除,快速刷新后MV中不存在

--插入
--id=101,在基表,MV中都不存在-->在基表中插入,快速刷新后MV中插入
--id=200,在基表不存在,MV中存在-->在基表中插入,快速刷新后MV中被更新(不是插入!)***

--更新
--id=2,在基表,MV中都存在-->在基表中更新,快速刷新后MV中被更新
--id=91,在基表存在,MV中不存在-->在基表中更新,快速刷新后MV中插入并更新***


--5,结论
(1)on prebuilt table支持refresh fast,要求基表上有主键;
(2)on prebuilt table内容可以与基表内容不一致,不一致时可以快速刷新:
**在基表存在,MV中不存在-->在基表中删除,快速刷新后MV中不存在;
**在基表不存在,MV中存在-->在基表中插入,快速刷新后MV中被更新(不是插入!)
**在基表存在,MV中不存在-->在基表中更新,快速刷新后MV中插入并更新
(3)基于以上结论,可以使用数据泵方式创建初始全量

--6,针对大表的快速刷新,如何实现初次全量加载?
6.1物化视图日志清理机制是什么?
(1)数据字典内的sys.sum$,sys.snap_reftime$记录了视图刷新时间
(2)有数据字典记录了试图与日志之间的依赖关系,对于快速刷新的物化视图需要的日志是不会被删除的.

6.2针对大表的快速刷新,如何实现初次全量加载?
思路:
(1)在源系统创建物化视图日志
(2)在源系统创建快速刷新的物化视图mv0,但不刷新,用于确保日志不被删除
(3)在源系统导出基表
(4)在目标系统导入基表
(5)在目标系统创建基于prebuilt table的快速刷新视图mv
(6)在目标系统快速刷新

6.3测试中发现,虽然在目标系统内快速刷新视图创建前的日志没有被删除,但并不能被应用;即该视图创建前的基表变化无法体现在目标系统内物化视图中.

6.4问题解决思路一:可以通过程序解析无法应用的日志,在目标系统应用后,再使用快速刷新实现同步.

6.5问题解决思路二:
系统内必定在数据字典中记录了物化视图刷新的时点(SCN或时间),通过修改数据字典,可以实现从指定时刻开始刷新。


6.5.1通过跟踪会话,确定create materialized view on prebuilt table refresh fast 操作,涉及如下的元数据操作:

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
update sys.mlog$ set youngest = :1, yscn = :2    where mowner = :3 and master = :4
UPDATE sys.snap_colmap$ SET mascol = :1, maspos = :2, colrole = :3,         snapos = :9   WHERE sowner = :4 AND vname = :5 AND snacol = :6 AND tabnum = :7   AND   instsite = :8
UPDATE "BOCNET"."MV_SP_TEST2" SET "CUST_ID" = :1,"NAME_ZH" = :2,"NAME_EN" = :3,"MKSG_ID" = :4,"CIF" = :5,"ENT_CDE" = :6,"IDENTITY_TYPE" = :7,"IDENTITY_NUM" = :8,"IDENTITY_OTH" = :9,"POSTCODE" = :10,"ADDRESS" = :11,"PHONE1" = :12,"PHONE2" = :13,"FAX" = :14,"MOBILE" = :15,"EMAIL" = :16,"STATUS" = :17,"CRT_DATE" = :18,"MOD_DATE" = :19,"BIRTHDAY" = :20,"EDUCATION" = :21,"FAMILY" = :22,"WEDDED" = :23,"INCOME" = :24,"FIRST_NAME" = :25,"LAST_NAME" = :26,"MIDDLE_NAME" = :27,"COUNTRY" = :28,"PROVINCE" = :29,"CITY" = :30,"RESIDENCE" = :31,"TYPE" = :32,"SYN_FLAG" = :33,"DESCRIPTION" = :34,"BANK_ID_O" = :35,"BANK_ID_M" = :36,"BIZ_ID" = :37,"VCT_ID" = :38,"BANK_ID_EBMS" = :39,"SECURITY" = :40,"CIF_VERSION" = :41,"TIME_ZONE" = :42,"SPECIAL_TYPE" = :43 WHERE "CUST_ID" = :1
UPDATE sys.snap_refop$ SET cols = :1, sql_txt = :2,         fcmaskvec =:3, ejmaskvec = :4  WHERE sowner = :5 AND vname = :6 AND tabnum = :7 AND operation# = :8  AND instsite = :9 AND setnum = :10
update c set snaptime = :1  where snapid = :2 and mowner = :3 and master = :4
update sys.mlog$ set ldest = :1, oldest_pk = :2, oldest_oid = :3,  oldest_new = :4, youngest = :5, oldest_seq = :6  where master = :7 and mowner = :8
UPDATE sys.reg_snap$ SET query_txt = :1, snapshot_id = :2,    flag = :3, rep_type = :4  WHERE sowner = :5 AND snapname = :6 AND snapsite = :7
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35 where obj#=:1
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)

INSERT INTO sys.snap_colmap$   (mascol, maspos, colrole, sowner, vname, snacol, tabnum, instsite, snapos)  VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9)
INSERT INTO "BOCNET"."MV_SP_TEST2"  ("CUST_ID","NAME_ZH","NAME_EN","MKSG_ID","CIF","ENT_CDE","IDENTITY_TYPE","IDENTITY_NUM","IDENTITY_OTH","POSTCODE","ADDRESS","PHONE1","PHONE2","FAX","MOBILE","EMAIL","STATUS","CRT_DATE","MOD_DATE","BIRTHDAY","EDUCATION","FAMILY","WEDDED","INCOME","FIRST_NAME","LAST_NAME","MIDDLE_NAME","COUNTRY","PROVINCE","CITY","RESIDENCE","TYPE","SYN_FLAG","DESCRIPTION","BANK_ID_O","BANK_ID_M","BIZ_ID","VCT_ID","BANK_ID_EBMS","SECURITY","CIF_VERSION","TIME_ZONE","SPECIAL_TYPE") VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43)
INSERT INTO sys.snap_refop$ (sowner, vname, tabnum,    operation#, cols, sql_txt, instsite, fcmaskvec, ejmaskvec, setnum)  VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10)
insert into sys.snap$  (sowner, vname, tname, mview, mowner, master, mlink, status, flag,   snapshot, snapid, auto_fast, auto_fun, auto_date, ustrg, uslog,   query_txt, can_use_log, master_version, tables, lobmaskvec, error#,   mtime, mas_roll_seg, rscn, refhnt, query_len, instsite, flavor_id,   objflag, sna_type_oid, sna_type_hashcode, sna_type_owner, sna_type_name,   mas_type_oid, mas_type_hashcode, mas_type_owner, mas_type_name,   parent_sowner, parent_vname, flag2, rel_query, alias_txt, syn_count)  values (:1, :2, :3, :4, :5, :6, :7, :8, :9,          :10, :11, :12, :13, :14, :15, :16,          :17, :18, :19, :20, :21, 0,          SYSDATE, :22, NVL(:23, 0), :24, :25, :26, :27,          :28, :29, :30, :31, :32,          :33, :34, :35, :36,          :37, :38, :39, NULL, NULL, :40)
insert into sys.snap_reftime$  (sowner, vname, tablenum, snaptime, mowner, master, masobj#,   masflag, loadertime, refscn, instsite, lastsuccess, fcmaskvec, ejmaskvec,   sub_handle, change_view)  values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14,          :15, :16)
insert into sys.slog$ (snaptime, snapid, mowner, master)  values (:1, :2, :3, :4)
INSERT INTO sys.reg_snap$ (sowner, snapname, snapsite, snapshot_id, flag,   query_txt, rep_type) values (:1, :2, :3, :4, :5, :6, :7)
insert into sumdetail$ (sumobj#, detailobj#, detailobjtype, detailalias, refreshscn, detaileut, spare4, inline#, instance#, dataless, qbcid)  values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11)
insert into sumkey$ (sumobj#, sumcolpos#, containercol#, detailobj#, detailobjtype, detailcol#, groupingpos#, textlen, text, exprlen, expression, detailcolfunction, nodetype, ordinalpos, parentpos, inline#, instance#,  qbcid, spare2, spare3) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10,  :11, :12, :13, :14, :15, :16, :17, :18, :19, :20)
insert into sumdep$(sumobj#, order#, p_obj#, p_type, p_ref_time, p_ref_scn, flags, inline#, instance#,qbcid, syn_own, syn_name, syn_master, vw_query, vw_query_len)  values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)
insert into sumqb$ (sumobj#, pflags, xpflags, sflags, state, text, textlen,  marker, markerlen, hashval, hashval2, rorder, sorder, leafcnt,  orignode, nodeid, parent, opttyp, frompo, selcnt, flags,  numdetailtab, numaggregates, numkeycolumns, numjoins, numinlines, numwhrnodes, numhavnodes )  values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14,  :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28)
insert into sum$ (obj#,containerobj#,containertype,containernam,fullrefreshtim,increfreshtim,lastrefreshscn,lastrefreshdate,refreshmode,pflags,mflags,numdetailtab,numaggregates,numkeycolumns,numjoins,numinlines,sumtextlen, sumtext,fromoffset,fromlen,objcount,metaversion,xpflags, numwhrnodes,numhavnodes,numqbnodes,qbcmarker,markerdty,rw_mode,rw_name, dest_stmt, src_stmt, spare1)values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12,:13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, NULL, NULL, :31)
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)


6.5.2 测试
(1)更改目标系统物化视图mv的刷新参数:

update sys.sum$ t set t.lastrefreshscn='5973849723298', t.spare1=5973849723298,
t.lastrefreshdate=to_date('2014-06-20 16:20:19','yyyy-mm-dd hh24:mi:ss')
where t.obj#='64581'; --5973849723458  5973849723458

update sys.snap_reftime$ t set t.refscn='5973849723290',
t.snaptime=to_date('2014-06-20 16:20:19','yyyy-mm-dd hh24:mi:ss')
where t.vname='MV_SP_TEST2'; --5973849546940
commit;
注:在没有更新日期时,只应用了部分日志。


(2)对目标系统的物化视图进行快速刷新,视图mv创建前的日志被应用。


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-733723/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    804580