マテリアライズドビューログとは

高速リフレッシュは完全リフレッシュとは異なり、元表に変更があった箇所のみをマテビューに反映する機能。データを集計するような場合、マテビューを再計算しているわけではなく、変更時の差分のみがマテビューに適用されるので、通常、完全リフレッシュよりも高速に処理される。

変更処理実行時、元表の変更された情報を管理しているのが、MLOG。

SQL> desc scott.emp
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4)
 ENAME						    VARCHAR2(10)
 JOB						    VARCHAR2(9)
 MGR						    NUMBER(4)
 HIREDATE					    DATE
 SAL						    NUMBER(7,2)
 COMM						    NUMBER(7,2)
 DEPTNO 					    NUMBER(2)

with句を指定していないから、デフォルトの primary keyが設定されたことになる。

MLOG作成

create materialized view log on scott.emp;

マテリアライズド・ビュー・ログが作成されました。

SQL> col owner for a20
col object_name for a30
col object_type for a20
select owner, object_name, object_type from dba_objects where object_name like '%MLOG%' and owner = 'SCOTT';

OWNER			       OBJECT_NAME		      OBJECT_TYPE
------------------------------ ------------------------------ -----------------------
SCOTT			       MLOG$_EMP		      TABLE
SCOTT			       I_MLOG$_EMP		      INDEX

// デフォルト（with primary keyの場合）
SQL> desc scott.MLOG$_EMP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO						    NUMBER(4)    << primary keyが設定されてる
 SNAPTIME$$					    DATE
 DMLTYPE$$					    VARCHAR2(1)
 OLD_NEW$$					    VARCHAR2(1)
 CHANGE_VECTOR$$				    RAW(255)
 XID$$						    NUMBER

SQL> create materialized view mv_emp refresh fast
  2  as select job, sum(sal) sum_sal, count(*) cnt_all, count(sal) cnt_sal
  3  from scott.emp group by job;
from scott.emp group by job
           *
行3でエラーが発生しました。:
ORA-12032:
"SCOTT"."EMP"のマテリアライズド・ビュー・ログからはROWID列を使用できません。　　<< この場合primary keyではできない

高速リフレッシュを利用するためには、with句に rowidを指定する必要がある。

with_rowidで指定した場合

SQL> create materialized view log on scott.emp with rowid( job, sal ) ;

マテリアライズド・ビュー・ログが作成されました。

経過: 00:00:00.18
SQL> desc scott.MLOG$_EMP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 JOB						    VARCHAR2(9)
 SAL						    NUMBER(7,2)
 M_ROW$$					    VARCHAR2(255)
 SNAPTIME$$					    DATE
 DMLTYPE$$					    VARCHAR2(1)
 OLD_NEW$$					    VARCHAR2(1)
 CHANGE_VECTOR$$				    RAW(255)
 XID$$						    NUMBER

SQL> create materialized view mv_emp refresh fast
  2  as select job, sum(sal) sum_sal, count(*) cnt_all, count(sal) cnt_sal
  3  from scott.emp group by job;

from scott.emp group by job
           *
行3でエラーが発生しました。:
ORA-32401:
"SCOTT"."EMP"のマテリアライズド・ビュー・ログには新規の値がありません。

集計を行なっている場合は、with句に sequence、参照されている全てのカラム、including newvalues句を指定しなければならない

with_rowidとsequenceを指定する

SQL> create materialized view log on scott.emp with sequence, rowid ( job, sal )
  2  including new values;

マテリアライズド・ビュー・ログが作成されました。

SQL> desc scott.mlog$_emp
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 JOB						    VARCHAR2(9)
 SAL						    NUMBER(7,2)
 M_ROW$$					    VARCHAR2(255) << rowidを指定した
場合作成される
 SEQUENCE$$					    NUMBER
 SNAPTIME$$					    DATE
 DMLTYPE$$					    VARCHAR2(1)
 OLD_NEW$$					    VARCHAR2(1)
 CHANGE_VECTOR$$				    RAW(255)
 XID$$						    NUMBER

SQL> create materialized view scott.mv_emp refresh fast
  2  as select job, sum(sal) sum_sal, count(*) cnt_all, count(sal) cnt_sal
  3  from scott.emp group by job;

マテリアライズド・ビューが作成されました。

with句

元表更新時にマテリアライズドビューログに記録する情報を指定

オプション	説明
PRIMARY KEY	更新される全ての行の主キーを記録
ROWID	更新される全ての行の ROWIDを記録
SEQUENCE	更新の順序番号を記録、集計を含むマテビューがある場合指定する必要がある

including / execluding new values句

元表更新時に、更新前情報と更新後情報を記録するかどうかを指定

オプション	説明
including	更新前、更新後の情報を記録集計を含むマテビューがある場合指定する必要があり
excluding	更新前情報のみ記録

マテリアライズドビューのリフレッシュ

UPDATEのみ変更時

SQL> select * from scott.mv_emp;

JOB	     SUM_SAL	CNT_ALL    CNT_SAL
--------- ---------- ---------- ----------
CLERK		3050	      3 	 3
ANALYST 	3000	      1 	 1
MANAGER 	8275	      3 	 3
PRESIDENT	5000	      1 	 1
SALESMAN	5600	      4 	 4

SQL> select * from scott.emp;

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 80-12-17	   800			  20
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30
      7521 WARD       SALESMAN	      7698 81-02-22	  1250	      500	  30
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30
      7698 BLAKE      MANAGER	      7839 81-05-01	  2850			  30
      7782 CLARK      MANAGER	      7839 81-06-09	  2450			  10
      7839 KING       PRESIDENT 	   81-11-17	  5000			  10
      7844 TURNER     SALESMAN	      7698 81-09-08	  1500		0	  30
      7900 JAMES      CLERK	      7698 81-12-03	   950			  30
      7902 FORD       ANALYST	      7566 81-12-03	  3000			  20
      7934 MILLER     CLERK	      7782 82-01-23	  1300			  10

12行が選択されました。

// 元表のデータを更新
SQL> update scott.emp set sal = 1800 where empno = 7369;

1行が更新されました。

SQL> commit;

コミットが完了しました。

SQL> col M_ROW$$ for a30
col CHANGE_VECTOR$$ for a30
set numwidth 20
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

セッションが変更されました。

SQL>

SQL> select * from scott.mlog$_emp;

JOB			   SAL M_ROW$$					SEQUENCE$$ SNAPTIME$$	       D O CHANGE_VECTOR$$				 XID$$
--------- -------------------- ------------------------------ -------------------- ------------------- - - ------------------------------ --------------------
CLERK			   800 AAAXMCAALAAAJDEAAA				 1 4000/01/01 00:00:00 U U 4000 			      1125977216270753
CLERK			  1800 AAAXMCAALAAAJDEAAA				 2 4000/01/01 00:00:00 U N 4000 			      1125977216270753

