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

投稿日: 2006年3月15日

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

今回からクエリーリライトの機能を検証していきます。
クエリーリライトは、SQLを切り替えてくれる便利な機能であり、高速リフレッ
シュと並んで、マテビューの特徴的な機能です。この２本柱を理解すれば、マ
テビューを使ってみよう、と思うのでは！？

■■■■■今回のあらすじ■■■■■
１）DBMS_MVIEW.EXPLAIN_REWRITE
２）クエリーリライトの条件

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

１）DBMS_MVIEW.EXPLAIN_REWRITE
クエリーリライトとは、ユーザーやアプリケーションが発行するSQL文を自動
的に書き換える機能ですが、自動的なので何をどう書きかえたのかよくわかり
ません。
実行したSQL文がどうリライトされるのか、また、何故リライトされなかったか
などを、DBMS_MVIEW.EXPLAIN_REWRITEというパッケージを利用して確認するこ
とができます。

では、実際に実行してみましょう。まずは、環境作成から。

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

SQL> create materialized view mv_rewrite enable query rewrite
     as select job, sum(sal) sum_sal from emp group by job;

utlxrw.sqlでDBMS_MVIEW.EXPLAIN_REWRITEを実行した結果を格納するテーブル
（REWRITE_TABLE）が作成されます。
また、作成するマテビューには、enable query rewrite句を指定する必要があ
ります。

環境が作成されたところで、パッケージを実行してrewrite_tableを見てみま
しょう。MESSAGEという項目でリライトに関する説明を確認できます。

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('select job, sum(sal) sum_sal
     from emp group by job');

SQL> select message from rewrite_table order by sequence;

MESSAGE
-----------------------------------------------------------------------
QSM-01151: 問合せはリライトされました
QSM-01209: テキスト一致アルゴリズムを使用して、マテリアライズド・ビュー
           MV_REWRITEで問合せがリライトされました

一瞬エラーメッセージのように見えますが、マテビューMV_REWRITEにリライト
されていることが確認できます。

ちなみに、リライトされなかった場合は以下のようになります。

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('select deptno, sum(sal) sum_sal
     from emp group by deptno', statement_id => 'REWRITE_TEST');

SQL> select message from rewrite_table where statement_id='REWRITE_TEST'
     order by sequence;

MESSAGE
----------------------------------------------------------------------
QSM-01150: 問合せをリライトしませんでした
QSM-01082: マテリアライズド・ビューMV_REWRITEと表EMPの結合は不可能です。
QSM-01102: マテリアライズド・ビューMV_REWRITEには表EMP(列DEPTNO)への後
           戻り結合が必要です

パッケージ実行時にstatement_idを指定して、その実行結果のみを抽出してい
ます。

メッセージをみると、後戻り結合、というよくわからない言葉がありますが、
要はMV_REWRITEを使ってリライトをしようとしてダメだった、ということのよ
うです。

このように、DBMS_MVIEW.EXPLAIN_REWRITEを使用することで、実行したSQL文
がどのようにリライトされたのか、また、なぜリライトに失敗したのたのかを
確認することができます。

では、このパッケージを利用して、クエリーリライトがどのようにクエリーを
リライトしているか、について見てみましょう。

２）クエリーリライト
クエリーリライトとは読んで字のごとくクエリーをリライトする機能です。
先ほどの例のように、実行したSQL文とマテビューが同じであれば、当然リラ
イトされます。（SQL文とマテビューのコストを比較してマテビューのコスト
が低ければですが。）

では、クエリーリライトは実行したSQL文とマテビューが全く同じ場合だけに
実行されるのでしょうか？

実はクエリーリライトは、実行したSQL文とマテビューがちょっと違う場合に
も柔軟に、少し懐かしい言葉で言うと「ファジー」にクエリーをリライトして
くれます。

例えば、deptnoを追加した以下のようなマテビューを作成し、

■create materialized view mv_rewrite enable query rewrite as SELECT job,
deptno, sum(sal) sum_sal FROM emp GROUP BY job, deptno;

以下のように問合せをした場合、

■SELECT job, sum(sal) sum_sal FROM emp GROUP BY job;

作成したMV_REWRITEを利用してリライトされます。DBMS_MVIEW.EXPLAIN_REWRITE
で確認してみましょう。

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT job, sum(sal) FROM emp
     GROUP BY job', statement_id => 'REWRITE_TEST2' );

SQL> select message from rewrite_table
     where statement_id = 'REWRITE_TEST2' order by sequence;

MESSAGE
---------------------------------------------------------------------
QSM-01151: 問合せはリライトされました
QSM-01033: マテリアライズド・ビューMV_REWRITEで問合せがリライトされま
           した。

リライトされました！ってこれだけじゃよくわからないですね。。。
REWRITTEN_TXTという項目を見れば、どのようなSQL文にリライトされたかを確
認することができます。

SQL> select distinct rewritten_txt from rewrite_table
     where statement_id = 'REWRITE_TEST2';

REWRITTEN_TXT
----------------------------------------------------------------------
SELECT MV_REWRITE.JOB JOB,SUM(MV_REWRITE.SUM_SAL) SUM(SAL) FROM SCOTT.
MV_REWRITE MV_REWRITE GROUP BY MV_REWRITE.JOB

マテビューで集計されたSUM_SALをさらにSUMで集計したものにリライトしてい
ます。

と、このように、問合せしたSQL文がマテビューを利用することができれば、
「ファジー」にリライトしてくれます。

また、以下のようなSQL文も同様にリライトされます。

■SELECT job, deptno, sum(sal) sum_sal FROM emp where deptno = 10
  GROUP BY job, deptno;

■SELECT job, deptno, sum(sal) sum_sal FROM emp where deptno In(10, 20)
  GROUP BY job, deptno;

■SELECT job, deptno, sum(sal) sum_sal FROM emp HAVING sum(sal) > 100
  GROUP BY job, deptno;

ここまでなら十分想定可能だと思います。

では、以下のような場合はどうでしょうか！？

■マテビュー（dept表を結合してdeptnoを表示）
create materialized view mv_rewrite enable query rewrite as
SELECT job, d.deptno, sum(sal) sum_sal FROM emp e, dept d
where e.deptno = d.deptno GROUP BY job, d.deptno;

■問合せ（dept表のdnameを表示）
SELECT job, d.deptno, d.dname, sum(sal) sum_sal FROM emp e, dept d
where e.deptno = d.deptno GROUP BY job, d.deptno, d.dname;

問合せには、マテビューにはないdnameが追加されています。
このSQL文はリライトされるのでしょうか？

続きはまた来週！

チョコで指輪のわらしべ長者、斬り！　恵比寿にて。