マテリアライズドビュー検証 まて マテ マテビュー その７
＜マテリアライズドビュー検証 まて マテ マテビュー その７＞
ペンネーム：クリープ
前回に引き続きクエリーリライトについて見ていきます。
前回は実行された 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文を確認！
といきたいところですが、今回はここまで。続きはまた来週！
今年こそ、桜見ながら花見がしたい。 恵比寿にて。