// 高速リフレッシュ実行
SQL> alter session set tracefile_identifier='MV_REFRESH';

セッションが変更されました。

SQL> alter session set timed_statistics = true;

セッションが変更されました。

SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

SQL> alter session set events '10046 trace name context forever, level 4';

セッションが変更されました。

SQL> exec dbms_mview.refresh( 'scott.mv_emp', 'f' );

PL/SQLプロシージャが正常に完了しました。

SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

SQL> select * from scott.mv_emp;

JOB		       SUM_SAL		    CNT_ALL		 CNT_SAL
--------- -------------------- -------------------- --------------------
CLERK			  4050			  3		       3
ANALYST 		  3000			  1		       1
MANAGER 		  8275			  3		       3
PRESIDENT		  5000			  1		       1
SALESMAN		  5600			  4		       4

/u01/app/oracle/diag/rdbms/orcl12c_primary/orcl12c2/trace/orcl12c2_ora_13650_MV_REFRESH.trc

     27 =====================
     28 PARSING IN CURSOR #140204034303880 len=55 dep=0 uid=118 oct=47 lid=118 tim=3638060523795 hv=951607750 ad='90620f88' sqlid='06cj8wcwbhsf6'
     29 BEGIN dbms_mview.refresh( 'scott.mv_emp', 'f' ); END;
     30 END OF STMT
     31 PARSE #140204034303880:c=0,e=182,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=3638060523795
     32 =====================
（略）
   1063 =====================
   1064 PARSING IN CURSOR #140204033830264 len=1608 dep=2 uid=0 oct=3 lid=0 tim=3638060554427 hv=1407121497 ad='8972fa10' sqlid='320ggfp9xxy2t'
   1065 WITH "TMPDLT$_EMP" AS ( SELECT /*+ RESULT_CACHE(LIFETIME=SESSION, NAME="DMLTYPES:MLOG$_EMP") */ "MAS$"."RID$" "RID$" , "MAS$"."JOB", "MAS$"."SAL"        , DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."TIME$$" "TIME$$", "MAS$"."DMLTYPE$$" "DMLTYPE$$" FRO        M (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXS        EQ$$" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL"  , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."D        MLTYPE$$" "DMLTYPE$$", "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."SNAPTIME$$" "TIME$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"           WHERE "MAS$".SNAPTIME$$ > :B_ST0 )  AS OF SNAPSHOT(:B_SCN)  "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."M        AXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O','U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$")))  ) SELECT CASE WHEN ddt_1 ='D' AND ddt_2 ='I' THEN 'U        ' ELSE ddt_1 END "DML$$",         MAX(mtime) "TIME$$"  FROM ( SELECT MIN(dd_type) OVER (PARTITION BY rid, old_new) ddt_1,                MAX(dd_t        ype) OVER (PARTITION BY rid, old_new) ddt_2, mtime         FROM ( SELECT "DLT$"."RID$" rid, "DLT$"."DML$$" dd_type,                       "DLT$".        "TIME$$" mtime,                       CASE WHEN "DLT$"."DMLTYPE$$" = 'U' AND "DLT$"."OLD_NEW$$" = 'N'                            THEN 'U' ELSE "D        LT$"."OLD_NEW$$" END old_new                       FROM "TMPDLT$_EMP" "DLT$" ) V3 ) V4  GROUP BY CASE WHEN ddt_1 = 'D' AND ddt_2 = 'I' THEN 'U' E        LSE ddt_1 END
   1066 END OF STMT
（略）
   1149 =====================
   1150 PARSING IN CURSOR #140204033830264 len=184 dep=2 uid=0 oct=3 lid=0 tim=3638060555242 hv=4193087787 ad='78a06f40' sqlid='d2jyn27wyuw9b'
   1151 select 1 from "SCOTT"."MLOG$_EMP" where snaptime$$ > :1 and dmltype$$ = 'U' and old_new$$ in ('U', 'O') and sys.dbms_snapshot_utl.vector_compare(        :2, change_vector$$) = 1 and rownum = 1
   1152 END OF STMT
（略）
   1386 =====================
   1387 PARSING IN CURSOR #140204032751648 len=60 dep=1 uid=118 oct=172 lid=118 tim=3638060558393 hv=2081304724 ad='d66ba228' sqlid='73buwwjy0wc4n'
   1388 /* QSMQ VALIDATION */ ALTER SUMMARY "SCOTT"."MV_EMP" COMPILE
   1389 END OF STMT
   1390 PARSE #140204032751648:c=1317,e=1668,p=0,cr=38,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=3638060558393
   1391 =====================
（略）
   2190 =====================
   2191 PARSING IN CURSOR #140204032751648 len=1338 dep=1 uid=118 oct=189 lid=118 tim=3638060569734 hv=1889226232 ad='d9475530' sqlid='2vmm7fjs9qmgs'
   2192 /* MV_REFRESH (MRG) */ MERGE INTO "SCOTT"."MV_EMP" "SNA$" USING (SELECT   /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=0)   */ "DLT$0"."JOB" "GB0", SUM(DECO        DE("DLT$0"."DML$$", 'I',  1, -1)* DECODE(("DLT$0"."SAL"), NULL, 0, 1)) "D0", SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)) "D1", NVL(SUM(DECODE("DLT$        0"."DML$$", 'I',  1, -1)* ("DLT$0"."SAL")), 0) "D2" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL"  , DECODE("MA        S$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."DMLTYPE$$" "DMLTYPE$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 AND NOT                ("MAS$".DMLTYPE$$ = 'U' AND         (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                                               "MAS$".chan        ge_vector$$) = 0))) AS OF SNAPSHOT(:B_SCN)  "DLT$0"  GROUP BY "DLT$0"."JOB")"AV$" ON (SYS_OP_MAP_NONNULL("SNA$"."JOB")=SYS_OP_MAP_NONNULL("AV$"."        GB0")) WHEN MATCHED THEN UPDATE  SET "SNA$"."CNT_SAL"="SNA$"."CNT_SAL"+"AV$"."D0", "SNA$"."CNT_ALL"="SNA$"."CNT_ALL"+"AV$"."D1", "SNA$"."SUM_SAL"        =DECODE("SNA$"."CNT_SAL"+"AV$"."D0",0,NULL,NVL("SNA$"."SUM_SAL",0)+"AV$"."D2") DELETE WHERE ("SNA$"."CNT_ALL" = 0) WHEN NOT MATCHED THEN INSERT (        "SNA$"."JOB", "SNA$"."CNT_SAL", "SNA$"."CNT_ALL", "SNA$"."SUM_SAL") VALUES ( "AV$"."GB0", "AV$"."D0", "AV$"."D1", DECODE ("AV$"."D0", 0, NULL, "A        V$"."D2")) WHERE ("AV$"."D1" > 0)
   2193 END OF STMT
   2194 PARSE #140204032751648:c=2289,e=3086,p=0,cr=20,cu=4,mis=1,r=0,dep=1,og=1,plh=0,tim=3638060569734
   2195 =====================
