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

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

今回も引き続きクエリーリライトについて見て行きましょう。
最初に前回のクエリーリライト結果を確認して、その後にディメンションを利
用したクエリーリライトを見ていきます。

※前回の検証で作成したmv_emp_200というマテビューを作成してましたが、今
回の検証では必要のないマテビューでした。
その為、今回の検証ではこのマテビューは割愛しております。

■■■■■今回のあらすじ■■■■■
１）クエリーリライト：例３－２
２）クエリーリライト：例４（ディメンション）

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

１）クエリーリライト：例３－２
早速、前回の確認から始めましょう！
前回作成したマテビューと実行した問合せは以下の通り。

□マテビュー
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> SELECT job, sum(sal) FROM emp WHERE sal BETWEEN 1 And 200 GROUP BY job;

前回の検証では、実行した問合せのコストよりリライトしたコストの方が少な
い所まで確認しました。一体どのようなリライトが行われていたのでしょうか？
早速リライトされたSQL文を確認！

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

REWRITTEN_TXT
---------------------------------------------------------------------
SELECT JOB JOB,SUM(VW_COL_1) SUM(SAL) FROM  ( (SELECT /*+ */ MV_EMP_1
00.SUM(SAL),MV_EMP_100.JOB JOB FROM SCOTT.MV_EMP_100 MV_EMP_100)
 UNION ALL
(SELECT /*+ */MV_EMP_1.SUM(SAL),MV_EMP_1.JOB JOB FROM SCOTT.MV_EMP_1
 MV_EMP_1)
 UNION ALL
(SELECT /*+ */ SUM(SAL),JOB JOB FROM EMP EMP WHERE SAL>99 AND SAL<100
 OR SAL>199 AND SAL<=200 GROUP BY JOB)) $kkqs_mmv_ivview GROUP BY JOB

なんと！マテビューをUNION ALLで結合して取得しています。
しかも、マテビューだけでは取得できない値を元表であるEMPテーブルを集計
して取得しています。以下参照。

SALの値が、
・0以上99以下の値				：mv_emp1
・99より大きく100より小さい値	：emp
・100以上199以下の値			：mv_emp100
・199より大きく200以下の値		：emp

もちろん、リライトされることでレスポンスが必ず向上する、とは言えません
が、マテビューの方が取得するデータ量が少なくなる可能性が高い、というこ
とは言えるでしょう。
それにしても、すごいリライト文です。。。

２）クエリーリライト：例４（ディメンション）
最後にディメンションを利用したクエリーリライトについて見てみましょう。

ディメンションとは、項目の親子関係を定義することができるオブジェクトで
す。ディメンションで親子関係が定義されていると、この親子関係を利用して
クエリーリライトすることが可能になります。
では、ディメンションを利用したクエリーリライトとはどのようなものなので
しょうか。実際に確認してみましょう。

まずは環境作成。ディメンションを利用する場合は初期化パラメータQUERY_RE
WRITE_INTEGRITYをTRUSTEDまたはSTALE_TOLERATEDに変更する必要があります。

SQL> ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY='TRUSTED';

今回はテーブルも作成しましょう。

SQL> CREATE TABLE category
     ( job VARCHAR2(9) CONSTRAINT job_pkey PRIMARY KEY,
       job_category VARCHAR2(20) CONSTRAINT job_category_nn NOT NULL,
       job_sub_category VARCHAR2(20) CONSTRAINT job_sub_category_nn NOT NULL );

JOBをカテゴリー分けするテーブルを作成してみました。このテーブルは、

job_category(親) >> job_sub_category(子) >> job(孫)

という関係を意図して作成しております。
では、この関係をもとにディメンションを作成。

SQL> CREATE DIMENSION job_dim
     LEVEL mago      IS (category.job)
     LEVEL ko        IS (category.job_sub_category)
     LEVEL oya       IS (category.job_category)
     HIERARCHY job_rollup ( mago CHILD OF ko CHILD OF oya);

LEVEL句で階層を定義し、HIERARCHY句で階層関係（親子関係）を定義していま
す。これでただの単体の項目から項目同士が親子関係を結んだ、ということに
なります。

環境が整ったところで、ディメンションを利用したクエリーリライトを見てみ
ましょう。
作成するマテビューは、EMPテーブルとCATEGORYテーブルをJOBで結合して、
JOB_SUB_CATEGORYで集計してみます。
実行する問合せはJOB_CATEGORYで集計します。

