[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              0

create 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
------------
       98014

SCOTT@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