津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第71回 マテリアライズド・ビューについて(2)

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年の冬は寒暖差が大きく身体に大変でしたが、やっと過ごしやすい気候になってきましたので、この原稿が公開される頃には桜も咲いていますね。
今回は、マテリアライズド・ビュー(MView)の続きとして、ビュー(インライン・ビューも含む)を使用したクエリー・リライトとリフレッシュについて説明しますので、参考にしてください。

1. ビューを使用したクエリー・リライト
まずは、ビューやインライン・ビューを使用したクエリー・リライトについて説明します。
前回の説明では、MViewがOracle固有の結合文になっていれば、問合せについてはANSI準拠の結合文でもクエリー・リライトは問題ないと説明しましたが、ビューやインライン・ビューが含むと異なってくるので、それについてまとめてみました。また、結合文の表現が分かり辛いと思い、Oracle固有の結合文をWHERE句の結合条件、ANSI準拠の結合文をFROM句の結合条件に変えました。

(1)ビューが含む問合せ
ビューは、MViewと問合せで同じビュー名にする必要があり、異なるとテキスト一致レベルのクエリー・リライトも動作しません。また、テキスト一致レベル以外のクエリー・リライトを行うには、MViewがWHERE句の結合条件にする必要があるので、ビューも同じ結合条件にする必要があります。分かりやすいように、以下のような同じ問合せに対する2つのビュー(sales_prod_v、sales_prod_v2)とMViewを使用して説明します。

CREATE VIEW sales_prod_v AS
SELECT s.prod_id, s.time_id, amount_sold FROM sales s, products p WHERE s.prod_id = p.prod_id;

CREATE MATERIALIZED VIEW sum_sales_mv ENABLE QUERY REWRITE AS
SELECT prod_id, time_id, SUM(amount_sold) AS sum_amount_sold 
FROM sales_prod_v GROUP BY prod_id, time_id;

CREATE VIEW sales_prod_v2 AS
SELECT s.prod_id, s.time_id, amount_sold FROM sales s, products p WHERE s.prod_id = p.prod_id;

問合せのビューが、以下のようにMViewと同じ(sales_prod_v)だと、テキスト一致レベル以外でもリライトされます(この例は、集計ロールアップと問合せデルタ結合が行われています)。

SQL> SELECT t.week_ending_day, SUM(v.amount_sold) AS sum_amount_sold
  2    FROM sales_prod_v v, times t WHERE v.time_id = t.time_id GROUP BY t.week_ending_day;

-------------------------------------------------------
| Id  | Operation                      | Name         |
-------------------------------------------------------
|   0 | SELECT STATEMENT               |              |
|   1 |  HASH GROUP BY                 |              |
|*  2 |   HASH JOIN                    |              |
|   3 |    TABLE ACCESS FULL           | TIMES        |
|   4 |    MAT_VIEW REWRITE ACCESS FULL| SUM_SALES_MV |

ただし、以下の左側のようなMViewと異なるビューの問合せではリライトしません(テキスト一致レベルも動作しません)。また、右側のように問合せをFROM句の結合条件にしたときもリライトしません。

SQL> SELECT t.week_ending_day,
  2         SUM(v.amount_sold) AS sum_amount_sold
  3    FROM sales_prod_v2 v, times t
  4   WHERE v.time_id = t.time_id
  5   GROUP BY t.week_ending_day;

-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  HASH GROUP BY      |       |
|*  2 |   HASH JOIN         |       |
|   3 |    TABLE ACCESS FULL| TIMES |
|   4 |    TABLE ACCESS FULL| SALES |
SQL> SELECT t.week_ending_day,
  2         SUM(v.amount_sold) AS sum_amount_sold
  3    FROM sales_prod_v v INNER JOIN times t
  4      ON (v.time_id = t.time_id)
  5   GROUP BY t.week_ending_day;

-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|   1 |  HASH GROUP BY      |       |
|*  2 |   HASH JOIN         |       |
|   3 |    TABLE ACCESS FULL| TIMES |
|   4 |    TABLE ACCESS FULL| SALES |

(2)インライン・ビューが含む問合せ
インライン・ビューは、Oracle Database 11gからテキスト完全一致でないときもクエリー・リライトするようになり、使いやすさが向上しています。ただし、インライン・ビュー内は、問合せとMViewの結合文を合わせるなど多少の注意が必要になります。これも以下のようなインライン・ビューが含むMViewを使用して説明します。

