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

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

前回の完全リフレッシュに続き、今回もリフレッシュについて検証していき
ます。今回から、高速リフレッシュ。まずは、マテリアライズドビューログ
を中心に見てみましょう。

■■■■■今回のあらすじ■■■■■
1)マテリアライズドビューログについて
2)MLOG$_XXX[元表]

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

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

変更処理実行時、元表の変更された情報を管理しているのが、マテリアライズ
ドビューログ(以後、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;

となります。

–補足1。オプションについて。

■with句
元表更新時にマテリアライズドビューログに記録する情報を指定
・PRIMARY KEY
更新される全ての行の主キーを記録
・ROWID
更新される全ての行のROWIDを記録
・SEQUENCE
更新時の順序番号を記録
集計を含むマテビューがある場合指定する必要あり

■including/excluding new values句
元表更新時に、更新前情報と更新後情報を記録するかどうかを指定
・including
更新前、更新後の情報を記録
集計を含むマテビューがある場合指定する必要あり
・excluding
更新前情報のみ記録

–補足2。作成されたテーブル構成は以下の通り。

■デフォルト(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$$というカラムが作成されます。

2)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

2行のデータが作成されています。
SEQUENCE$$ = 1000001が更新前、SEQUENCE$$ = 100002更新後のログとなります。
ちなみに、先ほどログ作成時に指定したincluding new valuesを指定しないと、
更新前のデータ(SEQUENCE$$ = 1000001)1行のみが保存されます。

この状態でリフレッシュを実行すれば、通常、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の値をマイナスするかプラスするかを
処理し、マテビューにプラスしてます。
この方法であれば、1行更新されただけで、再度集計し直すというような
ことをせずに、高速にリフレッシュすることができます。

補足として、先ほど、集計などを行っているマテビューで高速リフレッシュを
使用する場合、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のような更新された値を保持する
カラムがありません。
ですが、高速リフレッシュをすればちゃんとマテビューに更新されます。
さて、このとき、どのようにマテビューに更新しているのでしょうか。

続きは来週。。。

在庫チョコ週末大人買い計画、遂行予定。 恵比寿にて。