マテリアライズドビュー検証 まて マテ マテビュー その１
＜マテリアライズドビュー検証 まて マテ マテビュー その１＞
ペンネーム：クリープ
今回から、マテリアライズドビュー(以後マテビュー)について検証します。
マテビューについては、以前のメルマガ「Full Scanを速くしちゃう」で、
既に取り上げてますので、今回はさらに突っ込んだ形で、マテビューの機能
を検証していきます。題して！ まて マテ マテビュー！！
しばし、お付き合いを・・・
※マテビューには、レプリケーション的な要素とデータウエアハウス的な
要素がありますが、今回はレプリケーションで使用されるような機能につ
いては触れません。レプリケーションについての話を期待された方、あし
からず。。。
■■■■■今回のあらすじ■■■■■
１）マテビューのおさらい
２）リフレッシュの機能検証
１）マテビューのおさらい
それでは、おさらいも兼ねてマテビューについてさらっと確認。
マテビューとはその名の通り、実際にデータが存在しているビューのこと。
データウエアハウスでは、月次データなどを蓄積している集計テーブルと
同等です。では、集計テーブルと何が違うのか。ポイントは２つ。
１．集計表への更新負荷削減。
２．テーブル構成の変更などメンテナンスが容易。
これらの機能を実現するためのものが、それぞれリフレッシュ、クエリー
リライトという機能です。
■リフレッシュ
マテビューの元になるマスター表が変更された場合に、
その変更をマテビューに反映する。
※以下も参照
「Full Scanを速くしちゃう」 その８
https://old.insight-tec.com/mailmagazine/ora3/vol256.html
■クエリーリライト
ユーザーやアプリケーションが発行するSQL文をコストベース・オプティ
マイザが解析し、自動的にSQL文を書き換える。
※以下も参照
「Full Scanを速くしちゃう」 その７
https://old.insight-tec.com/mailmagazine/ora3/vol255.html
ここからは、それぞれの機能に焦点を絞ってみていきます。
２）リフレッシュ機能検証
リフレッシュの主な機能は以下の通り。
■リフレッシュ・オプション
・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文が使用されてるじゃあ～りませんか！
ポイントは、後ろから２つ目のatomic_refreshという引数。この引数がTRUEの
場合、１つ目の引数で指定したマテビュー全てに対して単一のトランザクション
でリフレッシュします。（１つ目の引数ではテーブルが複数指定することが
できます）
この値がFALSEの場合、マテビューごとに別のトランザクションとしてリフレッ
シュされます。この場合はDelete文ではなくTruncate文が使用されます。
デフォルトはTRUEですので、単一のマテビューをリフレッシュするときなどは
この引数を確認した方がいいでしょう。
次回も引き続きリフレッシュの機能について見ていきます。
鄙びた温泉で完全リフレッシュ。したい。 恵比寿にて