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

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

前回、前々回と、MLOGについて検証しました。
今回は、高速リフレッシュについてです。この機能を使ったことがある人で
あれば、必ず疑問に思うであろう制限のしくみについて見てみましょう。

■■■■■今回のあらすじ■■■■■
1)高速リフレッシュ制限について
2)制限:COUNT(*)が、SELECT句に必要。

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

1)高速リフレッシュの制限について
高速リフレッシュを使用されている方ならご存知と思いますが、この機能を
利用するにはいくつかの制限があります。
中でも、通常の集計SQL文では間違いなく無いと思われる制限が以下ではない
でしょうか。

COUNT(*)が、SELECT句に必要。
※ 集計関数が使用されているマテビューの場合

マテビューの機能を使ったことがない、又は高速リフレッシュを使ってない
人にしてみれば、「なんで必要なの?」と思うのでは???
高速リフレッシュの機能を使用してても、この制限の意味はわからずに、お
まじないのようにSELECT句に追加している人も多いのではないでしょうか。

ということで、ここからはこの疑問を解消すべく、検証を行って行きたいと
思います。

2)制限: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$"
WHERE "SNA$"."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)という項目が必要なのでしょうか。

続きはまた次回っ!

湘南新宿ライン上り電車、新宿下車。。。 恵比寿より。