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

＜マテリアライズドビュー検証まてマテマテビュー　その５＞
ペンネーム：クリープ

前回に引き続き高速リフレッシュについて見ていきます。
今回は、OracleのパッケージであるDBMS_MVIEW.EXPLAIN_MVIEWを使って、高速
リフレッシュの制限を確認します。
とその前に、まずは前回やり残してたcount(sal)について、見てみましょう。

■■■■■今回のあらすじ■■■■■
１）制限：count(sal)
２）DBMS_MVIEW.EXPLAIN_MVIEW

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

１）制限：count(sal)
※前回作成したマテビューおさらい

SQL> create materialized view mv_parttime refresh fast
     as select job, sum(sal) sum_sal, count(sal) cnt_sal, count(*) cnt_all
     from emp group by job;

まずは前回の宿題から。

上記マテビューにcount(sal)がないと、前回のcount(*)のようにUPDATE文や
DELETE文でエラーが発生するのは何故か、というのが前回の宿題でした。
この制限は、集計項目（今回の場合はsal）がNOT NULL制約でない場合のみの
制限になっています。ってことは、NULLデータが何か関係しているのかな！？

リカーシブコールを確認してみると、MERGE文で以下のような計算が行われて
いました。

※関係する箇所のみ抜粋。

WHEN MATCHED THEN UPDATE SET
SUM_SAL=DECODE(CNT_SAL+[式１], 0, NULL, NVL(SUM_SAL,0)+[式２])

[式１]
SUM(DECODE(MLOG$_EMP.OLD_NEW$$, 'N', 1, -1) * DECODE(MLOG$_EMP.SAL, NULL, 0, 1))

[式２]
NVL(SUM(DECODE(MLOG$_EMP.OLD_NEW$$, 'N', 1, -1) * MLOG$_EMP.SAL), 0)

よくわからんですね、これだけじゃ。。。説明します。

今回のテーマであるcount(sal)の項目cnt_salは、MERGE文のUPDATE句で使用さ
れています。UPDATE句では、CNT_SAL + [式１]が0の場合にSUM_SALの項目に
NULLをセットしています。
[式１]では、元表に追加/削除された行数を数えて集計しています。
（行追加：*1、行削除：* -1）

つまり、元表でNULL以外の行数が0行の場合、マテビューのSUM_SALの値にNULL
をセットする。極端な例でいうと、EMP表に10行あってそのSALの値が全てNULL
だった場合に、マテビューの値をNULLに更新している、ということになります。

count(*)もそうでしたが、高速リフレッシュでは通常の集計の値と同じにする
為に裏でいろいろと処理しているようです。

２）DBMS_MVIEW.EXPLAIN_MVIEW
以上、高速リフレッシュの制限について見てきました。高速リフレッシュには
これ以外にもいくつかの制限が存在します。

REFRESHオプションをデフォルトのFORCEに設定しているような環境の場合、実
は完全リフレッシュしか実行されていなかった、なんてことがありますので、
マテビューが高速リフレッシュ可能かどうか事前に確認しておきましょう。

DBMS_MVIEW.EXPLAIN_MVIEWを使用することで、既存のマテビュー及び作成予定
のSQL文を指定して、高速リフレッシュ可能かどうかを確認することができます。

まずは、前回の検証で使用したマテビューでDBMS_MVIEW.EXPLAIN_MVIEWを実行
してみます。

SQL> @?/rdbms/admin/utlxmv.sql

SQL> create materialized view log on emp with rowid( job, sal )
     including new values;

SQL> create materialized view mv_parttime refresh fast
     as select job, sum(sal) sum_sal from emp group by job;

SQL> exec dbms_mview.explain_mview( 'mv_parttime' );

SQL> select capability_name, possible, related_text, msgtxt
     from mv_capabilities_table where capability_name Like '%REFRESH%'
     and capability_name not Like '%PCT%';

※PCTはパーティション単位での高速リフレッシュの可否についてであり、今
  回はパーティション表ではないので割愛しています。

