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

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

 

---

皆さんこんにちは、インフルエンザが流行しているようですので、体調に気を付けてください。
今回は、問合せパフォーマンスに非常に有効なマテリアライズド・ビュー（MView）を取り上げます。MViewは、サマリー表（集計済みデータ）を作成するような機能ですので、あまり注意することがないかと思い、取り上げていませんでしたが、最近クエリー・リライトで苦労することがありましたので、それを含めて何回かに分けて説明しようと思います。まず今回は、MViewのタイプとクエリー・リライトについて説明しますので、参考にしてください。

1. MViewのタイプ
MViewは、指定できる内容の制限がごく少数で、必要な数の表をすべて結合することができます。このとき表以外に、ビュー、インライン・ビュー、副問合せなどもSELECT句で結合や参照できますが、SELECTリストに副問合せを持つMViewは定義できません。このようなMViewには、次のタイプがあり、ログベースの高速リフレッシュを使用するときに少し注意が必要です。

• 集計を含むMView
  事前集計することで行数を削減できるので、サマリー表として最も多く使用されるMViewです。高速リフレッシュでサポートされる集計関数は、SUM、COUNT、AVG、STDDEV、VARIANCE、MINおよびMAXのみで、COUNT(*)を含めないとINSERTだけの高速リフレッシュになります。
• 結合のみを含むMView
  集計が含まれない結合のみのMViewです。結合する表が多い場合で、頻繁に行われる特定の結合を高速にしたいときに有効です（結合カーディナリティが正しくないときに、このオプティマイザ統計で改善することもできます）。高速リフレッシュでは、FROM句のすべての表のROWIDが、MViewとMViewログにある必要があります。すべての表にないときは、ある表だけ高速リフレッシュになります。
• ネステッドMView
  別のMViewを参照するMViewで、実表も参照することができます。異なるMViewから同じ実表に対する結合が多いと、リフレッシュの負荷が大きくなるので、それを効果的に行うときなどに使用します（これから結合のみを含むMViewを参照する場合が多いです）。

2. クエリー・リライト
MViewは、どうしてもSQLチューニングが難しいときなどに、MViewを作成して問合せ変換（クエリー・リライト）させることで、簡単にパフォーマンスを改善することができます。ただし、MViewが最新データでないとリライトされないので、データの更新も考えてMViewを作成する必要があります。また、リライトしないときもあるので、苦労しないようにクエリー・リライトの注意点も含めて説明していきます。

（１）クエリー・リライトの制御
まずは、クエリー・リライトの有効化と精度について説明します。
クエリー・リライトは、以下のようにMViewに対してENABLE QUERY REWRITE句を指定すると、コストが低いときに行われます（つまり、MViewのオプティマイザ統計が正しく収集されていないと行われません）。

これは、初期化パラメータQUERY_REWRITE_ENABLEDで、以下のように変更することができます（デフォルトはTRUEです）。オプティマイザ統計が正しくないときなどに使用します。REWRITEヒント（/*+ REWRITE(<MView名>) */）で、強制的に特定のMViewにリライトさせるようにもできます（NOREWRITEヒントで強制的にリライトさせないようにもできます）。

• TRUE（リライトのコストが低いときに行う）
• FALSE（コストが低くてもリライトしない）
• FORCE（コストが高くてもリライトする）

また、初期化パラメータQUERY_REWRITE_INTEGRITYで、クエリー・リライトの整合性レベルを、以下のように指定できます。これにより参照整合性制約（外部キー制約）の状態によって、リライトするかどうかを指定できます。

• ENFORCED
  整合性が保証されたMViewの最新データだけが使用されるので、ENABLED VALIDATEDの制約が必要になります（これがデフォルトです）。
• TRUSTED
  NOVALIDATED（未検証）の制約に、RELY制約を設定しているものも対象になります。第34回で説明した結合の排除（Join Elimination）は、Oracle12cからクエリー・リライトと同じ条件で動作します（つまり、外部キー制約がNOVALIDATEDの場合、ENFORCEDでは動作しません）。
• STALE_TOLERATED
  データが最新かどうかを考慮しません。そのため、失効データを含むMViewもリライトされます。

（２）一般的なクエリー・リライト
次に、理解しやすいように、一般的な（集計を含むMViewの）クエリー・リライトについて説明します。 MViewのテキスト完全一致（SELECT文全体と一致）やテキスト部分一致（FROM句以降のテキストと一致）のリライト以外に、以下のようなタイプのクエリー・リライトが可能です。

• 集計可能性（Aggregate Computability）
• 集計ロールアップ（Aggregate Rollup）
• デルタ結合（Delta Join）
• データのフィルタリング
• ディメンションを使用したロールアップ
• 後戻り結合（Join Back）
• 複数のMViewを使用したリライト
• パーティション・チェンジ・トラッキング(PCT)リライト

