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

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

全８回に渡ってリフレッシュ、クエリーリライトの機能について見てきました。
今回は、今まで検証してきた機能の総まとめをしちゃいます。ここを見れば今
まで行ってきた検証が一目瞭然！という訳ではないですが、検証で利用した機
能を最後におさらいしちゃいましょう。

■■■■■今回のあらすじ■■■■■
１）マテビュー作成時に必要な設定
２）リフレッシュ　フラッシュバック！
３）クエリーリライト　フラッシュバック！
４）DBMS_ADVISOR.TUNE_MVIEW

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

１）マテビュー作成時に必要な設定
１．grant create any materialized view to ユーザー名;

２）リフレッシュ　フラッシュバック！
リフレッシュには完全リフレッシュと高速リフレッシュがある。

□完全リフレッシュ
DELETE文で削除した後に再度データを集計してマテビューにInsert。

１．CREATE MATERIALIZED VIEW文にREFRESH COMPLETE句。
２．DBMS_MVIEW.REFRESH( [マテビュー], ‘c’ );
でリフレッシュを実行。

※DELETE文の削除をTRUNCATE文で削除するようにする方法。
後ろから２つ目の値をFALSEにすることで、マテビューをTRUNCATE文で削除。

DBMS_MVIEW.REFRESH([マテビュー], ‘c’, NULL, TRUE, FALSE, 1, 0, 0,
FALSE, FALSE);

□高速リフレッシュ
元表に変更があった箇所のみをマテビューに反映。（MLOGの作成が必須。）
１．CREATE MATERIALIZED VIEW文にREFRESH FAST句。
２．DBMS_MVIEW.REFRESH( [リフレッシュするマテビュー], ‘f’ );
でリフレッシュを実行。

□FORCEオプション
高速リフレッシュを試みて、できない時に完全リフレッシュが実行される。
１．CREATE MATERIALIZED VIEW文にREFRESH FORCE句。
（FORCEはREFRESH句を指定しない場合のデフォルト）
２．DBMS_MVIEW.REFRESH( [リフレッシュするマテビュー], ‘?’ );
でリフレッシュを実行。

□高速リフレッシュを可能にする時の注意点。
１．MLOGを作成する必要あり。MLOG作成時の注意点。
with rowid句
sequence句（集計を含むマテビューの場合）
including new values句（集計を含むマテビューの場合）
２．マテビュー作成時にselect文に必要な項目を追加。
例）
集計されたマテビューの場合SELECT句にCOUNT(*),COUNT(expr)を指定。
複数のテーブルを結合したマテビューの場合：SELECT句にROWIDを指定。
など。。。

□高速リフレッシュ可能かどうか確認。
１．DBMS_MVIEW.EXPLAIN_MVIEWで確認。
○実行前にテーブル作成（mv_capabilities_tableテーブル）
SQL> @?/rdbms/admin/utlxmv.sql

○実行
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW( マテビュー名 );

○確認
SQL> select capability_name, possible, related_text, msgtxt
from mv_capabilities_table where capability_name Like ‘%REFRESH%’;

２．DBMS_MVIEW.TUNE_MVIEWで確認。（後で説明）

３）クエリーリライト　フラッシュバック！
□クエリーリライトに必要な設定。
１．CREATE MATERIALIZED VIEW文にENABLE QUERY REWRITE句を指定。

２．初期化パラメータQUERY_REWRITE_ENABLEDを確認しTRUEに設定。
（FORCEにすると常にリライト）

３．初期化パラメータQUERY_REWRITE_INTEGRITYを確認。
※クエリーリライトされるレベルを設定。
enforced :マテビューと問合せSQL文の整合性が保障
trusted :ディメンションを利用してクエリーリライトする場合
stale_tolerated :整合性がとれてなくてもリライトされる

□クエリーリライトされたかどうか確認。
１．DBMS_MVIEW.EXPLAIN_REWRITEで確認。
○実行前にテーブル作成（rewrite_tableテーブル）
SQL> @?/rdbms/admin/utlxrw.sql

○実行
SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE(SQL文、又はマテビュー名);

○確認
SQL> select message, rewritten_txt from rewrite_table order by sequence;

４）DBMS_ADVISOR.TUNE_MVIEW
最後に、DBMS_ADVISOR.TUNE_MVIEWというパッケージを使用してみましょう。
このパッケージを使用することで、高速リフレッシュの制限にひっかかるよう
なマテビューを事前に確認することができます。

例えば、以下のようなSQL文を実行して集計するマテビューを作成する場合、

select job, sum(sal) from emp group by job;

以下を実行することで高速リフレッシュを意識したマテビューの構文を作成し
てくれます。

SQL> CREATE DIRECTORY TUNE_RESULTS AS  'c:';

SQL> GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;

SQL> VARIABLE emp_var VARCHAR2(30);

SQL> EXEC :emp_var := 'emp_mv';

SQL> EXEC DBMS_ADVISOR.TUNE_MVIEW(:emp_var, 'CREATE MATERIALIZED VIEW
     emp_mv AS select job, sum(sal) from emp group by job');
SQL> EXEC DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:emp_var),
     'TUNE_RESULTS', 'mv_create.sql');

ディレクトリを定義上記でc:mv_create.sqlというファイルが作成されます。
作成されたファイルの中身を確認すると。。。

CREATE MATERIALIZED VIEW LOG ON
    "SCOTT"."EMP"
    WITH ROWID, SEQUENCE("JOB","SAL")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SCOTT"."EMP"
    ADD ROWID, SEQUENCE("JOB","SAL")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SCOTT.EMP_MV
    REFRESH FAST WITH ROWID
    DISABLE QUERY REWRITE
    AS SELECT SCOTT.EMP.JOB C1, SUM("SCOTT"."EMP"."SAL") M1,
    COUNT("SCOTT"."EMP"."SAL")
       M2, COUNT(*) M3 FROM SCOTT.EMP GROUP BY SCOTT.EMP.JOB;

というように、MLOGとマテビュー作成のスクリプトが作成されています。
MLOGをみると、with rowid句とsequence句、including new values句が指定さ
れています。またマテビューでは、REFRESH FAST 句とSELECT文にはCOUNT(*),
COUNT(SAL)句が指定されていて、高速リフレッシュで必要な項目が全て網羅さ
れています。

高速リフレッシュを利用する場合、このツールを利用してMLOGとマテビューを
作成した方がよいでしょう。

以上でマテビュー検証は終了です。
パーティションなど検証できなかった項目については、また別の機会に再検証
できればと思います。

以上。

エープリールフールにあまりウソをつきませんでした。　恵比寿にて

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

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