マテリアライズドビュー検証 まて マテ マテビュー その3

<マテリアライズドビュー検証 まて マテ マテビュー その3>
ペンネーム:クリープ

今回も前回に引き続きMLOGの検証をしていきます。
前回の宿題!?だった、with primary keyの場合のMLOGの更新について、前回
同様意地悪しながら確認してみましょう。

■■■■■今回のあらすじ■■■■■
1)MLOG with primary key
2)更新可能マテリアライズドビュー

■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

1)MLOG with primary key
ということで、まずは、前回の確認をしてみましょう
前回作成したログは以下の通り。

SQL> create materialized view log on emp;

SQL> desc mlog$_emp;
名前                                      NULL?    型
----------------------------------------- -------- --------------------
EMPNO                                              NUMBER(4)
SNAPTIME$$                                         DATE
DMLTYPE$$                                          VARCHAR2(1)
OLD_NEW$$                                          VARCHAR2(1)
CHANGE_VECTOR$$                                    RAW(255)

作成されたMLOG$_EMPを見ると、変更前後の情報を保持してるわけではなく、
主キーであるEMPNOのみが保存されています。
これでは、値が変更されても差分情報での更新はできないはず。
ということは、保存されている主キー列から元表を参照し、その値を更新し
ているということでしょうか。
それでは、実際に検証してみましょう。

例のごとく、まずは環境作成。

SQL> create materialized view log on emp;

SQL> insert into emp values
    (1, 'TOM', 'PART-TIME', null, sysdate, 300, null, 10 );

SQL> insert into emp values
    (2, 'COLIN', 'PART-TIME', null, sysdate, 400, null, 10 );

SQL> commit;

SQL> create materialized view mv_parttime refresh fast
    as select * from emp where job = 'PART-TIME';

SQL> select * from mv_parttime;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL  COMM     DEPTNO
---------- ---------- --------- ---- -------- ---------- ----- ----------
         1 TOM        PART-TIME      06-02-18        300               10
         2 COLIN      PART-TIME      06-02-18        400               10

SQL> update emp set ename = 'JONNY' where empno = 2;

SQL> commit;

SQL> select * from mlog$_emp;

     EMPNO SNAPTIME D O CHANGE_VECTOR$$
---------- -------- - - ---------------
         2 00-01-01 U U 0400

UPDATEされた情報がMLOG$_EMPテーブルに更新されました。この状態でリフレ
ッシュすれば、ENAME=2のENAMEは「JONNY」になります。
それでは、またまた意地悪。

SQL> update mlog$_emp set empno = 1 where empno = 2;

SQL> commit;

SQL> select * from mlog$_emp;

     EMPNO SNAPTIME D O CHANGE_VECTOR$$
---------- -------- - - ---------------
         1 00-01-01 U U 0400

MLOG$_EMPのEMPNOを2から1に変更してみました。
ここでリフレッシュしたら、EMPNO=1の「TOM」が「JONNY」になるのでは???

SQL> exec dbms_mview.refresh( 'MV_PARTTIME' );

SQL> select * from mv_parttime;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL  COMM     DEPTNO
---------- ---------- --------- ---- -------- ---------- ----- ----------
         1 TOM        PART-TIME      06-02-18        300               10
         2 COLIN      PART-TIME      06-02-18        400               10

期待に反して、何も変更されませんでした。しかも、EMPNO=2の情報も更新さ
れてません。今のリフレッシュでMLOGは一体何をしたのでしょうか。
意地悪しすぎて、すねちゃった???

=====================
PARSING IN CURSOR #30 len=152 dep=2 uid=54 oct=6 lid=54 tim=29311455826
 hv=1591683701 ad='6cd95e4c'
UPDATE "SCOTT"."MV_PARTTIME" SET "EMPNO" = :1,"ENAME" = :2,"JOB" = :3,
"MGR" = :4,"HIREDATE" = :5,"SAL" = :6,"COMM" = :7,"DEPTNO" = :8 WHERE
"EMPNO" = :1
END OF STMT

