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

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

今回から、マテリアライズドビュー(以後マテビュー)について検証します。

マテビューについては、以前のメルマガ「Full Scanを速くしちゃう」で、
既に取り上げてますので、今回はさらに突っ込んだ形で、マテビューの機能
を検証していきます。題して! まて マテ マテビュー!!

しばし、お付き合いを・・・

※マテビューには、レプリケーション的な要素とデータウエアハウス的な
要素がありますが、今回はレプリケーションで使用されるような機能につ
いては触れません。レプリケーションについての話を期待された方、あし
からず。。。

■■■■■今回のあらすじ■■■■■
1)マテビューのおさらい
2)リフレッシュの機能検証

1)マテビューのおさらい
それでは、おさらいも兼ねてマテビューについてさらっと確認。

マテビューとはその名の通り、実際にデータが存在しているビューのこと。
データウエアハウスでは、月次データなどを蓄積している集計テーブルと
同等です。では、集計テーブルと何が違うのか。ポイントは2つ。

1.集計表への更新負荷削減。
2.テーブル構成の変更などメンテナンスが容易。

これらの機能を実現するためのものが、それぞれリフレッシュ、クエリー
リライトという機能です。

■リフレッシュ
マテビューの元になるマスター表が変更された場合に、
その変更をマテビューに反映する。

※以下も参照
「Full Scanを速くしちゃう」 その8
http://www.insight-tec.com/mailmagazine/ora3/vol256.html

■クエリーリライト
ユーザーやアプリケーションが発行するSQL文をコストベース・オプティ
マイザが解析し、自動的にSQL文を書き換える。

※以下も参照
「Full Scanを速くしちゃう」 その7
http://www.insight-tec.com/mailmagazine/ora3/vol255.html

ここからは、それぞれの機能に焦点を絞ってみていきます。

2)リフレッシュ機能検証

リフレッシュの主な機能は以下の通り。

■リフレッシュ・オプション
・complete
完全リフレッシュ。文字通り、全データ削除後データを入れ直す。
・fast
高速リフレッシュ。前回からの増分のみをリフレッシュ。
・never
リフレッシュされない。
・force(デフォルト)
可能な場合はfast。それ以外はcomplete。

■リフレッシュ・モード
・on commit
データ更新時、マテビューに自動で更新
・on demand(デフォルト)
手動で更新

まずは、動きが理解しやすい完全リフレッシュから検証します。

完全リフレッシュは、全てのデータを削除後にデータをInsertしてます。
では、実際に確認!

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

--マテビュー作成

sqlplus scott/tiger

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


--Insert

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

今回作成したマテビューは、リフレッシュ・モードを指定してないので(on
demand)この段階ではマテビューにデータが反映されてません。

では、いざリフレッシュ!

SQL> select * from mv_emp where job = 'PART-TIME';

レコードが選択されませんでした。

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

SQL> select * from mv_emp where job = 'PART-TIME';

JOB          SUM_SAL
--------- ----------
PART-TIME        300

正常にリフレッシュされました。
では、この時にOracle内部ではどのようなことが行われているでしょうか。
リカーシブコールを確認。

--sql_traceから抜粋。
=====================
PARSING IN CURSOR #26 len=51 dep=1 uid=57 oct=7 lid=57 tim=7972577575
 hv=3309992133 ad='690e86ac'
/* MV_REFRESH (DEL) */ delete from "SCOTT"."MV_EMP"
END OF STMT

=====================
PARSING IN CURSOR #26 len=176 dep=1 uid=57 oct=2 lid=57 tim=7972585058
 hv=4080544272 ad='69125ef4'
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
"SCOTT"."MV_EMP"("JOB","SUM_SAL") SELECT "EMP"."JOB",SUM("EMP"
."SAL") FROM "EMP" "EMP" GROUP BY "EMP"."JOB"
END OF STMT

マテビューの削除にDelete文が。。。

10g以前では、Truncate文が使用されていましたが、10gからはデフォルトで
Delete文が使用されるようになったようです。
大量データが格納されていることが多いマテビューでは、レスポンス時間に
大きな影響が出てしまう可能性が非常に高いのでは!?

そこで、dbms_mview.refreshを以下のようにして実行してみると・・・

SQL> exec dbms_mview.refresh('MV_EMP', 'c', NULL,
     TRUE, FALSE, 1, 0, 0, FALSE, FALSE);

--sql_traceから抜粋。
=====================
PARSING IN CURSOR #11 len=73 dep=1 uid=57 oct=85 lid=57 tim=8585867163
 hv=3842965948 ad='68c894d4'
/* MV_REFRESH (DEL) */ truncate table "SCOTT"."MV_EMP"
 purge snapshot log
END OF STMT


=====================
PARSING IN CURSOR #11 len=205 dep=1 uid=57 oct=2 lid=57 tim=8589132085
 hv=2547952802 ad='686fd398'
/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND
SKIP_UNQ_UNUSABLE_IDX */ INTO "SCOTT"."MV_EMP"("JOB","SUM_SAL") SELECT
 "EMP"."JOB",SUM("EMP"."SAL") FROM "EMP" "EMP" GROUP BY "EMP"."JOB"
END OF STMT

なんと!Truncate文が使用されてるじゃあ~りませんか!

ポイントは、後ろから2つ目のatomic_refreshという引数。この引数がTRUEの
場合、1つ目の引数で指定したマテビュー全てに対して単一のトランザクション
でリフレッシュします。(1つ目の引数ではテーブルが複数指定することが
できます)
この値がFALSEの場合、マテビューごとに別のトランザクションとしてリフレッ
シュされます。この場合はDelete文ではなくTruncate文が使用されます。

デフォルトはTRUEですので、単一のマテビューをリフレッシュするときなどは
この引数を確認した方がいいでしょう。

次回も引き続きリフレッシュの機能について見ていきます。

鄙びた温泉で完全リフレッシュ。したい。 恵比寿にて