マテリアライズドビュー検証 まて マテ マテビュー その４
＜マテリアライズドビュー検証 まて マテ マテビュー その４＞
ペンネーム：クリープ
前回、前々回と、MLOGについて検証しました。
今回は、高速リフレッシュについてです。この機能を使ったことがある人で
あれば、必ず疑問に思うであろう制限のしくみについて見てみましょう。
■■■■■今回のあらすじ■■■■■
１）高速リフレッシュ制限について
２）制限：COUNT(*)が、SELECT句に必要。
■環境
Microsoft Windows XP Pro
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
１）高速リフレッシュの制限について
高速リフレッシュを使用されている方ならご存知と思いますが、この機能を
利用するにはいくつかの制限があります。
中でも、通常の集計SQL文では間違いなく無いと思われる制限が以下ではない
でしょうか。
COUNT(*)が、SELECT句に必要。
※ 集計関数が使用されているマテビューの場合
マテビューの機能を使ったことがない、又は高速リフレッシュを使ってない
人にしてみれば、「なんで必要なの？」と思うのでは？？？
高速リフレッシュの機能を使用してても、この制限の意味はわからずに、お
まじないのようにSELECT句に追加している人も多いのではないでしょうか。
ということで、ここからはこの疑問を解消すべく、検証を行って行きたいと
思います。
２）制限：COUNT(*)が、SELECT句に必要。
それでは早速、COUNTの制限について確認していきましょう。
マテビューで使用できる集計関数は以下の通り。
・SUM, AVG, STDDEV, VARIANCE, COUNT, MIN, MAX
これらの関数を使用した場合、COUNT(*)をつけないと高速リフレッシュする
ことができません。
実際に確認。
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> insert into emp values
(3, 'ED', 'PART-TIME', null, sysdate, 200, null, 10 );
SQL> commit;
SQL> exec dbms_mview.refresh('mv_parttime');
PL/SQLプロシージャが正常に完了しました。
？？？
できちゃいました。。。
上記のようなマテビューでも問題なく高速リフレッシュすることができるよう
です。もしかして、マニュアル間違えてる？？？確認確認。。。
すべてのタイプの高速リフレッシュを保証するには、常にCOUNT(*)が必要で
す。さもないと、挿入後の高速リフレッシュのみに制限される場合がありま
す。
※ データウェアハウス・ガイド参照
「挿入後の高速リフレッシュ」のみ、つまりInsert文のみ可能ということの
ようです。なんだか、不思議な制限。。。
SQL> update emp set sal = 230 where empno = 3;
SQL> commit;
SQL> exec dbms_mview.refresh('mv_parttime');
行1でエラーが発生しました。:
ORA-32314: "SCOTT"."MV_PARTTIME"のREFRESH
FASTは削除/更新の後ではサポートされていません。
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2255
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2461
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2430
ORA-06512: 行1
マニュアル通り！？UPDATE文ではエラーになりました。削除/更新とあるので、
きっと、DELETE文も同様なのでしょう。
と、通常の動きを確認したところで、count(*)を追加したマテビューを作成。
SQL> drop materialized view mv_parttime;
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;
SQL> update emp set sal = 200 where empno = 3;
SQL> commit;
SQL> exec dbms_mview.refresh('mv_parttime');
PL/SQLプロシージャが正常に完了しました。
count(*)を追加することで、先ほど失敗したUPDATE文も問題なく処理するこ
とができました。
これはつまり、高速リフレッシュで集計する場合、count(*)はなくてはなら
ない、いわば「コーヒーにクリープ」な存在と言えるでしょう！？
しかし、何故、count(*)が必要なのでしょうか。
困った時のリカーシブコール！
===================== PARSING IN CURSOR #6 len=83 dep=1 uid=54 oct=7 lid=54 tim=16117843501 hv=1646961049 ad='6ccc529c' /* MV_REFRESH (DEL) */ DELETE FROM "SCOTT"."MV_PARTTIME" "SNA
quot; WHERE "SNA
quot;."CNT_ALL"=0 END OF STMT
見てみると、マテビューに怪しげなDELETE文が実行されていました。
“CNT_ALL”=0。。。
なるほど！
COUNT(*)は、COUNT(*)の結果が0の項目をマテビューから削除する為に必要って
ことか。。。
つまり。
集計されたマテビューは、前回の検証で見てきたようにMLOGの差分で計算して
います。その為、集計項目全てが削除された場合、マテビューから削除する必
要があります。
例を使って説明すると、
JOB SUM_SAL CNT_SAL CNT_ALL --------- ---------- ---------- ---------- PART-TIME 230 1 1
現在のマテビューは上記のように、先ほどINSERTした1件のみがあります。
ここでDELETE文を実行した場合、MLOGは以下のようになります。
JOB SAL M_ROW$ SNAPTIME D O CHANGE_VECTOR$ --------- ---------- ------------------ -------- - - --------------- PART-TIME 230 AAAM1FAAEAAAAGgAAC 00-01-01 D O 0000
この後に高速リフレッシュした場合、前回の検証の通り、マテビューのSALの
値とMLOGの値の差分を計算し、SUM_SALの値を0に更新します。
つまり、差分の計算だけだと、以下のようなデータになってしまいます。
JOB SUM_SAL CNT_SAL CNT_ALL --------- ---------- ---------- ---------- PART-TIME 0 0 0
このままでは、元表とは異なったものになってしまいます。
そこで、先ほど実行されていたDELETE文（COUNT(*) = 0）で、上記の行を削除
している、という訳。
ちなみに、UPDATE文は？とお思いの方。
UPDATE文の場合も残り続けてしまうことがあります。
先ほどの例で説明すると、JOBをPART-TIMEから別のものに変更した場合に、
元表ではPART-TIMEのデータはなくなり、DELETEと同じことになります。
※わからない方は、実際に試してみましょう。
冒頭にあったINSERTのみ保証されるという不思議な制限も、このことを理解す
れば納得することができるでしょう。
ところで、先ほど作成したマテビューですが、count(*)といっしょに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(sal)という項目が必要なのでしょうか。
続きはまた次回っ！
湘南新宿ライン上り電車、新宿下車。。。 恵比寿より。