[20150705]12c物化视图刷新Out of place2.txt
--11G物化视图刷新有1个参数atomic_refresh.
--如果为false，采用的方式是truncate，再使用/*+ append */ 提示insert。这样redo最少，但是刷新期间无法访问。
--如果为true，采用的方式是delete，再insert。这样产生许多redo与undo。这样在刷新期间访问没问题，最多有点慢。
--自己做一个测试：
--12c在这个基础上引入1个参数Out of place,刷新时先建立表在外部,刷新后通过类似分区交换的技术与之交换,这个刷新很形象的命名
--为out-of-place refresh.
--上次做的使用10046跟踪不是很好观察,这次使用logminer发掘看看.
--自己做一个测试:
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
--做一个清空回收站.
SCOTT@test01p> purge recyclebin;
Recyclebin purged.
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 ;
SCOTT@test01p> select index_name from dba_indexes where owner=user and table_name='T_MV';
INDEX_NAME
-------------
I_SNAP$_T_MV
SCOTT@test01p> select object_id,data_object_id ,object_name,object_type from dba_objects where owner=user and object_name in ('T','T_MV','I_SNAP$_T_MV');
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------- ------------------------------ -----------------------
98094 T_MV MATERIALIZED VIEW
98092 98092 T_MV TABLE
98091 98091 T TABLE
98093 98093 I_SNAP$_T_MV INDEX
--注意 OBJECT_ID DATA_OBJECT_ID.下面作为观察很重要.
2.开始测试:
SCOTT@test01p> select sysdate from dual ;
SYSDATE
-------------------
2015-07-05 22:09:55
insert into t select * from all_objects a where rownum commit;
exec dbms_mview.refresh('T_MV','C', atomic_refresh=>false, out_of_place=>true);
2015-07-05 22:10:29
--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的缺点.
3.使用logminer发掘:
--以sys用户登录,12c不能在pluggable database执行如下命令,否则报如下错误.
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2
BEGIN
DBMS_LOGMNR.START_LOGMNR (
STARTTIME => '2015-07-05 22:09:54',
ENDTIME => '2015-07-05 22:10:29',
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/
SYS@test> column c100 format a100
SYS@test> select sql_redo c100,timestamp,data_obj# from V$LOGMNR_CONTENTS where SEG_OWNER='SCOTT' ;
C100 TIMESTAMP DATA_OBJ#
---------------------------------------------------------------------------------------------------- ------------------- ----------
insert into "SCOTT"."T"("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_ 2015-07-05 22:10:14 98091
TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE"
,"EDITION_NAME","SHARING","EDITIONABLE","ORACLE_MAINTAINED") values ('SYS','I_OBJ1',NULL,'36','36','
INDEX',TO_DATE('2013-06-28 09:03:29', 'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2013-06-28 09:03:29', 'YYYY-
MM-DD HH24:MI:SS'),'2013-06-28:09:03:29','VALID','N','N','N','4',NULL,'NONE',NULL,'Y');
--插入1行.
/* MV_REFRESH (CTB) */CREATE TABLE RV$17F2C ("OWNER","COUNT(*)") AS SELECT * FROM (SELECT "T"."OWN 2015-07-05 22:10:26 98095
ER" "OWNER",COUNT(*) "COUNT(*)" FROM "T" "T" GROUP BY "T"."OWNER") WHERE 1 = NULL;
--建立表CREATE TABLE RV$17F2C.
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('APEX_040200','3009'); 2015-07-05 22:10:26 98095
-- snap ....
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('BI','8'); 2015-07-05 22:10:26 98095
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('SYS','41418'); 2015-07-05 22:10:26 98095
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('WMSYS','369'); 2015-07-05 22:10:26 98095
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('SI_INFORMTN_SCHEMA','8'); 2015-07-05 22:10:26 98095
insert into "SCOTT"."T_MV"("OWNER","COUNT(*)") values ('LBACSYS','209'); 2015-07-05 22:10:26 98095
---注意看~~~,这里实际上应该还是RV$17F26表,因为数据字典的缘故.后面的data_obj#没有变化.
/* MV_REFRESH (CIDX) */ CREATE UNIQUE INDEX "SCOTT"."RV$17F2D" ON "SCOTT"."RV$17F2C" (SYS_OP_MAP_NON 2015-07-05 22:10:26 98096
NULL("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" ;
/* MV_REFRESH (DIDX) */ DROP INDEX SCOTT.RV$17F2D; 2015-07-05 22:10:26 98093
ALTER TABLE "SCOTT"."RV$17F2C" RENAME CONSTRAINT "SYS_C0011081" TO "BIN$izvCtfj/TMyq7Q/mJff0xg==$0" 2015-07-05 22:10:27 98092
;
--这里删除的原来的T_mv表的索引以及改名,注意看后面的DATA_OBJ#
ALTER TABLE "SCOTT"."RV$17F2C" RENAME TO "BIN$LCNGIqa6SD2T5PDkG3yfxg==$0" ; 2015-07-05 22:10:27 98092
/* MV_REFRESH (DTB) */ drop table "SCOTT"."RV$17F2C" AS "BIN$LCNGIqa6SD2T5PDkG3yfxg==$0" ; 2015-07-05 22:10:27 98092
--这里删除的原来的T_mv表以及改名,注意看后面的DATA_OBJ#
39 rows selected.
4.分析:
SCOTT@test01p> select * from t_mv where owner='SYS';
OWNER COUNT(*)
------ ----------
SYS 41418
SCOTT@test01p> select * from "SCOTT"."BIN$LCNGIqa6SD2T5PDkG3yfxg==$0" where owner='SYS';
SYS 41417
--正好相差1条.
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$LCNGIqa6SD2T5PDkG3yfxg==$0 RV$17F2C DROP TABLE 98092 98092 98092 2015-07-05:22:06:17
SCOTT@test01p> @10to16 98092
10 to 16 HEX REVERSE16
-------------- ------------------
0000000017f2c 0x2c7f0100
98095 98095 T_MV TABLE
98096 98096 I_SNAP$_T_MV INDEX
--对比前面的 OBJECT_ID DATA_OBJECT_ID,可以发现t_MV对应的表依旧它的索引的OBJECT_ID DATA_OBJECT_ID都发生了变化.
![oracle中的start with connect by用法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)