分かりやすいように、先ほどのMView’sum_sales_pscat_week_mv’（プロダクト・サブカテゴリ別週別売上集計）を使用して、それぞれのクエリー・リライトを説明していきます。

(a)集計可能性（Aggregate Computability）
MViewに含まれている集計関数から、導出または計算可能なときのリライトです。以下の問合せは、SUMとCOUNTのMViewに対して、AVG集計を行ったものです。

 

以下のようなSQLにリライトされます（AVGはSUM/COUNTで求めることができるからです）。

SELECT prod_subcategory, week_ending_day, sum_amount_sold/count_amount_sold AS avg_sales
  FROM sum_sales_pscat_week_mv;

導出可能なものとして、外部結合のMViewから内部結合の問合せもリライトすることができます。

(b)集計ロールアップ（Aggregate Rollup）とデルタ結合（Delta Join）
MViewに格納されているグルーピングから、ロールアップ（上位レベルのグルーピング）が可能なときのリライトです。以下の問合せは、prod_subcategoryとweek_ending_dayでグルーピングされたMViewに対して、prod_subcategoryだけのグルーピングを行ったものです。

以下のようなSQLにリライトされます。ただし、この問合せは、times表に対してMViewデルタ結合（MViewに存在する結合が問合せにない）を実行することになり、無損失結合にするためsales表の外部キー(time_id)にNOT NULL制約が必要になるので、注意してください。

SELECT prod_subcategory, SUM(sum_amount_sold) FROM sum_sales_pscat_week_mv GROUP BY prod_subcategory;

問合せデルタ結合（問合せだけにある結合）もあり、MViewに結合列が含んでいればリライトすることができます。

(c)データのフィルタリング
フィルタリングのデータ・サブセットを取得可能なときのリライトです。以下の問合せは、すべてのprod_subcategoryが含んでいるMViewに対して、’Camera Media’だけを指定して実行したものです。

 

以下のようなフィルターを追加したSQLにリライトされます。

SELECT prod_subcategory, week_ending_day, sum_amount_sold FROM sum_sales_pscat_week_mv
 WHERE prod_subcategory = 'Camera Media';

MViewにない列のフィルタリングは、リライトすることができないので、注意してください。

(d)ディメンションを使用したロールアップ
MViewにない列を、ディメンションを使用してロールアップすることが可能なときのリライトです。
MViewにない列でグルーピングする場合でも、ディメンションのHIERARCHY句（列の階層レベル）を使用してロールアップすることができます。以下のようなプロダクトのディメンション（カテゴリ、サブカテゴリ、プロダクトの階層）が作成されていたときの動作を説明します。

CREATE DIMENSION products_dim 
        LEVEL product      IS (products.prod_id)
        LEVEL subcategory  IS (products.prod_subcategory) 
        LEVEL category     IS (products.prod_category) 
        HIERARCHY prod_rollup (product CHILD OF subcategory CHILD OF category)
        ATTRIBUTE subcategory DETERMINES products.prod_subcat_desc;

以下の問合せは、MViewにないprod_categoryでグルーピングを実行したものです。

以下のようにproducts表と結合して、prod_categoryとweek_ending_dayでグルーピングするSQLにリライトされます。ただし、ディメンションを利用するには、QUERY_REWRITE_INTEGRITYをTRUSTEDまたはSTALE_TOLERATEDにする必要があります。

SELECT pv.prod_category, mv.week_ending_day, SUM(mv.sum_amount_sold)
  FROM sum_sales_pscat_week_mv mv, (SELECT DISTINCT prod_subcategory, prod_category FROM products) pv
 WHERE mv.prod_subcategory = pv.prod_subcategory GROUP BY pv.prod_category, mv.week_ending_day;

(e)後戻り結合（Join Back）
MViewにない列を、再結合して求めることが可能なときのリライトで、主キーまたはディメンジョンのDETERMINES句（列の依存性）を使用して行うことができます。主キーの方は、列を特定するために、主キーをMViewに含める必要があり、使用できない場合もあります。そのため、ここではディメンションのDETERMINES句を使用したリライトとして、先程のプロダクトのディメンション（prod_subcategoryはprod_subcat_descを決定できる）を使用して説明します。以下の問合せは、MViewにないprod_subcat_descに対して、選択条件を指定して実行したものです。

以下のようなproducts表と結合するSQLにリライトされます（ロールアップのように、再度グルーピングを行っているのは、GROUP BY列を変更したためです）。

SELECT pv.prod_subcat_desc, mv.week_ending_day, SUM(mv.sum_amount_sold)
  FROM sum_sales_pscat_week_mv mv, (SELECT DISTINCT prod_subcategory, prod_subcat_desc FROM products) pv
 WHERE mv.prod_subcategory = pv.prod_subcategory AND pv.prod_subcat_desc LIKE '%Men'
 GROUP BY pv.prod_subcat_desc, mv.week_ending_day;

 