（略）
   3045 =====================
   3046 PARSING IN CURSOR #140204034064488 len=54 dep=1 uid=0 oct=7 lid=0 tim=3638060583245 hv=3813164406 ad='d6e7c5f0' sqlid='6n82yazjnhjbq'
   3047 delete from "SCOTT"."MLOG$_EMP" where snaptime$$ <= :1
   3048 END OF STMT

UPDATE,DELETE,INSERT時

SQL> select * from scott.mv_emp;

JOB		       SUM_SAL		    CNT_ALL		 CNT_SAL
--------- -------------------- -------------------- --------------------
CLERK			  4050			  3		       3
ANALYST 		  3000			  1		       1
MANAGER 		  8275			  3		       3
PRESIDENT		  5000			  1		       1
SALESMAN		  5600			  4		       4

SQL> select * from scott.emp;

	       EMPNO ENAME	JOB			   MGR HIREDATE 			    SAL 		COMM		   DEPTNO
-------------------- ---------- --------- -------------------- ------------------- -------------------- -------------------- --------------------
		7369 SMITH	CLERK			  7902 1980/12/17 00:00:00		   1800 				       20
		7499 ALLEN	SALESMAN		  7698 1981/02/20 00:00:00		   1600 		 300		       30
		7521 WARD	SALESMAN		  7698 1981/02/22 00:00:00		   1250 		 500		       30
		7566 JONES	MANAGER 		  7839 1981/04/02 00:00:00		   2975 				       20
		7654 MARTIN	SALESMAN		  7698 1981/09/28 00:00:00		   1250 		1400		       30
		7698 BLAKE	MANAGER 		  7839 1981/05/01 00:00:00		   2850 				       30
		7782 CLARK	MANAGER 		  7839 1981/06/09 00:00:00		   2450 				       10
		7839 KING	PRESIDENT		       1981/11/17 00:00:00		   5000 				       10
		7844 TURNER	SALESMAN		  7698 1981/09/08 00:00:00		   1500 		   0		       30
		7900 JAMES	CLERK			  7698 1981/12/03 00:00:00		    950 				       30
		7902 FORD	ANALYST 		  7566 1981/12/03 00:00:00		   3000 				       20
		7934 MILLER	CLERK			  7782 1982/01/23 00:00:00		   1300 				       10

12行が選択されました。

// 元表のデータを更新
SQL> update scott.emp set sal = 1950 where empno = 7900;

1行が更新されました。

SQL> delete scott.emp where empno = 7499;

1行が削除されました。

SQL> insert into scott.emp 
  2  select 8000, 'ALLEN2', 'SALESMAN2', 7698, sysdate, 2000, 500, 30 from dual;

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> col M_ROW$$ for a30
col CHANGE_VECTOR$$ for a30
set numwidth 20
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';

セッションが変更されました。

SQL>

SQL> select * from scott.mlog$_emp;

JOB			   SAL M_ROW$$					SEQUENCE$$ SNAPTIME$$	       D O CHANGE_VECTOR$$				 XID$$
--------- -------------------- ------------------------------ -------------------- ------------------- - - ------------------------------ --------------------
CLERK			   950 AAAXMCAALAAAJDEAAJ				11 4000/01/01 00:00:00 U U 4000 			      4503694116668436
CLERK			  1950 AAAXMCAALAAAJDEAAJ				12 4000/01/01 00:00:00 U N 4000 			      4503694116668436
SALESMAN		  1600 AAAXMCAALAAAJDEAAB				13 4000/01/01 00:00:00 D O 0000 			      4503694116668436
SALESMAN2		  2000 AAAXMCAALAAAJDEAAM				14 4000/01/01 00:00:00 I N FEFF 			      4503694116668436

// 高速リフレッシュ実行
SQL> alter session set tracefile_identifier='MV_REFRESH2';

セッションが変更されました。

SQL> alter session set timed_statistics = true;

セッションが変更されました。

SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

SQL> alter session set events '10046 trace name context forever, level 4';

セッションが変更されました。

SQL> exec dbms_mview.refresh( 'scott.mv_emp', 'f' );

PL/SQLプロシージャが正常に完了しました。

SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

SQL> select * from scott.mv_emp;

JOB		       SUM_SAL		    CNT_ALL		 CNT_SAL
--------- -------------------- -------------------- --------------------
CLERK			  5050			  3		       3
ANALYST 		  3000			  1		       1
MANAGER 		  8275			  3		       3
PRESIDENT		  5000			  1		       1
SALESMAN		  4000			  3		       3
SALESMAN2		  2000			  1		       1

/u01/app/oracle/diag/rdbms/orcl12c_primary/orcl12c2/trace/orcl12c2_ora_13650_MV_REFRESH2.trc

     27 =====================
     28 PARSING IN CURSOR #140204034168656 len=55 dep=0 uid=118 oct=47 lid=118 tim=3639374129980 hv=951607750 ad='90620f88' sqlid='06cj8wcwbhsf6'
     29 BEGIN dbms_mview.refresh( 'scott.mv_emp', 'f' ); END;
     30 END OF STMT
（略）
   1171 =====================
   1172 PARSING IN CURSOR #140204034126880 len=1608 dep=2 uid=0 oct=3 lid=0 tim=3639374173168 hv=1407121497 ad='8972fa10' sqlid='320ggfp9xxy2t'
   1173 WITH "TMPDLT$_EMP" AS ( SELECT /*+ RESULT_CACHE(LIFETIME=SESSION, NAME="DMLTYPES:MLOG$_EMP") */ "MAS$"."RID$" "RID$" , "MAS$"."JOB", "MAS$"."SAL"        , DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."TIME$$" "TIME$$", "MAS$"."DMLTYPE$$" "DMLTYPE$$" FRO        M (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXS        EQ$$" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL"  , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."D        MLTYPE$$" "DMLTYPE$$", "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."SNAPTIME$$" "TIME$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"           WHERE "MAS$".SNAPTIME$$ > :B_ST0 )  AS OF SNAPSHOT(:B_SCN)  "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."M        AXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O','U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$")))  ) SELECT CASE WHEN ddt_1 ='D' AND ddt_2 ='I' THEN 'U        ' ELSE ddt_1 END "DML$$",         MAX(mtime) "TIME$$"  FROM ( SELECT MIN(dd_type) OVER (PARTITION BY rid, old_new) ddt_1,                MAX(dd_t        ype) OVER (PARTITION BY rid, old_new) ddt_2, mtime         FROM ( SELECT "DLT$"."RID$" rid, "DLT$"."DML$$" dd_type,                       "DLT$".        "TIME$$" mtime,                       CASE WHEN "DLT$"."DMLTYPE$$" = 'U' AND "DLT$"."OLD_NEW$$" = 'N'                            THEN 'U' ELSE "D        LT$"."OLD_NEW$$" END old_new                       FROM "TMPDLT$_EMP" "DLT$" ) V3 ) V4  GROUP BY CASE WHEN ddt_1 = 'D' AND ddt_2 = 'I' THEN 'U' E        LSE ddt_1 END
   1174 END OF STMT