CREATE MATERIALIZED VIEW sum_sales_mv ENABLE QUERY REWRITE AS
SELECT iv.prod_id, iv.time_id, SUM(iv.amount_sold) AS sum_amount_sold
FROM (SELECT s.prod_id, s.time_id, amount_sold FROM sales s, products p WHERE s.prod_id = p.prod_id) iv
GROUP BY iv.prod_id, iv.time_id;

以下の問合せは、すべてのPROD_IDが含んでいるMViewに対して、100未満を指定したものですが、問題なくクエリー・リライトされています(以下のインライン・ビューは、MViewと結合条件が逆になっているので、テキスト一致ではありませんが、このようなレベルでは問題ありません)。このような使い方だと、前回説明した一般的なクエリー・リライトがすべて動作します。

SQL> SELECT iv.prod_id, iv.time_id, SUM(iv.amount_sold) AS sum_amount_sold FROM
2      (SELECT s.prod_id,s.time_id,amount_sold FROM sales s, products p WHERE p.prod_id=s.prod_id) iv
 3   WHERE iv.prod_id < 100 GROUP BY iv.prod_id, iv.time_id;

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| SUM_SALES_MV |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUM_SALES_MV"."PROD_ID"<100)

ただし、ビューと同じようにクエリー・リライトさせるには、インライン・ビュー内もWHERE句の結合条件にする必要があります。このとき、以下の左側のような問合せのインライン・ビュー内だけFROM句の結合条件にするや、右側のように問合せだけビューにする使い方ではリライトしないので注意してください(MViewだけビューのときもリライトしません)。

SELECT prod_id, time_id, SUM(amount_sold)
  FROM (SELECT s.prod_id, s.time_id, amount_sold
          FROM sales s INNER JOIN products p
            ON (s.prod_id = p.prod_id)) iv
 WHERE prod_id < 100 GROUP BY prod_id, time_id;
SELECT prod_id, time_id, SUM(amount_sold)
  FROM sales_prod_v v
 WHERE prod_id < 100
 GROUP BY prod_id, time_id;

このように、ビューやインライン・ビューを含んでいる問合せは、問合せとMViewのどちらもWHERE句の結合条件にしないと、テキスト一致レベル以外のクエリー・リライトは動作しません。ただし、内部結合は、WHERE句の結合条件を使用するのが一般的で問題ないと思いますが、外部結合はFROM句の結合条件(ANSI準拠の結合文)を使用するのが多いと思うので、外部結合を使用するMViewでは注意が必要です。

Oracle固有の外部結合に対するフィルターについて
ご存知ない方のために、ここでOracle固有の外部結合に対するフィルターについて簡単に説明します。
外部結合を行うときに、核でない表にフィルターを行う場合があります。ANSI準拠のときには、以下の左側のように行いますが、Oracle固有のときには、インライン・ビューを使用する以外に、以下の右側のように行うことができます。MViewでクエリー・リライトさせるために、Oracle固有の外部結合とインライン・ビューで苦労したので、知らない方も多いと思い、参考のために載せておきます。

SELECT * FROM t1 LEFT OUTER JOIN t2
              ON (t1.c1 = t2.c1 AND t2.c2 < 100)
SELECT * FROM t1, t2
 WHERE t1.c1 = t2.c1(+) AND t2.c2(+) < 100

2. リフレッシュ
次に、MViewのリフレッシュについて説明します。
問合せは、処理済みのデータにアクセスするので速いですが、問題はMViewをリフレッシュする時間やオーバーヘッドになります。クエリー・リライトは、MViewが最新でないと動作しないので、どのようにリフレッシュするかも重要になります。そのため、より効果的にできる機能も拡張されているので、そのような機能を含めて、以下について簡単に説明します。

  • ・リフレッシュ・タイプ
  • ・リフレッシュ・モード
  • ・ホーム外リフレッシュ(Oracle12cから)
  • ・同期リフレッシュ(Oracle12cから)
  • ・ON STATEMENTリフレッシュ・モード(Oracle12cR2から)
  • ・リアルタイムMView(Oracle12cR2から)