(f)複数のMViewを使用したリライトとPCTリライト
Oracle10gからの複数のMViewを使用することが可能なときのリライトです。一つのMViewでは解決できない場合に、複数のMView（MViewがないときには実表）を使用して解決することができます。NO_MULTIMV_REWRITEヒント（二つ以上のMViewのリライト）やNO_BASETABLE_MULTIMV_REWRITEヒント（MViewと実表の組合せによるリライト）で、実行しないようにもできます。
パーティション表のときは、PCTリライトによりパーティション単位に最新が識別され、最新のパーティションだけリライトされます（ただし、ハッシュ・パーティションは動作しません）。このとき問合せに、最新でないパーティションが含む場合、複数のMViewを使用したリライトによって、MViewと実表を使用してリライトすることもできます。
複数のMViewを使用したリライトとPCTリライトを使用した例として、以下のようなパーティション表とMViewを使用して説明します。

CREATE TABLE part_sales_by_time (time_id, prod_id, amount_sold, quantity_sold)
  PARTITION BY RANGE (time_id)
  ( PARTITION s1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')),
    PARTITION s1999q1 VALUES LESS THAN (TO_DATE('1999-04-01','YYYY-MM-DD')),
    PARTITION s1999q2 VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD')),
    PARTITION s1999q3 VALUES LESS THAN (TO_DATE('1999-10-01','YYYY-MM-DD')),
    PARTITION s1999q4 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')),
    PARTITION s2000 VALUES LESS THAN (MAXVALUE) )
  AS SELECT s.time_id, s.prod_id, s.amount_sold, s.quantity_sold FROM sales s;

CREATE MATERIALIZED VIEW  sales_in_1999_mv
  ENABLE QUERY REWRITE AS
  SELECT s.time_id, p.prod_name, SUM(s.quantity_sold) AS sum_quantity_sold
    FROM part_sales_by_time s, products p
   WHERE p.prod_id = s.prod_id
     AND s.time_id BETWEEN TO_DATE('1999-01-01','YYYY-MM-DD') AND TO_DATE('1999-12-31','YYYY-MM-DD')
   GROUP BY s.time_id, p.prod_name;

上記のパーティション表とMViewに対して、以下のINSERT文を実行するとMViewは失効状態になります。

INSERT INTO part_sales_by_time VALUES (TO_DATE('1999-12-26','YYYY-MM-DD'), 38920, 2500, 20);
COMMIT;

パーティション表を使用したときの失効状態は、以下のようにDBA_MVIEW_DETAIL_PARTITIONビューで確認することができます（パーティション’s1999q4’が失効状態になっています）。

 

以下の問合せは、上記の状態でパーティションs1999q3とs1999q4に対して実行したものです。

 

以下のようなUNION ALLで、MViewと実表を使用するSQLにリライトされます。ただし、QUERY_REWRITE_INTEGRITYがSTALE_TOLERATEDのときは、最新データが考慮されないので、PCTリライトは動作しません。

SELECT time_id, prod_name, sum_quantity_sold FROM sales_in_1999_mv
 WHERE time_id BETWEEN TO_DATE('1999-07-01','YYYY-MM-DD') AND TO_DATE('1999-09-30','YYYY-MM-DD')
UNION ALL
SELECT s.time_id, p.prod_name, SUM(s.quantity_sold) FROM part_sales_by_time s, products p
 WHERE p.prod_id = s.prod_id AND s.time_id <= TO_DATE('1999-10-31','YYYY-MM-DD')
 GROUP BY s.time_id, p.prod_name;

このように一つのMViewで、様々な問合せのクエリー・リライトができるので、使ったことのない方は是非とも簡単なものから利用してみてください。
ただし、以下のようなANSI準拠の結合文を使用したMViewでは、テキスト一致レベルのクエリー・リライトだけしか行われないので、Oracle固有の結合文を使用してください（問合せについては、ANSI準拠の結合文を使用してもテキスト一致以外のクエリー・リライトが行われます）。

 

（３）クエリー・リライトの確認
最後に、クエリー・リライトの確認方法について説明します。
クエリー・リライトは、実行計画を見れば動作したかの判断はできますが、実施されないときの原因までは出力されません。そのため、以下のようにDBMS_MVIEW.EXPLAIN_REWRITEプロシージャで、クエリー・リライトの確認を行うことができます（DBMS_MVIEW.EXPLAIN_REWRITEプロシージャを実行すると、結果がREWRITE_TABLE表に出力されるので、事前にutlxrw.sqlで作成する必要があります）。リライトしていないときには、その原因を確認することもできます。以下の例は、クエリー・リライトのコストが高いため、行わなかったことを示しています。

 

以下のようにリライトされたSQLを出力することもできます。

SQL> SELECT distinct rewritten_txt FROM rewrite_table WHERE statement_id = 'TEST01';

REWRITTEN_TXT
-------------------------------------------------------------------------------------
SELECT …

 

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

---

ページトップへ戻る▲ 

 

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