リカーシブコールを確認すると、ちゃんとUPDATE文が実行されていました。

っということは。。。
MLOG$_EMPの主キーを参照して、元表の主キー値の行をマテビューの主キー値
の行に更新している(同じ値のため、何も変更されていないようにみえる)
ということでは???

と、この動きを確認したいのですが、その為には、

・元表とマテビューの値が異なるデータ
・MLOGにその変更データのログが挿入されていない

という条件が必要になります。
通常の更新ではMLOGにログが挿入されてしまいますので、マテビューの値を
変更する必要があります。
ということで、マテビューに細工をして、更新できるようにしちゃいましょう。

2)更新可能マテリアライズドビュー
この機能を使うことで、文字通りマテビューを更新することができるように
なります。
更新可能にするには、マテビュー作成時にfor update句を追加するだけ。

ということで、マテビュー再作成!

SQL> drop materialized view mv_parttime;

SQL> update emp set ename = 'COLIN' where empno = 2;

SQL> commit;

SQL> create materialized view mv_parttime refresh fast
    for update as select * from emp where job = 'PART-TIME';

SQL> select * from mv_parttime;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL  COMM     DEPTNO
---------- ---------- --------- ---- -------- ---------- ----- ----------
         1 TOM        PART-TIME      06-02-18        300               10

         2 COLIN      PART-TIME      06-02-18        400               10

SQL> update emp set ename = 'JONNY' where empno = 2;

SQL> commit;

SQL> update mlog$_emp set empno = 1 where empno = 2;

SQL> select * from mlog$_emp;

     EMPNO SNAPTIME D O CHANGE_VECTOR$$
---------- -------- - - --------------------------------------
         1 00-01-01 U U 0400

リフレッシュ一歩手前の環境が整いました。先ほどの検証環境と同じです。
違うのは、マテビューが更新できるかどうか。ではここで、マテビューを更新
してみましょう。

SQL> update mv_parttime set ename = 'PHIL', HIREDATE=NULL where empno = 1;

SQL> commit;

SQL> select * from mv_parttime;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL  COMM     DEPTNO
---------- ---------- --------- ---- -------- ---------- ----- ----------
         1 PHIL       PART-TIME      06-02-18        300               10

         2 COLIN      PART-TIME      06-02-18        400               10

マテビューのEMPNO=1のENAMEがPHILに変更されました。
ちょっとわかりづらくなってしまったので、まとめると以下の通り。

■EMPNO = 1
	EMP			:TOM
	MV_PARTTIME	:PHIL

■EMPNO = 2
	EMP			:JONNY
	MV_PARTTIME	:COLIN

つまり、2行の名前全てが違うということ。これなら、どこに何が更新された
かがわかるはず。
では、リフレッシュ!

SQL> exec dbms_mview.refresh( 'MV_PARTTIME' );

SQL> select * from mv_parttime;

     EMPNO ENAME      JOB        MGR HIREDATE        SAL  COMM     DEPTNO
---------- ---------- --------- ---- -------- ---------- ----- ----------
         1 TOM        PART-TIME      06-02-18        300               10

         2 COLIN      PART-TIME      06-02-18        400               10

予想通り!?ENAME=1の値がTOMに更新されました。
つまり、MLOG$_EMPの主キーの値を参照して元表の主キー値の行を取得し、その
値をマテビューの主キー値に更新しています。

言い換えれば、MLOG$_EMPの主キー値が、元表とマテビューをつなぐ「パイプ」
のような役割をしているといえるでしょう。

以上、2回に渡ってMLOGの動作検証を行ってきました。
MLOGがどのような役割をしているかが(大体)理解できたと思います。

次回は本丸の高速リフレッシュを見てみましょう。

春が待ち遠しい、インサイトハイキング部所属。 恵比寿にて。