(1)リフレッシュ・タイプ
リフレッシュ・タイプには、増分リフレッシュと完全リフレッシュがあり、増分リフレッシュではログベースとパーティション・チェンジ・トラッキング(PCT)の2つが使用できます。増分リフレッシュは、高速リフレッシュとも呼ばれ、完全リフレッシュよりも高速に実行されます。また、高速リフレッシュが可能なときは高速リフレッシュ(ログベース、PCTの順番)を実行し、可能でないときは完全リフレッシュを実行する強制リフレッシュ(FORCE)があり、これがデフォルトになっています。

  • ・完全リフレッシュ(COMPLETE)
    すべてのデータを再構築します。BUILD IMMEDIATE(これがデフォルト)で定義した場合に、最初に作成するときに実行されます。ただし、ON PREBUILT TABLE(事前作成コンテナ表の使用)で定義された場合は除きます。このリフレッシュを実行すると、MViewを定義する問合せが再実行されます。
  • ・ログベースの高速リフレッシュ(FAST)
    実表に対する変更は、MViewログまたはダイレクト・ローダー・ログに記録され、MViewを増分的にリフレッシュできるようにします。このリフレッシュは、変更点だけ適応するので高速ですが、使用するためには制限があるので、すべてのMViewで使用できる訳ではありません。
  • ・PCTリフレッシュ(FAST_PCT)
    実表がパーティション化している場合に使用でき、変更された実表のパーティションを使用して、MView内の影響を受けるパーティションまたはデータ部分を識別します。このリフレッシュを実行すると、影響を受ける部分のデータがすべて削除され、そこだけ再計算されます。パーティション・メンテナンス操作は、このリフレッシュだけになります。

(2)リフレッシュ・モード
リフレッシュ・モードには、トランザクションがコミットされるたびに行うON COMMITモード、実行したいときにPL/SQLパッケージで行うON DEMANDモードがあります。どちらも指定していないとON DEMANDモードになります。

  • ・ON COMMITリフレッシュ・モード
    トランザクションがコミットされるたびに更新されるので、MViewが常に最新データであることが保証されます。デメリットは、余分な処理によるコミットの完了時間が少し長くなることと、高速リフレッシュが可能なときだけになることです。
  • ・ON DEMANDリフレッシュ・モード
    DBMS_MVIEWパッケージ(3つのリフレッシュ・タイプとOracle12cからのホーム外リフレッシュ)とDBMS_SYNC_REFRESHパッケージ(Oracle12cからの同期リフレッシュ)を使用して、いつでもリフレッシュすることができます。

(3)ホーム外リフレッシュ
ホーム外(Out-of-Place)リフレッシュは、MViewコンテナ表(MView元表)に直接変更を行うホーム内(In-Place)リフレッシュとは異なり、リフレッシュを中間表(内部的に作成する表)に行い、完了後にMViewコンテナ表と切り替えます。これにより、大量のリフレッシュに対する高可用性を、アトミック・リフレッシュ(atomic_refresh=TRUE)を使用しなくても実現できます(アトミック・リフレッシュは、完全リフレッシュやパーティションMViewのPCTリフレッシュをDELETEとINSERTで行うので、TRUNCATEとダイレクト・パスで行うときよりも性能が問題になります)。また、同期リフレッシュやリアルタイムMViewで、リフレッシュ中にもクエリー・リライトできるようになります。ホーム外リフレッシュを実行するには、以下のようにDBMS_MVIEW.REFRESHプロシージャなどで、out_of_placeパラメータにTRUEを指定します。

exec DBMS_MVIEW.REFRESH('SUM_SALES_MV', method =>'?', atomic_refresh=>FALSE, out_of_place=>TRUE);

実行すると、以下のような中間表'RV$nnnn'を作成します(nnnnは、MViewコンテナ表の16進数オブジェクトIDです)。

img-1

(4)同期リフレッシュ
同期リフレッシュは、ホーム外リフレッシュ・メカニズムを使用して、実表とMViewを同期した状態に維持することで、クエリー・リライトできない時間を削減します。これは、高速リフレッシュ(ログベースとPCT)のいくつかの要素を組合せたもので、定期的にファクト表のデータが増えるような場合に適しています。ただし、スター・スキーマなどに準拠して、ファクト表と同様にMViewもパーティション化する必要があります。同期リフレッシュの実行は、DBMS_SYNC_REFRESHパッケージを使用して、以下のような手順で行います。

CREATE MATERIALIZED VIEW LOG ON sales FOR SYNCHRONOUS REFRESH USING st_sales; -- ステージング・ログの作成
exec DBMS_SYNC_REFRESH.REGISTER_MVIEWS('SUM_SALES_MV'); -- MViewの登録
<変更データの指定>
exec DBMS_SYNC_REFRESH.PREPARE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('SUM_SALES_MV')); -- 準備ステップ
exec DBMS_SYNC_REFRESH.EXECUTE_REFRESH(DBMS_SYNC_REFRESH.GET_GROUP_ID('SUM_SALES_MV')); -- 実行ステップ
exec DBMS_SYNC_REFRESH.UNREGISTER_MVIEWS('SUM_SALES_MV'); -- MViewの登録解除
DROP MATERIALIZED VIEW LOG on sales; -- ステージング・ログの削除