（略）
   2263 =====================
   2264 PARSING IN CURSOR #140204033834168 len=1338 dep=1 uid=118 oct=189 lid=118 tim=3639374188680 hv=1889226232 ad='d9475530' sqlid='2vmm7fjs9qmgs'
   2265 /* MV_REFRESH (MRG) */ MERGE INTO "SCOTT"."MV_EMP" "SNA$" USING (SELECT   /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=0)   */ "DLT$0"."JOB" "GB0", SUM(DECO        DE("DLT$0"."DML$$", 'I',  1, -1)* DECODE(("DLT$0"."SAL"), NULL, 0, 1)) "D0", SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)) "D1", NVL(SUM(DECODE("DLT$        0"."DML$$", 'I',  1, -1)* ("DLT$0"."SAL")), 0) "D2" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL"  , DECODE("MA        S$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."DMLTYPE$$" "DMLTYPE$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 AND NOT                ("MAS$".DMLTYPE$$ = 'U' AND         (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                                               "MAS$".chan        ge_vector$$) = 0))) AS OF SNAPSHOT(:B_SCN)  "DLT$0"  GROUP BY "DLT$0"."JOB")"AV$" ON (SYS_OP_MAP_NONNULL("SNA$"."JOB")=SYS_OP_MAP_NONNULL("AV$"."        GB0")) WHEN MATCHED THEN UPDATE  SET "SNA$"."CNT_SAL"="SNA$"."CNT_SAL"+"AV$"."D0", "SNA$"."CNT_ALL"="SNA$"."CNT_ALL"+"AV$"."D1", "SNA$"."SUM_SAL"        =DECODE("SNA$"."CNT_SAL"+"AV$"."D0",0,NULL,NVL("SNA$"."SUM_SAL",0)+"AV$"."D2") DELETE WHERE ("SNA$"."CNT_ALL" = 0) WHEN NOT MATCHED THEN INSERT (        "SNA$"."JOB", "SNA$"."CNT_SAL", "SNA$"."CNT_ALL", "SNA$"."SUM_SAL") VALUES ( "AV$"."GB0", "AV$"."D0", "AV$"."D1", DECODE ("AV$"."D0", 0, NULL, "A        V$"."D2")) WHERE ("AV$"."D1" > 0)
   2266 END OF STMT
   2267 PARSE #140204033834168:c=2911,e=3571,p=0,cr=20,cu=4,mis=1,r=0,dep=1,og=1,plh=0,tim=3639374188680
   2268 =====================
（略）
   3124 =====================
   3125 PARSING IN CURSOR #140204032484240 len=54 dep=1 uid=0 oct=7 lid=0 tim=3639374239640 hv=3813164406 ad='d6e7c5f0' sqlid='6n82yazjnhjbq'
   3126 delete from "SCOTT"."MLOG$_EMP" where snaptime$$ <= :1
   3127 END OF STMT

もう少し複雑なマテリアライズドビューを作成する

SQL> drop materialized view log on scott.emp;

マテリアライズド・ビュー・ログが削除されました。

SQL> create materialized view log on scott.emp with sequence, rowid ( job, sal, comm )
  2  including new values;

マテリアライズド・ビュー・ログが作成されました。

SQL> desc scott.MLOG$_EMP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 JOB						    VARCHAR2(9)
 SAL						    NUMBER(7,2)
 COMM						    NUMBER(7,2)
 M_ROW$$					    VARCHAR2(255)
 SEQUENCE$$					    NUMBER
 SNAPTIME$$					    DATE
 DMLTYPE$$					    VARCHAR2(1)
 OLD_NEW$$					    VARCHAR2(1)
 CHANGE_VECTOR$$				    RAW(255)
 XID$$						    NUMBER

SQL> create materialized view mv_emp2 refresh fast
  2  as select job, sum(sal) sum_sal, avg(sal) avg_sal
  3          , max(comm) max_comm, min(comm) min_comm
  4          , count(*) cnt_all, count(sal) cnt_sal
  5  from scott.emp group by job;

マテリアライズド・ビューが作成されました。

SQL> select * from scott.mv_emp2;

JOB		       SUM_SAL		    AVG_SAL		MAX_COMM	     MIN_COMM		   CNT_ALL		CNT_SAL
--------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
SALESMAN2		  2000		       2000		     500		  500			 1		      1
CLERK			  5050 1683.333333333333333								 3		      3
SALESMAN		  4000 1333.333333333333333		    1400		    0			 3		      3
PRESIDENT		  5000		       5000								 1		      1
MANAGER 		  8275 2758.333333333333333								 3		      3
ANALYST 		  3000		       3000								 1		      1

6行が選択されました。

SQL> select * from scott.emp;

	       EMPNO ENAME	JOB			   MGR HIREDATE 			    SAL 		COMM		   DEPTNO
-------------------- ---------- --------- -------------------- ------------------- -------------------- -------------------- --------------------
		7369 SMITH	CLERK			  7902 1980/12/17 00:00:00		   1800 				       20
		7521 WARD	SALESMAN		  7698 1981/02/22 00:00:00		   1250 		 500		       30
		7566 JONES	MANAGER 		  7839 1981/04/02 00:00:00		   2975 				       20
		7654 MARTIN	SALESMAN		  7698 1981/09/28 00:00:00		   1250 		1400		       30
		7698 BLAKE	MANAGER 		  7839 1981/05/01 00:00:00		   2850 				       30
		7782 CLARK	MANAGER 		  7839 1981/06/09 00:00:00		   2450 				       10
		7839 KING	PRESIDENT		       1981/11/17 00:00:00		   5000 				       10
		7844 TURNER	SALESMAN		  7698 1981/09/08 00:00:00		   1500 		   0		       30
		7900 JAMES	CLERK			  7698 1981/12/03 00:00:00		   1950 				       30
		7902 FORD	ANALYST 		  7566 1981/12/03 00:00:00		   3000 				       20
		7934 MILLER	CLERK			  7782 1982/01/23 00:00:00		   1300 				       10

	       EMPNO ENAME	JOB			   MGR HIREDATE 			    SAL 		COMM		   DEPTNO
-------------------- ---------- --------- -------------------- ------------------- -------------------- -------------------- --------------------
		8000 ALLEN2	SALESMAN2		  7698 2019/03/17 15:10:23		   2000 		 500		       30

