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

投稿日: 2006年3月22日

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

前回に引き続きクエリーリライトについて見ていきます。
前回は実行された SQL文とマテビューとが異なる時でも、クエリーリライトが
実行されることを確認しました。
今回はリライトのさらに「ファジー」な部分を見てみましょう。

■■■■■今回のあらすじ■■■■■
１）クエリーリライト：例２
２）クエリーリライト：例３

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

１）クエリーリライト：例２
まずは、前回の宿題、マテビューにない列（dname）を追加した場合どうなる
か、について見てみましょう。
前回の環境を確認。

■マテビュー（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;

では、DBMS_MVIEW.EXPLAIN_REWRITEを実行！

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('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', statement_id => 'REWRITE_TEST3' );

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

MESSAGE
---------------------------------------------------------------------
QSM-01151: 問合せはリライトされました
QSM-01033: マテリアライズド・ビューMV_REWRITEで問合せがリライトされま
           した。
QSM-01102: マテリアライズド・ビューMV_REWRITEには表DEPT(列DNAME)への後
           戻り結合が必要です

ちゃんとリライトできたのでしょうか？１、２行目のメッセージを見る限りで
はリライトされているように見えますが。。。

とりあえず、マニュアルでQSM-01102のメッセージを確認。

原因: 問合せに含まれる列がマテリアライズド・ビューにありません。
クエリー・リライトは、列を含むマテリアライズド・ビューを後戻り結合し
て、実表からこの列を取得します。

dname列がマテビューにはないので「後戻り結合」ということをしてdname列を
取得しているようです。今回はdname列を取得できたのでリライトされたので
しょう。
では「後戻り結合」とは何でしょうか？？リライトされたSQLを見てみましょう。

SQL> select rewritten_txt from rewrite_table
     where statement_id = 'REWRITE_TEST3' and sequence = 1;

REWRITTEN_TXT
---------------------------------------------------------------------
SELECT MV_REWRITE.JOB JOB,D.DEPTNO DEPTNO,D.DNAME DNAME,MV_REWRITE.SU
M_SAL SUM_SAL FROM SCOTT.MV_REWRITE MV_REWRITE,DEPT D WHERE D.DEPTNO=
MV_REWRITE.DEPTNO

マテビューとDEPT表を結合して、DNAMEを取得しています。
DEPT表に再度結合して列を取得することを「後戻り結合」と表現しているよう
です。

今回、クエリーリライトは主キーをもとに実表からその他の列を取得していま
す。主キーをもとにということなので、極端な例でいうと、主キーのみのマテ
ビューを作成した場合、クエリーリライトによって、そのテーブル全ての列を
取得することができる、ということになります。

SQL> create materialized view mv_emp enable query rewrite as
     select empno from emp;

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT * FROM emp',
     statement_id => 'REWRITE_TEST4' );

SQL> select rewritten_txt from rewrite_table
     where statement_id = 'REWRITE_TEST4' and sequence = 1;

REWRITTEN_TXT
---------------------------------------------------------------------
SELECT EMP.EMPNO EMPNO,EMP.ENAME ENAME,EMP.JOB JOB,EMP.MGR MGR,EMP.HI
REDATE HIREDATE,EMP.SAL SAL,EMP.COMM COMM,EMP.DEPTNO DEPTNO FROM SCOT
T.MV_EMP MV_EMP,EMP EMP WHERE EMP.EMPNO=MV_EMP.EMPNO

このように、マテビューとEMPテーブルを結合して、その他の列を取得してい
ます。使い方次第では非常に有効な機能といえます。

２）クエリーリライト：例３
もう一つクエリーリライトされる例を見てみましょう。今度は条件句に注目！

SQL> create materialized view mv_emp_1 enable query rewrite as  SELECT job,
     sum(sal) FROM emp WHERE sal BETWEEN 1 And 99 GROUP BY job;

SQL> create materialized view mv_emp_100 enable query rewrite as SELECT job,
     sum(sal) FROM emp WHERE sal BETWEEN 100 And 199 GROUP BY job;

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT job, sum(sal) FROM emp
     WHERE sal BETWEEN 1 And 299 GROUP BY job', statement_id => 'REWRITE_TEST5' );

SQL> SELECT message FROM rewrite_table WHERE statement_id = 'REWRITE_TEST5';

MESSAGE
---------------------------------------------------------------------
QSM-01150: 問合せをリライトしませんでした
QSM-01091: コスト・ベース・オプティマイザが、クエリー・リライトはより
           コストが高いと判定しました。

3つのマテビューを作成して、全ての条件に合うような問合せを実行したとき
にどうなるかを見てみましたが、今回はリライトされませんでした。
メッセージでは、コストが高い為にリライトされなかった、と説明されていま
す。ってことは、コストが低ければリライトされる、ということ？？？
コストを確認。

SQL> select original_cost, rewritten_cost from rewrite_table
     where statement_id = 'REWRITE_TEST5' and sequence = 1;

ORIGINAL_COST REWRITTEN_COST
------------- --------------
            4             10

やはり、リライトのコストの方が高いようです。ということは、このREWRITT
EN_COSTがORIGINAL_COSTを下回ればリライトされるはず。
現在のEMP表はデータ量が少ないので、リライトする効果はあまり期待できま
せん。データを入れて再度試してみましょう。

SQL> create table emp_bk as select * from emp;

SQL> truncate table emp;

SQL> insert into emp select rownum, e1.ename, e1.job, e1.mgr, e1.hiredate,
     e1.sal, e1.comm, e1.deptno from emp_bk e1, emp_bk e2, emp_bk e3,
     emp_bk e4 where rownum <= 9999;

SQL> commit;

SQL> exec dbms_mview.refresh('mv_emp_1,mv_emp_100, 'C');

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT job, sum(sal) FROM emp
     WHERE sal BETWEEN 1 And 200 GROUP BY job',
     statement_id => 'REWRITE_TEST6' );

emp_bkという一時テーブルを作成して、そのテーブルを結合無しで複数指定す
ることで大量の行を作成し強引に9999行のデータを挿入しています。
では、コストを確認。

SQL> select original_cost, rewritten_cost from rewrite_table
     where statement_id = 'REWRITE_TEST6' and sequence = 1;

ORIGINAL_COST REWRITTEN_COST
------------- --------------
           18             20

少し縮まりましたが、まだ足りないようです。EMP表は9999行までしかいれら
れないので、表を変更して、再度チャレンジ！

SQL> alter table emp modify empno number(5);

SQL> truncate table emp;

SQL> insert into emp select rownum, e1.ename, e1.job, e1.mgr, e1.hiredate,
     e1.sal, e1.comm, e1.deptno from emp_bk e1, emp_bk e2, emp_bk e3,
     emp_bk e4, emp_bk e5 where rownum <= 99999;

SQL> commit;

SQL> exec dbms_mview.refresh('mv_emp_1,mv_emp_100, 'C');

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT job, sum(sal) FROM emp
     WHERE sal BETWEEN 1 And 200 GROUP BY job',
     statement_id => 'REWRITE_TEST7' );

SQL> select original_cost, rewritten_cost from rewrite_table
     where statement_id = 'REWRITE_TEST7' and sequence = 1;

ORIGINAL_COST REWRITTEN_COST
------------- --------------
          144            135

99999行挿入で見事！？REWRITTEN_COSTがORIGINAL_COSTを下回りました。
では、気になるSQL文を確認！

といきたいところですが、今回はここまで。続きはまた来週！

今年こそ、桜見ながら花見がしたい。　恵比寿にて。