SQL> CREATE MATERIALIZED VIEW mv_job ENABLE QUERY REWRITE AS
     SELECT j.job_category, j.job_sub_category, SUM(e.sal) AS sum_sal
     FROM emp e, category j WHERE e.job = j.job
     GROUP BY j.job_category, j.job_sub_category;

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT j.job_category, SUM(e.sal)
     AS sum_sal FROM emp e, category j WHERE e.job = j.job
     GROUP BY j.job_category', statement_id => 'REWRITE_TEST8' );

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

REWRITTEN_TXT
---------------------------------------------------------------------
SELECT MV_JOB.JOB_CATEGORY JOB_CATEGORY,SUM(MV_JOB.SUM_SAL) SUM_SAL
FROM SCOTT.MV_JOB MV_JOB GROUP BY MV_JOB.JOB_CATEGORY

正常にリライトされました。。。

ん？？？
一体どこでディメンションが利用されているのでしょうか！？？？

一見ディメンションを参照しているように見えますが、実はこのクエリーリラ
イトはディメンション情報を参照していません。前に検証したクエリーリライ
トと同じリライトです。（まてマテマテビュー　その６参照）

では、ディメンションを利用したクエリーリライトを（今度は本当に）見てみ
ましょう。

SQL> DROP MATERIALIZED VIEW mv_job;

SQL> CREATE MATERIALIZED VIEW mv_job ENABLE QUERY REWRITE AS
     SELECT j.job_sub_category, SUM(e.sal) AS sum_sal
     FROM emp e, category j WHERE e.job = j.job
     GROUP BY j.job_sub_category;

SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('SELECT j.job_category, SUM(e.sal)
     AS sum_sal FROM emp e, category j  WHERE e.job = j.job
     GROUP BY j.job_category', statement_id => 'REWRITE_TEST9' );

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

REWRITTEN_TXT
---------------------------------------------------------------------
SELECT from$_subquery$_005.JOB_CATEGORY JOB_CATEGORY,SUM(MV_JOB.SUM_S
AL) SUM_SAL FROM SCOTT.MV_JOB MV_JOB, (SELECT DISTINCT JOB_SUB_CATEGO
RY JOB_SUB_CATEGORY,JOB_CATEGORY JOB_CATEGORY FROM CATEGORY CATEGORY)
from$_subquery$_005 WHERE from$_subquery$_005.JOB_SUB_CATEGORY=MV_JO
B.JOB_SUB_CATEGORY GROUP BY from$_subquery$_005.JOB_CATEGORY

リライトされたSQL文が非常に複雑になっています。。。
SQL文をみるとマテビューmv_jobにリライトされているようです。

では、解説。

まず、作成したマテビューは、JOB_SUB_CATEGORYのみを集計するように変更し
ております。一方、実行した問合せの方はJOB_CATEGORYで集計しています。
以下参照。

□マテビュー
SELECT job_sub_category, SUM(sal) FROM …

□問合せ
SELECT job_category, SUM(sal) FROM …

ディメンションが定義されていない場合、クエリーリライトではJOB_CATEGORY
の項目がマテビュー内に存在しない為、リライトすることができません。
しかし、ディメンションが定義されていると、JOB_SUB_CATEGORYの親がJOB_CA
TEGORYとして認識できるため、親の項目がマテビューになくてもリライトする
ことができます。
リライトされたSQL文を単純にすると以下のようになります。

   SELECT JC.JOB_CATEGORY,SUM(MV.SUM_SAL)
     FROM MV_JOB MV,
          (SELECT DISTINCT JOB_SUB_CATEGORY, JOB_CATEGORY
                      FROM CATEGORY ) JC
    WHERE JC.JOB_SUB_CATEGORY=MV.JOB_SUB_CATEGORY
GROUP BY JC.JOB_CATEGORY

CATEGORYテーブルから親であるJOB_CATEGORYを引っ張り出して集計しています。
このように、ディメンションを利用することでマテビューに定義されていない
項目でもクエリーリライトすることができるようになります。
クエリーリライト、恐るべし。。。

以上、いくつかの例をもとにクエリーリライトを見てきました。
マテビューを使用していない方にはあまり馴染みのない機能だと思いますが、
Oracleが裏でいろいろやっているんだなぁ、ということは理解して頂けたこと
と思います。

次回は、マテビュー検証の総まとめをする予定です。

定義しないと築けない親子関係、何故か切なくなりました。　恵比寿にて

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

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