本文共 5331 字，大约阅读时间需要 17 分钟。
[20150629]12c物化视图刷新Out of place.txt
--11G物化视图刷新有1个参数atomic_refresh.
--如果为false，采用的方式是truncate，再使用/*+ append */ 提示insert。这样redo最少，但是刷新期间无法访问。 --如果为true，采用的方式是delete，再insert。这样产生许多redo与undo。这样在刷新期间访问没问题，最多有点慢。 --自己做一个测试：--12c在这个基础上引入1个参数Out of place,刷新时先建立表在外部,刷新后通过类似分区交换的技术与之交换,这个刷新很形象的命名
--为out-of-place refresh.--自己做一个测试:
1.建立测试环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0create table t as select * from all_objects a;
create materialized view t_mv build immediate refresh on demand enable query rewrite as
select owner, count(*) from t group by owner ;insert into t select * from all_objects a where rownum commit;
@10046on 12
exec dbms_mview.refresh('T_MV','C', atomic_refresh=>false, out_of_place=>true); @10046off--atomic_refresh=>false, out_of_place=>true
--在刷新时先建立表RV$xxxxxx( xxxxx 表示 新表的object_id),然后插入数据,如果物化视图有索引在插入完成后建立,命名RV$xxxxxxx. --( xxxxx 表示 新索引的object_id).一旦以上步骤完成,做交换在数据字段内部,删除的对象保持在recyclebin.这种方式减少redo与 --undo的生成.避免atomic_refresh=>false, out_of_place=>false的缺点.--看看跟踪文件:
=====================
PARSE ERROR #179385424:len=68 dep=2 uid=109 oct=26 lid=109 tim=947239691 err=1418 LOCK TABLE FOR INDEX "SCOTT"."RV$17EDF" IN EXCLUSIVE MODE NOWAIT CLOSE #179385424:c=0,e=3,dep=2,type=0,tim=947239944 ===================== PARSE ERROR #350722896:len=49 dep=1 uid=109 oct=10 lid=109 tim=947246385 err=1418 /* MV_REFRESH (DIDX) */ DROP INDEX SCOTT.RV$17EDF CLOSE #350722896:c=0,e=6,dep=1,type=0,tim=947246764 XCTEND rlbk=0, rd_only=1, tim=947247033 ===================== PARSING IN CURSOR #350722896 len=52 dep=1 uid=109 oct=12 lid=109 tim=947247214 hv=2144366463 ad='22d9988' sqlid='55vc89xzx0vvz' /* MV_REFRESH (DTB) */ drop table "SCOTT"."RV$17EDE" END OF STMT .....--先删除表"SCOTT"."RV$17EDE",索引"SCOTT"."RV$17EDF".
=====================
PARSING IN CURSOR #350722896 len=181 dep=1 uid=109 oct=1 lid=109 tim=947494659 hv=1271955313 ad='7ff59657c00' sqlid='az9jhb95x0zvj' /* MV_REFRESH (CTB) */CRE END OF STMT PARSE #350722896:c=78001,e=71340,p=0,cr=160,cu=0,mis=1,r=0,dep=1,og=1,plh=1064870033,tim=947494658 =====================--这里是建表,仅仅看见CRE.
=====================
PARSING IN CURSOR #350722896 len=135 dep=1 uid=109 oct=2 lid=109 tim=947756432 hv=898979897 ad='7ff55b616a0' sqlid='32hyvfhutaq1t' /* MV_REFRESH (ITB) */INSERT INTO "SCOTT"."RV$17EDE" ("OWNER","COUNT(*)") SELECT "T"."OWNER",COUNT(*) FROM "T" "T" GROUP BY "T"."OWNER" END OF STMT PARSE #350722896:c=15600,e=4699,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=47235625,tim=947756431 ==========================================
PARSING IN CURSOR #350722896 len=736 dep=1 uid=109 oct=9 lid=109 tim=947886624 hv=2337637262 ad='7ff559d86b8' sqlid='1frh4hu5pb0wf' /* MV_REFRESH (CIDX) */ CREATE UNIQUE INDEX "SCOTT"."RV$17EDF" ON "SCOTT"."RV$17EDE" (SYS_OP_MAP_NONNULL("OWNER")) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" END OF STMT PARSE #350722896:c=0,e=6877,p=0,cr=9,cu=1,mis=1,r=0,dep=1,og=1,plh=1685961876,tim=947886624 PARSE #350689800:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=0,tim=947887312 EXEC #350689800:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=0,tim=947887446 CLOSE #350689800:c=0,e=9,dep=2,type=3,tim=947887554 CLOSE #728695808:c=0,e=4,dep=2,type=3,tim=947887674 ===================== --不知道为什么要建立这个索引?看了建立的mv确实有1个索引.--...交换后.
=====================
PARSING IN CURSOR #350722896 len=49 dep=1 uid=109 oct=10 lid=109 tim=950101269 hv=3950017501 ad='22d9988' sqlid='cfrmrnbpr0yyx' /* MV_REFRESH (DIDX) */ DROP INDEX SCOTT.RV$17EDF END OF STMT =========================================
PARSING IN CURSOR #350722896 len=52 dep=1 uid=109 oct=12 lid=109 tim=952228430 hv=2144366463 ad='22d9988' sqlid='55vc89xzx0vvz' /* MV_REFRESH (DTB) */ drop table "SCOTT"."RV$17EDE" END OF STMT PARSE #350722896:c=0,e=342,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=952228429 =====================SCOTT@test01p> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- RV$17EDE BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0 TABLE 2015-06-29:20:42:31--可以发现原来的t_mv是RV$17EDE.很容易验证.
SCOTT@test01p> select * from "BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0" minus select * from t_mv; OWNER COUNT(*) ------ ---------- SYS 41418
SCOTT@test01p> select * from t_mv minus select * from "BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0";
OWNER COUNT(*) ------ ---------- SYS 41423--正好相差5条.
--说明原来的t_mv就是"BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0",oracle在内部数据字典做了交换.SCOTT@test01p> @16to10 17EDE
16 to 10 DEC ------------ 98014SCOTT@test01p> select object_name,original_name,operation,type,related,base_object,purge_object,CREATETIME from user_recyclebin ;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE RELATED BASE_OBJECT PURGE_OBJECT CREATETIME ------------------------------ -------------- --------- ------ ---------- ----------- ------------ ------------------- BIN$g0wGYZ6pRhmN7FiieHWDdQ==$0 RV$17EDE DROP TABLE 98014 98014 98014 2015-06-29:20:41:29转载地址：http://gyymo.baihongyu.com/