リフレッシュ操作は、準備と実行のステップに分割することで、使用できない時間の制御や削減を行います。準備ステップでは、すべての変更が交換用の表に記録されるので、MViewは問合せでも使用できます。実行ステップでは、ファクト表とMViewの該当パーティションを、この交換用の表と交換するだけです(ディメンション表の変更もリフレッシュされます)。このリフレッシュ操作を行う前に、変更データを指定する必要があり、以下の2つの方法で行うことができます。

  • ・ステージング・ログの使用
    実表がステージング領域の表からロードされる場合に、この表をMViewログと同じような役割のステージング・ログを使用して行います(MViewログとは異なり、変更データをロードして、MViewのリフレッシュと実表の適用に使用します)。変更データは、以下のような形式(追加制御列'DMLTYPE$$'とすべての列)でステージング・ログにロードして、検証する必要があります。
INSERT INTO st_sales(DMLTYPE$$, … ) VALUES('I', … );
…
exec DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG('SYNCREF_USER', 'SALES'); -- ステージング・ログの検証
  • ・パーティション単位のロード
    パーティション単位にロードするときには、ステージング・ログに変更データを指定するかわりに、影響するパーティションと交換する形式で、変更データを直接実表とMViewに適応することができます。指定は、以下のようにパーティション操作の登録を行います。ただし、ロードできるのはファクト表だけになります。

    CREATE TABLE sales_ex … ; -- パーティション交換用の表の作成
    <交換用の表にロード>
    begin
       DBMS_SYNC_REFRESH.REGISTER_PARTITION_OPERATION(
           partition_op                 => 'EXCHANGE',     -- パーティション操作
           schema_name                  => 'SYNCREF_USER',
           base_table_name              => 'SALES',
           partition_name               => 'FP3',
           outside_partn_table_schema   => 'SYNCREF_USER', -- 交換用の表のスキーマ名
           outside_partn_table_name     => 'SALES_EX');    -- 交換用の表
    end;
    /
    
    EXCHANGEパーティション以外に、DROP、TRUNCATEのパーティション操作が登録できます。それ以外の操作は、同期リフレッシュに登録された表でも直接実行できます。

(5)ON STATEMENTリフレッシュ・モード
この新しいリフレッシュ・モードでは、SQL文ごとにリフレッシュが可能になり、トランザクションをロールバックするとMViewもロールバックされます(ただし、指定できるのは、MViewを作成するときだけで、後からモードを変更することはできません。また、結合のみを含むMViewだけで、SELECTリストにファクト表のROWID列を含める必要があります)。このモードのメリットは、MViewログを保持するオーバーヘッドがなく、MViewと実表のデータが常に同期される点です(ただし、高速リフレッシュは、指定されている必要があります)。そのため、以下のようにMViewを指定します。

CREATE MATERIALIZED VIEW join_sales_cust_time_mv
  REFRESH FAST
  ON STATEMENT
  ENABLE QUERY REWRITE AS
  SELECT s.ROWID srowid, … FROM sales s, customers c, times t
   WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;

デメリットは、DML操作の実行時間がMViewのリフレッシュにより長くなることと、MViewに制限(ANSI準拠の結合文、インライン・ビュー、副問合せ、非表示列、複合主キー、LOB列などを指定できない)があることです。

(6)リアルタイムMView
最後に、リフレッシュではあれませんが、リフレッシュにも影響するリアルタイムMViewについて説明します。
リアルタイムMViewは、MViewが最新のデータにリフレッシュされていないときに、MViewログを使用して最新の問合せ結果を作成します。そのため、すべての実表にMViewログの作成が必要です。また、ホーム外高速リフレッシュ(ログベースまたはPCT)も使用できる必要があります。メリットは、リフレッシュしなくてもMViewにクエリー・リライトできることと、ホーム外リフレッシュ中でもクエリー・リライトが可能になることです。前回説明したPCTリライトでも可能ですが、パーティション表を使用していない、パーティション表を使用しても更新データが少ない(実表にアクセスするよりオーバーヘッドが少ない)ときには有効です。以下のようにMViewに対してENABLE ON QUERY COMPUTATION句を指定することで使用できます。

CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID ( … ) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID ( … ) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sum_sales_mv
  ENABLE QUERY REWRITE
  ENABLE ON QUERY COMPUTATION AS
  SELECT … ;

3. おわりに
今回はマテリアライズド・ビューのビューを使用したクエリー・リライトとリフレッシュについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。

津島博士のパフォーマンス講座 indexページ▶▶