マテリアライズドビューログとは
高速リフレッシュは完全リフレッシュとは異なり、元表に変更があった箇所のみをマテビューに反映する機能。データを集計するような場合、マテビューを再計算しているわけではなく、変更時の差分のみがマテビューに適用されるので、通常、完全リフレッシュよりも高速に処理される。
変更処理実行時、元表の変更された情報を管理しているのが、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$"
)