12行が選択されました。

SQL> update scott.emp set sal = 950 where empno = 7900;

1行が更新されました。

SQL> delete scott.emp where empno=8000;;
delete scott.emp where empno=8000;
                                 *
行1でエラーが発生しました。:
ORA-00933: SQLコマンドが正しく終了されていません。


SQL> delete scott.emp where empno=8000;

1行が削除されました。

SQL> insert into scott.emp
  2  select 7499, 'ALLEN', 'SALESMAN', 7698, to_date('1981/02/20', 'YYYY/MM/DD'), 1600, 300, 30 from dual;

1行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> select * from scott.mlog$_emp;

JOB			   SAL		       COMM M_ROW$$				     SEQUENCE$$ SNAPTIME$$	    D O CHANGE_VECTOR$$ 	      XID$$
--------- -------------------- -------------------- ------------------------------ -------------------- ------------------- - - ------------------------------ --------------------
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     15 4000/01/01 00:00:00 U U 4000			   4503625397191708
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     16 4000/01/01 00:00:00 U N 4000			   4503625397191708
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     17 4000/01/01 00:00:00 D O 0000			   4503625397191708
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     18 4000/01/01 00:00:00 I N FEFF			   4503625397191708

SQL> alter session set tracefile_identifier='MV_REFRESH3';

セッションが変更されました。

SQL> alter session set timed_statistics = true;

セッションが変更されました。

SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

SQL> alter session set events '10046 trace name context forever, level 4';

セッションが変更されました。

SQL> exec dbms_mview.refresh( 'scott.mv_emp', 'f' );
BEGIN dbms_mview.refresh( 'scott.mv_emp', 'f' ); END;

*
行1でエラーが発生しました。:
ORA-12034: "SCOTT"."EMP"のマテリアライズド・ビュー・ログは最終リフレッシュよりも新しいものです。 ORA-06512: "SYS.DBMS_SNAPSHOT",
行2821
ORA-06512: "SYS.DBMS_SNAPSHOT", 行3058
ORA-06512: "SYS.DBMS_SNAPSHOT", 行3017
ORA-06512: 行1


SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

SQL> select * from scott.mv_emp;

JOB		       SUM_SAL		    CNT_ALL		 CNT_SAL
--------- -------------------- -------------------- --------------------
CLERK			  5050			  3		       3
ANALYST 		  3000			  1		       1
MANAGER 		  8275			  3		       3
PRESIDENT		  5000			  1		       1
SALESMAN		  4000			  3		       3
SALESMAN2		  2000			  1		       1

6行が選択されました。

SQL> alter session set tracefile_identifier='MV_REFRESH4';

セッションが変更されました。

SQL> alter session set timed_statistics = true;

セッションが変更されました。

SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

SQL> alter session set events '10046 trace name context forever, level 4';

セッションが変更されました。

SQL> exec dbms_mview.refresh( 'scott.mv_emp', 'C' );

PL/SQLプロシージャが正常に完了しました。

SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

SQL> select * from scott.mv_emp;

JOB		       SUM_SAL		    CNT_ALL		 CNT_SAL
--------- -------------------- -------------------- --------------------
CLERK			  4050			  3		       3
SALESMAN		  5600			  4		       4
PRESIDENT		  5000			  1		       1
MANAGER 		  8275			  3		       3
ANALYST 		  3000			  1		       1

SQL>

SQL> select * from scott.mlog$_emp;

JOB			   SAL		       COMM M_ROW$$				     SEQUENCE$$ SNAPTIME$$	    D O CHANGE_VECTOR$$ 	      XID$$
--------- -------------------- -------------------- ------------------------------ -------------------- ------------------- - - ------------------------------ --------------------
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     15 2019/03/17 15:47:17 U U 4000			   4503625397191708  <<< この日付が悪さしてる？
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     16 2019/03/17 15:47:17 U N 4000			   4503625397191708  <<< この日付が悪さしてる？
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     17 2019/03/17 15:47:17 D O 0000			   4503625397191708  <<< この日付が悪さしてる？
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     18 2019/03/17 15:47:17 I N FEFF			   4503625397191708  <<< この日付が悪さしてる？
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     19 4000/01/01 00:00:00 U U 4000			   3377725490349242
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     20 4000/01/01 00:00:00 U N 4000			   3377725490349242
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     21 4000/01/01 00:00:00 D O 0000			   3377725490349242
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     22 4000/01/01 00:00:00 I N FEFF			   3377725490349242

8行が選択されました。

SQL> alter session set tracefile_identifier='MV_REFRESH6';

セッションが変更されました。

SQL> alter session set timed_statistics = true;

セッションが変更されました。

SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

SQL> alter session set events '10046 trace name context forever, level 4';

セッションが変更されました。

SQL> exec dbms_mview.refresh( 'scott.mv_emp', 'f' );

PL/SQLプロシージャが正常に完了しました。

SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

SQL> select * from scott.mv_emp;

JOB		       SUM_SAL		    CNT_ALL		 CNT_SAL
--------- -------------------- -------------------- --------------------
SALESMAN2		  2000			  1		       1
CLERK			  5050			  3		       3
SALESMAN		  4000			  3		       3
PRESIDENT		  5000			  1		       1
MANAGER 		  8275			  3		       3
ANALYST 		  3000			  1		       1

6行が選択されました。

SQL> select * from scott.mlog$_emp;

JOB			   SAL		       COMM M_ROW$$				     SEQUENCE$$ SNAPTIME$$	    D O CHANGE_VECTOR$$ 	      XID$$
--------- -------------------- -------------------- ------------------------------ -------------------- ------------------- - - ------------------------------ --------------------
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     15 2019/03/17 15:47:17 U U 4000			   4503625397191708
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     16 2019/03/17 15:47:17 U N 4000			   4503625397191708
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     17 2019/03/17 15:47:17 D O 0000			   4503625397191708
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     18 2019/03/17 15:47:17 I N FEFF			   4503625397191708
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     19 2019/03/17 17:08:24 U U 4000			   3377725490349242
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     20 2019/03/17 17:08:24 U N 4000			   3377725490349242
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     21 2019/03/17 17:08:24 D O 0000			   3377725490349242
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     22 2019/03/17 17:08:24 I N FEFF			   3377725490349242

8行が選択されました。

SQL>

