マテリアライズドビュー検証 まて マテ マテビュー その２
＜マテリアライズドビュー検証 まて マテ マテビュー その２＞
ペンネーム：クリープ
前回の完全リフレッシュに続き、今回もリフレッシュについて検証していき
ます。今回から、高速リフレッシュ。まずは、マテリアライズドビューログ
を中心に見てみましょう。
■■■■■今回のあらすじ■■■■■
１）マテリアライズドビューログについて
２）MLOG$_XXX[元表]
■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
１）マテリアライズドビューログについて
高速リフレッシュは完全リフレッシュとは異なり、元表に変更があった箇所
のみをマテビューに反映する機能です。データを集計するような場合、
マテビューを再計算しているわけではなく、変更時の差分のみがマテビューに
適用されるので、通常、完全リフレッシュよりも高速に処理されます。
変更処理実行時、元表の変更された情報を管理しているのが、マテリアライズ
ドビューログ（以後、MLOG）です。
そのため、高速リフレッシュを使用するには、MLOGの作成が必須となります。
ではまず、MLOGを作成！
SQL> create materialized view log on emp;
上記コマンドを実行することで、MLOG$_XXX[元表]というテーブルが作成され
ます。元表更新時には、このテーブルに情報が更新されます。
MLOG作成に当たってのポイント
高速リフレッシュを利用するためには、with句にrowidを指定する必要があり
ます。
上記コマンドではwith句を指定していない為、デフォルトのprimary keyが
設定されたことになります。
また、集計を行っているマテビューがある場合は、with句にsequence、
参照されている全てのカラム、including newvalues句を指定しなければ
なりません。
これらを踏まえて、前回の検証で作成したマテビューを高速リフレッシュ
できるようにする場合、
SQL> create materialized view log on emp with sequence, rowid( job, sal )
including new values;
となります。
–補足１。オプションについて。
■with句
元表更新時にマテリアライズドビューログに記録する情報を指定
・PRIMARY KEY
更新される全ての行の主キーを記録
・ROWID
更新される全ての行のROWIDを記録
・SEQUENCE
更新時の順序番号を記録
集計を含むマテビューがある場合指定する必要あり
■including/excluding new values句
元表更新時に、更新前情報と更新後情報を記録するかどうかを指定
・including
更新前、更新後の情報を記録
集計を含むマテビューがある場合指定する必要あり
・excluding
更新前情報のみ記録
–補足２。作成されたテーブル構成は以下の通り。
■デフォルト（with primary key）の場合
（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)
■with rowidの場合
create materialized view log on emp with sequence, rowid( job, sal ) including new values; SQL> desc mlog$_emp; 名前 NULL? 型 ----------------------------------------- -------- -------------------- JOB VARCHAR2(9) SAL NUMBER(7,2) M_ROW$ VARCHAR2(255) SEQUENCE$ NUMBER SNAPTIME$ DATE DMLTYPE$ VARCHAR2(1) OLD_NEW$ VARCHAR2(1) CHANGE_VECTOR$ RAW(255)
with句にprimary keyを指定した場合は主キーのempnoカラムが、rowidを指定
した場合はM_ROW$$というカラムが作成されます。
２）MLOG$_XXX[元表]
MLOGが無事作成されました。
では、MLOGはどのようにして高速リフレッシュの機能を実現しているので
しょうか。
MLOGの実態である、MLOG$_EMPの中身をみながら、マテビューをどのように更
新しているかを見てみましょう。
まずはマテビューを作成。
–環境作成
※MLOGは先ほど作成したwith rowid句で作成したものを使用します。
また、データも前号で作成したものをそのまま利用してます。
SQL> create materialized view mv_emp refresh fast
as select job, sum(sal) sum_sal, count(*) cnt_all, count(sal) cnt_sal
from emp where job = 'PART-TIME' group by job;
SQL> select * from mv_emp;
JOB SUM_SAL CNT_ALL CNT_SAL
--------- ---------- ---------- ----------
PART-TIME 300 1 1
前号で使用したマテビューを高速リフレッシュ可能な形に変更してます。
count(*)とcount(column)は、SUM関数が使用されているマテビューを高速リフ
レッシュを行う場合に記述する必要があります。
では、元表のデータを変更。
SQL> update emp set sal = 400 where empno = 1;
TOMさんのSALARYが300から400へUPされました。
この時のMLOG$_EMPテーブルを確認すると、
SQL> select * from mlog$_emp; JOB SAL M_ROW$ SEQUENCE$ SNAPTIME D O CHANGE_VECTOR$ --------- ----- ------------------- ---------- -------- - - --------------- PART-TIME 300 AAAM1FAAEAAAAGgAAB 100001 00-01-01 U U 4000 PART-TIME 400 AAAM1FAAEAAAAGgAAB 100002 00-01-01 U N 4000
２行のデータが作成されています。
SEQUENCE$$ = 1000001が更新前、SEQUENCE$$ = 100002更新後のログとなります。
ちなみに、先ほどログ作成時に指定したincluding new valuesを指定しないと、
更新前のデータ（SEQUENCE$$ = 1000001）１行のみが保存されます。
この状態でリフレッシュを実行すれば、通常、TOMさんのSALは400に変更されます。
と、ここで、ちょっと意地悪を。
SQL> update mlog$_emp set sal = 350 where SEQUENCE$ = 100001; SQL> select * from mlog$_emp; JOB SAL M_ROW$ SEQUENCE$ SNAPTIME D O CHANGE_VECTOR$ --------- ----- ------------------- ---------- -------- - - --------------- PART-TIME 350 AAAM1FAAEAAAAGgAAB 100001 00-01-01 U U 4000 PART-TIME 400 AAAM1FAAEAAAAGgAAB 100002 00-01-01 U N 4000
更新前の情報をおさわりしてみました。すると。。。
SQL> exec dbms_mview.refresh( 'MV_EMP' ); SQL> select * from mv_emp; JOB SUM_SAL CNT_ALL CNT_SAL --------- ---------- ---------- ---------- PART-TIME 350 1 1
400となるはずが、350と、50マイナスされてしまいました。。。
内部処理をみると、merge文を使用して、以下のような計算を行っています。
※実際はちょっと違うのですが、わかりやすく補正してます。
“SUM_SAL” （MV_EMPテーブルのSUM_SAL）
+
SUM(DECODE(“OLD_NEW$$”, ‘N’, 1, -1) * (“SAL”)) （MLOG$_EMP）
MLOG$_EMPのOLD_NEW$$の値を見て、SALの値をマイナスするかプラスするかを
処理し、マテビューにプラスしてます。
この方法であれば、１行更新されただけで、再度集計し直すというような
ことをせずに、高速にリフレッシュすることができます。
補足として、先ほど、集計などを行っているマテビューで高速リフレッシュを
使用する場合、including new values句が必須とありましたが、これは、
更新後のデータがないと差分情報で計算できなくなってしまうという理由から
なのでしょう。
また、おさわりされてしまったデータですが、元表を更新しても、マテビューの
情報を元に差分を計算し続けるため、おさわりされたままになってしまいます。
完全リフレッシュして正しい状態にしてあげましょう。
SQL> exec dbms_mview.refresh( 'MV_EMP', 'C' ); SQL> select * from mv_emp; JOB SUM_SAL CNT_ALL CNT_SAL --------- ---------- ---------- ---------- PART-TIME 400 1 1
今回はMLOG$_EMPの更新について見てきたわけですが、冒頭で確認したprimary
keyのMLOG$_EMPを見て何か違和感を感じた方がいると思います。
SQL> desc mlog$_emp; 名前 NULL? 型 ----------------------------------------- -------- -------------------- EMPNO NUMBER(4) SNAPTIME$ DATE DMLTYPE$ VARCHAR2(1) OLD_NEW$ VARCHAR2(1) CHANGE_VECTOR$ RAW(255)
テーブル構成を見ると、MLOG$_EMPには、SALのような更新された値を保持する
カラムがありません。
ですが、高速リフレッシュをすればちゃんとマテビューに更新されます。
さて、このとき、どのようにマテビューに更新しているのでしょうか。
続きは来週。。。
在庫チョコ週末大人買い計画、遂行予定。 恵比寿にて。