マテリアライズドビュー検証 まて マテ マテビュー その３
＜マテリアライズドビュー検証 まて マテ マテビュー その３＞
ペンネーム：クリープ
今回も前回に引き続きMLOGの検証をしていきます。
前回の宿題！？だった、with primary keyの場合のMLOGの更新について、前回
同様意地悪しながら確認してみましょう。
■■■■■今回のあらすじ■■■■■
１）MLOG with primary key
２）更新可能マテリアライズドビュー
■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
１）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にログが挿入されてしまいますので、マテビューの値を
変更する必要があります。
ということで、マテビューに細工をして、更新できるようにしちゃいましょう。
２）更新可能マテリアライズドビュー
この機能を使うことで、文字通りマテビューを更新することができるように
なります。
更新可能にするには、マテビュー作成時に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がどのような役割をしているかが（大体）理解できたと思います。
次回は本丸の高速リフレッシュを見てみましょう。
春が待ち遠しい、インサイトハイキング部所属。 恵比寿にて。