/u01/app/oracle/diag/rdbms/orcl12c_primary/orcl12c2/trace/orcl12c2_ora_13650_MV_REFRESH6.trc

    694 =====================
    695 PARSING IN CURSOR #140204031282136 len=203 dep=1 uid=0 oct=6 lid=0 tim=3646363903115 hv=311697240 ad='79a12968' sqlid='fjzkr1n9987us'
    696 update "SCOTT"."MLOG$_EMP" set snaptime$$ = :1 where rowid in  (select rowid from "SCOTT"."MLOG$_EMP" AS OF SNAPSHOT (:2) log$   where snaptime$$         > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'))
    697 END OF STMT
   1151 =====================
   1152 PARSING IN CURSOR #140204031231000 len=1608 dep=2 uid=0 oct=3 lid=0 tim=3646363907392 hv=1407121497 ad='8972fa10' sqlid='320ggfp9xxy2t'
   1153 WITH "TMPDLT$_EMP" AS ( SELECT /*+ RESULT_CACHE(LIFETIME=SESSION, NAME="DMLTYPES:MLOG$_EMP") */ "MAS$"."RID$" "RID$" , "MAS$"."JOB", "MAS$"."SAL"        , DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."TIME$$" "TIME$$", "MAS$"."DMLTYPE$$" "DMLTYPE$$" FRO        M (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXS        EQ$$" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL"  , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."D        MLTYPE$$" "DMLTYPE$$", "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."SNAPTIME$$" "TIME$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"           WHERE "MAS$".SNAPTIME$$ > :B_ST0 )  AS OF SNAPSHOT(:B_SCN)  "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."M        AXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O','U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$")))  ) SELECT CASE WHEN ddt_1 ='D' AND ddt_2 ='I' THEN 'U        ' ELSE ddt_1 END "DML$$",         MAX(mtime) "TIME$$"  FROM ( SELECT MIN(dd_type) OVER (PARTITION BY rid, old_new) ddt_1,                MAX(dd_t        ype) OVER (PARTITION BY rid, old_new) ddt_2, mtime         FROM ( SELECT "DLT$"."RID$" rid, "DLT$"."DML$$" dd_type,                       "DLT$".        "TIME$$" mtime,                       CASE WHEN "DLT$"."DMLTYPE$$" = 'U' AND "DLT$"."OLD_NEW$$" = 'N'                            THEN 'U' ELSE "D        LT$"."OLD_NEW$$" END old_new                       FROM "TMPDLT$_EMP" "DLT$" ) V3 ) V4  GROUP BY CASE WHEN ddt_1 = 'D' AND ddt_2 = 'I' THEN 'U' E        LSE ddt_1 END
   1154 END OF STMT
   2243 =====================
   2244 PARSING IN CURSOR #140204034116336 len=1338 dep=1 uid=118 oct=189 lid=118 tim=3646363924548 hv=1889226232 ad='d9475530' sqlid='2vmm7fjs9qmgs'
   2245 /* MV_REFRESH (MRG) */ MERGE INTO "SCOTT"."MV_EMP" "SNA$" USING (SELECT   /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=0)   */ "DLT$0"."JOB" "GB0", SUM(DECO        DE("DLT$0"."DML$$", 'I',  1, -1)* DECODE(("DLT$0"."SAL"), NULL, 0, 1)) "D0", SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)) "D1", NVL(SUM(DECODE("DLT$        0"."DML$$", 'I',  1, -1)* ("DLT$0"."SAL")), 0) "D2" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL"  , DECODE("MA        S$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."DMLTYPE$$" "DMLTYPE$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 AND NOT                ("MAS$".DMLTYPE$$ = 'U' AND         (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                                               "MAS$".chan        ge_vector$$) = 0))) AS OF SNAPSHOT(:B_SCN)  "DLT$0"  GROUP BY "DLT$0"."JOB")"AV$" ON (SYS_OP_MAP_NONNULL("SNA$"."JOB")=SYS_OP_MAP_NONNULL("AV$"."        GB0")) WHEN MATCHED THEN UPDATE  SET "SNA$"."CNT_SAL"="SNA$"."CNT_SAL"+"AV$"."D0", "SNA$"."CNT_ALL"="SNA$"."CNT_ALL"+"AV$"."D1", "SNA$"."SUM_SAL"        =DECODE("SNA$"."CNT_SAL"+"AV$"."D0",0,NULL,NVL("SNA$"."SUM_SAL",0)+"AV$"."D2") DELETE WHERE ("SNA$"."CNT_ALL" = 0) WHEN NOT MATCHED THEN INSERT (        "SNA$"."JOB", "SNA$"."CNT_SAL", "SNA$"."CNT_ALL", "SNA$"."SUM_SAL") VALUES ( "AV$"."GB0", "AV$"."D0", "AV$"."D1", DECODE ("AV$"."D0", 0, NULL, "A        V$"."D2")) WHERE ("AV$"."D1" > 0)
   2246 END OF STMT
   2247 PARSE #140204034116336:c=3054,e=3860,p=0,cr=20,cu=4,mis=1,r=0,dep=1,og=1,plh=0,tim=3646363924548
   2248 =====================

リフレッシュ対象が間違っていたことに気づく。。。

SQL> select * from scott.mv_emp2;

JOB		       SUM_SAL		    AVG_SAL		MAX_COMM	     MIN_COMM		   CNT_ALL		CNT_SAL
--------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
SALESMAN2		  2000		       2000		     500		  500			 1		      1
CLERK			  5050 1683.333333333333333								 3		      3
SALESMAN		  4000 1333.333333333333333		    1400		    0			 3		      3
PRESIDENT		  5000		       5000								 1		      1
MANAGER 		  8275 2758.333333333333333								 3		      3
ANALYST 		  3000		       3000								 1		      1

6行が選択されました。

SQL> select * from scott.emp;

	       EMPNO ENAME	JOB			   MGR HIREDATE 			    SAL 		COMM		   DEPTNO
-------------------- ---------- --------- -------------------- ------------------- -------------------- -------------------- --------------------
		7369 SMITH	CLERK			  7902 1980/12/17 00:00:00		   1800 				       20
		7521 WARD	SALESMAN		  7698 1981/02/22 00:00:00		   1250 		 500		       30
		7566 JONES	MANAGER 		  7839 1981/04/02 00:00:00		   2975 				       20
		7654 MARTIN	SALESMAN		  7698 1981/09/28 00:00:00		   1250 		1400		       30
		7698 BLAKE	MANAGER 		  7839 1981/05/01 00:00:00		   2850 				       30
		7782 CLARK	MANAGER 		  7839 1981/06/09 00:00:00		   2450 				       10
		7839 KING	PRESIDENT		       1981/11/17 00:00:00		   5000 				       10
		7844 TURNER	SALESMAN		  7698 1981/09/08 00:00:00		   1500 		   0		       30
		7900 JAMES	CLERK			  7698 1981/12/03 00:00:00		   1950 				       30
		7902 FORD	ANALYST 		  7566 1981/12/03 00:00:00		   3000 				       20
		7934 MILLER	CLERK			  7782 1982/01/23 00:00:00		   1300 				       10
		8000 ALLEN2	SALESMAN2		  7698 2019/03/17 17:06:59		   2000 		 500		       30