CAPABILITY_NAME               POS REL_TEXT  MSGTXT
----------------------------- --- --------- ------------------------------------
REFRESH_COMPLETE              Y
REFRESH_FAST                  Y
REFRESH_FAST_AFTER_INSERT     Y
REFRESH_FAST_AFTER_ONETAB_DML N   SUM_SAL   COUNT(expr)のないSUM(expr)
REFRESH_FAST_AFTER_ONETAB_DML N             選択リストにCOUNT(*)が存在しません
REFRESH_FAST_AFTER_ANY_DML    N   SCOTT.EMP mvログには順序番号がありません
REFRESH_FAST_AFTER_ANY_DML    N             REFRESH_FAST_AFTER_ONETAB_DMLを
使用できない理由を参照してください

DBMS_MVIEW.EXPLAIN_MVIEWを実行することで、POSSIBLEやMSGTXT等の情報が、
MV_CAPABILITIES_TABLEというテーブルに格納されます。
POSSIBLEには指定したマテビューが高速リフレッシュ可能かどうかが、MSGTXT
には高速リフレッシュできない理由がそれぞれ入っています。
※MV_CAPABILITIES_TABLEは、utlxmv.sqlを実行することで作成されます。

possibleの値が「Y」なら高速リフレッシュ可能ということを表しています。

今回実験したマテビューは、count(*)、count(sal)列がないので、REFRESH_F
AST_AFTER_INSERT（INSERT文の後の高速リフレッシュ）が「Y」、その他の高
速リフレッシュが「N」になっています。

と、ここで気になるのが、REFRESH_FAST_AFTER_ONETAB_DMLとREFRESH_FAST_A
FTER_ANY_DMLという項目。直訳すると、それぞれ、単一テーブルへ更新した後
の高速リフレッシュ、複数テーブルへ更新した後の高速リフレッシュという意
味になります。

これらは何故別項目になっているのでしょうか。
複数テーブルに更新した後に高速リフレッシュができない、ということなので
しょうか。

と、MV_CAPABILITIES_TABLEをよくみると、「mvログには順序番号がありません」
というメッセージが出力されています。

制限をマニュアルを確認すると、

混在型のDML操作（複数の表に対するINSERT、UPDATEまたはDELETE操作の組
合せ）を実行しないことが確実でないかぎり、キーワードSEQUENCEをマテリ
アライズド・ビュー・ログ文に含めることをお薦めします。
※データウェアハウス・ガイド参照

と、わかりづらい説明がありました。
MLOGにはSEQUENCE句をつけた方がよいということのようです。ってことで、
SEQUENCE句を付けて再チャレンジ！

SQL> exec dbms_mview.refresh( 'mv_parttime' );
※MLOGを初期化しておきます。

SQL> truncate table mv_capabilities_table;

SQL> alter materialized view log on emp add sequence;

SQL> exec dbms_mview.explain_mview( 'mv_parttime' );

SQL> select capability_name, possible, related_text, msgtxt
     from mv_capabilities_table where capability_name Like '%REFRESH%'
     and capability_name not Like '%PCT%';

CAPABILITY_NAME               POS REL_TEXT   MSGTXT
----------------------------- --- --------   -------------------------
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REFRESH_FAST_AFTER_INSERT      Y
REFRESH_FAST_AFTER_ONETAB_DML  Y
REFRESH_FAST_AFTER_ANY_DML     Y

SEQUENCE句をつけることで、先ほどのメッセージは消えました。

とこのように、DBMS_MVIEW.EXPLAIN_MVIEWを使用することで高速リフレッシュ
の制限に引っかかってないか確認することができます。高速リフレッシュを利
用する際にはこのパッケージを利用してマテビューを確認した方がよいでし
ょう。

また、SEQUENCE句をつけないと複数のテーブルの更新時に高速リフレッシュさ
れないことがありますので、MLOG作成の際は、SEQUENCE句をつけ忘れないよう
気をつけましょう。

次回は、クエリーリライトの機能について検証します。

インサイトオープンワールド In 白木屋　随時開催！？　恵比寿にて。

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

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