12行が選択されました。

SQL> select * from scott.mlog$_emp;

JOB			   SAL		       COMM M_ROW$$				     SEQUENCE$$ SNAPTIME$$	    D O CHANGE_VECTOR$$ 	      XID$$
--------- -------------------- -------------------- ------------------------------ -------------------- ------------------- - - ------------------------------ --------------------
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     15 2019/03/17 15:47:17 U U 4000			   4503625397191708
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     16 2019/03/17 15:47:17 U N 4000			   4503625397191708
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     17 2019/03/17 15:47:17 D O 0000			   4503625397191708
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     18 2019/03/17 15:47:17 I N FEFF			   4503625397191708
CLERK			   950			    AAAXMCAALAAAJDEAAJ				     19 2019/03/17 17:08:24 U U 4000			   3377725490349242
CLERK			  1950			    AAAXMCAALAAAJDEAAJ				     20 2019/03/17 17:08:24 U N 4000			   3377725490349242
SALESMAN		  1600			300 AAAXMCAALAAAJDEAAB				     21 2019/03/17 17:08:24 D O 0000			   3377725490349242
SALESMAN2		  2000			500 AAAXMCAALAAAJDEAAM				     22 2019/03/17 17:08:24 I N FEFF			   3377725490349242

8行が選択されました。

SQL> alter session set tracefile_identifier='MV_REFRESH7';

セッションが変更されました。

SQL> alter session set timed_statistics = true;

セッションが変更されました。

SQL> alter session set statistics_level=all;

セッションが変更されました。

SQL> alter session set max_dump_file_size = unlimited;

セッションが変更されました。

SQL> alter session set events '10046 trace name context forever, level 4';

セッションが変更されました。

SQL> exec dbms_mview.refresh( 'scott.mv_emp2', 'f' );

PL/SQLプロシージャが正常に完了しました。

SQL> alter session set events '10046 trace name context off';

セッションが変更されました。

SQL> select * from scott.mv_emp2;

JOB		       SUM_SAL		    AVG_SAL		MAX_COMM	     MIN_COMM		   CNT_ALL		CNT_SAL
--------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
PRESIDENT		  5000		       5000								 1		      1
MANAGER 		  8275 2758.333333333333333								 3		      3
ANALYST 		  3000		       3000								 1		      1
SALESMAN2		  2000		       2000		     500		  500			 1		      1
CLERK			  5050 1683.333333333333333								 3		      3
SALESMAN		  4000 1333.333333333333333		    1400		    0			 3		      3

6行が選択されました。

SQL> select * from scott.mlog$_emp;

レコードが選択されませんでした。

SQL>

/u01/app/oracle/diag/rdbms/orcl12c_primary/orcl12c2/trace/orcl12c2_ora_13650_MV_REFRESH7.trc

=====================
PARSING IN CURSOR #140204033188792 len=1638 dep=2 uid=0 oct=3 lid=0 tim=3647348247428 hv=377604487 ad='677d43e8' sqlid='3z5cqacb83kc7'
WITH "TMPDLT$_EMP" AS ( SELECT /*+ RESULT_CACHE(LIFETIME=SESSION, NAME="DMLTYPES:MLOG$_EMP") */ "MAS$"."RID$" "RID$" , "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM", DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."TIME$$" "TIME$$", "MAS$"."DMLTYPE$$" "DMLTYPE$$" FROM (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXSEQ$$" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM"  , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."DMLTYPE$$" "DMLTYPE$$", "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."SNAPTIME$$" "TIME$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 )  AS OF SNAPSHOT(:B_SCN)  "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."MAXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O','U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$")))  ) SELECT CASE WHEN ddt_1 ='D' AND ddt_2 ='I' THEN 'U' ELSE ddt_1 END "DML$$",         MAX(mtime) "TIME$$"  FROM ( SELECT MIN(dd_type) OVER (PARTITION BY rid, old_new) ddt_1,                MAX(dd_type) OVER (PARTITION BY rid, old_new) ddt_2, mtime         FROM ( SELECT "DLT$"."RID$" rid, "DLT$"."DML$$" dd_type,                       "DLT$"."TIME$$" mtime,                       CASE WHEN "DLT$"."DMLTYPE$$" = 'U' AND "DLT$"."OLD_NEW$$" = 'N'                            THEN 'U' ELSE "DLT$"."OLD_NEW$$" END old_new                       FROM "TMPDLT$_EMP" "DLT$" ) V3 ) V4  GROUP BY CASE WHEN ddt_1 = 'D' AND ddt_2 = 'I' THEN 'U' ELSE ddt_1 END
END OF STMT
PARSE #140204033188792:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=3647348247428
=====================
=====================
PARSING IN CURSOR #140204034084320 len=993 dep=3 uid=0 oct=3 lid=118 tim=3647348248264 hv=91796199 ad='d9b185f8' sqlid='2c0wucs2rjcr7'
/* SQL Analyze(66,2) */ SELECT /*+ RESULT_CACHE(LIFETIME=SESSION, NAME="DMLTYPES:MLOG$_EMP") */ "MAS$"."RID$" "RID$" , "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM", DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."TIME$$" "TIME$$", "MAS$"."DMLTYPE$$" "DMLTYPE$$" FROM (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXSEQ$$" FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM"  , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$, "MAS$"."DMLTYPE$$" "DMLTYPE$$", "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$", "MAS$"."SNAPTIME$$" "TIME$$"  FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 )  AS OF SNAPSHOT(:B_SCN)  "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."MAXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O','U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$")))
END OF STMT
PARSE #140204034084320:c=0,e=249,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=1,plh=0,tim=3647348248264
=====================
   2300 =====================
   2301 PARSING IN CURSOR #140204032608760 len=494 dep=1 uid=118 oct=7 lid=118 tim=3647348266826 hv=2641951102 ad='818f6008' sqlid='1fw0arqfrjyby'
   2302 /* MV_REFRESH (DEL) */ DELETE FROM "SCOTT"."MV_EMP2" "SNA$" WHERE (SYS_OP_MAP_NONNULL("SNA$"."JOB")) IN (SELECT /*+ NO_MERGE  */ SYS_OP_MAP_NONNU        LL("MAS$"."JOB") FROM (SELECT     "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM"   FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0         AND NOT        ("MAS$".DMLTYPE$$ = 'U' AND         (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                                               "M        AS$".change_vector$$) = 0))) AS OF SNAPSHOT(:B_SCN) "MAS$")
   2303 END OF STMT
   2317 =====================
   2318 PARSING IN CURSOR #140204031093032 len=344 dep=2 uid=118 oct=3 lid=118 tim=3647348268913 hv=3983836748 ad='8949aac0' sqlid='b0zdnbgqr91kc'
   2319 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_IN        DEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("MAS$") FULL("MAS$") NO_PARALLEL_INDEX("MAS$") */ 1 AS         C1, 1 AS C2 FROM "SCOTT"."MLOG$_EMP" "MAS$") SAMPLESUB
   2320 END OF STMT
   2464 =====================
   2465 PARSING IN CURSOR #140204032608760 len=775 dep=1 uid=118 oct=2 lid=118 tim=3647348273966 hv=299709598 ad='7fe18be0' sqlid='cp6bupc8xud4y'
   2466 /* MV_REFRESH (INS) */ INSERT  /*+ NOAPPEND */ INTO "SCOTT"."MV_EMP2"("JOB", "SUM_SAL", "AVG_SAL", "MAX_COMM", "MIN_COMM", "CNT_ALL", "CNT_SAL")          SELECT "AV$"."JOB",SUM("AV$"."SAL"),AVG("AV$"."SAL"),MAX("AV$"."COMM"),MIN("AV$"."COMM"),COUNT(*),COUNT("AV$"."SAL") FROM (SELECT  "MAS$".* FROM         "SCOTT"."EMP" "MAS$" WHERE (SYS_OP_MAP_NONNULL("MAS$"."JOB")) IN ( SELECT SYS_OP_MAP_NONNULL("MAS$"."JOB") FROM (SELECT  /*+ HASH_SJ */     "MAS        $"."JOB", "MAS$"."SAL", "MAS$"."COMM"   FROM "SCOTT"."MLOG$_EMP" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :B_ST0 AND NOT        ("MAS$".DMLTYPE$$ = 'U'         AND         (sys.dbms_snapshot_utl.vector_compare(:B_CV0,                                               "MAS$".change_vector$$) = 0))) AS OF SNA        PSHOT(:B_SCN) "MAS$" )) "AV$" GROUP BY "AV$"."JOB"
   2467 END OF STMT
   3215 =====================
   3216 PARSING IN CURSOR #140204120690696 len=54 dep=1 uid=0 oct=7 lid=0 tim=3647348289559 hv=3813164406 ad='d6e7c5f0' sqlid='6n82yazjnhjbq'
   3217 delete from "SCOTT"."MLOG$_EMP" where snaptime$$ <= :1
   3218 END OF STMT

内部SQL

３パターンみたいだけど、使い分けがわからない

MERGE

/* MV_REFRESH (MRG) */ MERGE INTO "SCOTT"."MV_EMP" "SNA$" 
USING (
  SELECT   /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=0)   */ 
    "DLT$0"."JOB" "GB0"
  , SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)* DECODE(("DLT$0"."SAL"), NULL, 0, 1)) "D0"
  , SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)) "D1"
  , NVL(SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)* ("DLT$0"."SAL")), 0) "D2" 
    FROM (
         SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$
              , "MAS$"."JOB", "MAS$"."SAL"
              , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$
              , "MAS$"."DMLTYPE$$" "DMLTYPE$$"
           FROM "SCOTT"."MLOG$_EMP" "MAS$"
          WHERE "MAS$".SNAPTIME$$ > :B_ST0
            AND NOT (   "MAS$".DMLTYPE$$ = 'U' 
                    AND (sys.dbms_snapshot_utl.vector_compare(:B_CV0, "MAS$".change_vector$$) = 0)
                    )
         ) AS OF SNAPSHOT(:B_SCN) "DLT$0"
   GROUP BY "DLT$0"."JOB"
)"AV$" 
ON (SYS_OP_MAP_NONNULL("SNA$"."JOB")=SYS_OP_MAP_NONNULL("AV$"."GB0")) 
WHEN MATCHED THEN 
  UPDATE SET "SNA$"."CNT_SAL"="SNA$"."CNT_SAL"+"AV$"."D0"
           , "SNA$"."CNT_ALL"="SNA$"."CNT_ALL"+"AV$"."D1"
           , "SNA$"."SUM_SAL"=DECODE("SNA$"."CNT_SAL"+"AV$"."D0",0,NULL,NVL("SNA$"."SUM_SAL",0)+"AV$"."D2") 
  DELETE WHERE ("SNA$"."CNT_ALL" = 0) 
WHEN NOT MATCHED THEN 
  INSERT ("SNA$"."JOB", "SNA$"."CNT_SAL", "SNA$"."CNT_ALL", "SNA$"."SUM_SAL") 
  VALUES ( "AV$"."GB0", "AV$"."D0", "AV$"."D1", DECODE ("AV$"."D0", 0, NULL, "AV$"."D2"))
   WHERE ("AV$"."D1" > 0)

INSERT

/* MV_REFRESH (INS) */ 
INSERT  /*+ NOAPPEND */ INTO "SCOTT"."MV_EMP2"("JOB", "SUM_SAL", "AVG_SAL", "MAX_COMM", "MIN_COMM", "CNT_ALL", "CNT_SAL")          
SELECT "AV$"."JOB",SUM("AV$"."SAL"),AVG("AV$"."SAL"),MAX("AV$"."COMM"),MIN("AV$"."COMM"),COUNT(*),COUNT("AV$"."SAL") 
  FROM (SELECT "MAS$".* 
          FROM "SCOTT"."EMP" "MAS$"
         WHERE (SYS_OP_MAP_NONNULL("MAS$"."JOB")) 
               IN ( SELECT SYS_OP_MAP_NONNULL("MAS$"."JOB") 
                      FROM (SELECT  /*+ HASH_SJ */     "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM"   
                              FROM "SCOTT"."MLOG$_EMP" "MAS$"   
                             WHERE "MAS$".SNAPTIME$$ > :B_ST0 
                               AND NOT (    "MAS$".DMLTYPE$$ = 'U' 
                                        AND (sys.dbms_snapshot_utl.vector_compare(:B_CV0, "MAS$".change_vector$$) = 0))
                           ) AS OF SNAPSHOT(:B_SCN) "MAS$" 
                  )
       ) "AV$" GROUP BY "AV$"."JOB"

DELETE

/* MV_REFRESH (DEL) */ 
DELETE FROM "SCOTT"."MV_EMP2" "SNA$" 
WHERE (SYS_OP_MAP_NONNULL("SNA$"."JOB")) IN (
       SELECT /*+ NO_MERGE  */ SYS_OP_MAP_NONNULL("MAS$"."JOB") 
       FROM (SELECT "MAS$"."JOB", "MAS$"."SAL", "MAS$"."COMM"
               FROM "SCOTT"."MLOG$_EMP" "MAS$"   
              WHERE "MAS$".SNAPTIME$$ > :B_ST0
                AND NOT (    "MAS$".DMLTYPE$$ = 'U' 
                         AND (sys.dbms_snapshot_utl.vector_compare(:B_CV0, "MAS$".change_vector$$) = 0)
                        )
            ) AS OF SNAPSHOT(:B_SCN) "MAS$"
        )