6 高度なマテリアライズド・ビュー
この章では、マテリアライズド・ビューの高度な使用方法について説明します。次の項目が含まれます。
6.1 パーティション化とマテリアライズド・ビューについて
データ・ウェアハウスに格納されているデータの量は膨大であるため、パーティション化は、データベースの設計時に非常に有効なオプションです。ファクト表のパーティション化によって、スケーラビリティの向上とシステム管理の簡素化を実現できます。また、効率的に再作成できるローカル索引を定義できるようになります。ファクト表をパーティション化することにより、マテリアライズド・ビューに対するパーティション・チェンジ・トラッキング(PCT)リフレッシュが可能になるので、マテリアライズド・ビューを高速リフレッシュできる可能性も高くなります。マテリアライズド・ビューのパーティション化は、リフレッシュの面でもメリットがあります。リフレッシュ・プロシージャは、より多くの場合にパラレルDMLを使用できるようになり、PCTベースのリフレッシュは、パーティションの切捨てを使用してマテリアライズド・ビューを効率的にメンテナンスできるようになります。
関連項目:
パーティション化の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。
この項では、次の項目について説明します。
6.1.1 パーティション・チェンジ・トラッキングについて
最新の状態かどうかの追跡対象を、マテリアライズド・ビュー全体ではなく、より細かく限定でき、それによるメリットが得られます。これはパーティション・チェンジ・トラッキング(PCT)により実現できます。PCTとは、特定のディテール表のパーティションにより影響を受ける、マテリアライズド・ビュー内の行を識別する方法です。1つ以上のディテール表がパーティション化されている場合は、マテリアライズド・ビュー内で、変更されたディテール・パーティションに対応する特定の行を識別できます。これらの行はパーティションが変更されると失効しますが、他のすべての行は最新のままです。
PCTを使用すると、特定のパーティションに対応するマテリアライズド・ビューの行を識別できます。PCTは、ディテール表に対するパーティション・メンテナンス操作後の高速リフレッシュのサポートにも使用されます。たとえば、ディテール表のパーティションが切り捨てられるか削除されると、マテリアライズド・ビュー内で影響を受ける行が識別され、削除されます。
QUERY_REWRITE_INTEGRITY
= ENFORCED
またはTRUSTED
モードのときは、マテリアライズド・ビュー全体を失効とみなすのではなく、マテリアライズド・ビューの行のうち最新のものと失効しているものが識別され、最新状態の行をクエリー・リライトで使用できるようになります。DBA_MVIEW_DETAIL_PARTITION
などの一部のビューでは、どのパーティションが失効しているかまたは最新なのかが具体的に示されます。マテリアライズド・ビューに結合依存の式があることによって、変更対象の表のパーティション・チェンジ・トラッキングが使用可能になる場合は、部分的に失効しているマテリアライズド・ビューへのリライトは行われません。
関連項目:
詳細は、結合依存の式およびパーティション・チェンジ・トラッキングについてを参照してください。
パーティション・チェンジ・トラッキングは、パーティション・レベルおよびサブパーティション・レベルでの失効を追跡しますが(コンポジット・パーティション表に関して)、PCTリフレッシュの粒度のレベルは、パーティション化戦略の最上位レベルのみであることに注意してください。その結果、コンポジット・パーティション表のサブパーティションの1つにあるデータに変更があると、影響を受けた1つのサブパーティションのみが失効としてマークされ、表の残りの部分はリライトが可能になります。ただし、PCTリフレッシュにより、影響を受けたサブパーティションを含む、パーティション全体がリフレッシュされます。
PCTをサポートするには、マテリアライズド・ビューが次の要件を満たしている必要があります。
-
マテリアライズド・ビューで参照される1つ以上のディテール表が、パーティション化されている必要があります。
-
パーティション表には、レンジ、リストまたはコンポジットのいずれかのパーティション化を使用し、レンジまたはリストはパーティション化戦略の最上位レベルである必要があります。
-
最上位レベルのパーティション・キーは、単一列のみで構成する必要があります。
-
マテリアライズド・ビューには、ディテール表のパーティション・キー列、パーティション・マーカー、
ROWID
または結合依存の式のいずれかを含める必要があります。 -
GROUP
BY
句を使用する場合は、パーティション・キー列、パーティション・マーカー、ROWID
または結合依存の式をGROUP
BY
句に指定する必要があります。 -
分析ウィンドウ関数または
MODEL
句を使用する場合は、パーティション・キー列、パーティション・マーカー、ROWID
または結合依存の式を、それぞれのPARTITION
BY
副次句に指定する必要があります。 -
データ修正の発生が可能なのは、パーティション表のみです。マテリアライズド・ビューに結合依存の式を含む表に対してPCTリフレッシュを実行する際は、いずれの結合依存の表についても、データ修正が発生していてはなりません。
-
COMPATIBILITY
初期化パラメータは9.0.0.0.0以上に設定する必要があります。
ビュー、リモート表または外部結合を参照するマテリアライズド・ビューの場合、PCTはサポートされません。
関連項目:
DBMS_MVIEW.PMARKER
関数およびパーティション・マーカーの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
この項では、次の項目について説明します。
6.1.1.1 パーティション・キーおよびパーティション・チェンジ・トラッキングについて
パーティション・チェンジ・トラッキングは、マテリアライズド・ビューに必要な情報が存在すれば、ベースとなるディテール表のパーティションの各行を、対応するマテリアライズド・ビューの各行に関連付けることができます。そのためには、ディテール表のパーティション・キー列をSELECT
構文のリストに含めます。また、GROUP
BY
を使用する場合は、GROUP
BY
リストにも含めます。
例として、日次の顧客売上を格納するマテリアライズド・ビューを考えてみます。次の例では、sh
サンプル・スキーマおよび3つのディテール表(sales
、products
およびtimes
)を使用してマテリアライズド・ビューを作成します。sales
表はtime_id
列でパーティション化されており、products
表はprod_id
列でパーティション化されています。times
表はパーティション表ではありません。
例6-1 パーティション・キーを使用したマテリアライズドビュー
CREATE MATERIALIZED VIEW LOG ON SALES WITH ROWID (prod_id, time_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON PRODUCTS WITH ROWID (prod_id, prod_name, prod_desc) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON TIMES WITH ROWID (time_id, calendar_month_name, calendar_year) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW cust_dly_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT s.time_id, p.prod_id, p.prod_name, COUNT(*), SUM(s.quantity_sold), SUM(s.amount_sold), COUNT(s.quantity_sold), COUNT(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY s.time_id, p.prod_id, p.prod_name;
cust_dly_sales_mv
では、sales
表でPCTが使用可能になります。これは、パーティション化キー列time_id
がマテリアライズド・ビューに含まれているためです。
6.1.1.2 結合依存式およびパーティション・チェンジ・トラッキングについて
結合依存の式とは、パーティション化キーでの等価結合を介して直接的または間接的にパーティション化ディテール表に結合されている表の列で構成され、結合キーのディメンション属性またはディメンション階層の親である式のことです。ディテール表へのパスに存在する一連の表は、結合依存の表と呼ばれます。次の点を考慮してください。
SELECT s.time_id, t.calendar_month_name FROM sales s, times t WHERE s.time_id = t.time_id;
この問合せでは、times
表が結合依存の表です。この表は、パーティション化キー列のtime_id
でsales
表に結合されているためです。また、calendar_month_name
はtimes.time_id
のディメンション階層属性です。これは、calendar_month_name
がtimes.mon_id
の属性で、times.mon_id
がtimes.time_id
のディメンション階層の親であるためです。したがって、times
表からの式calendar_month_name
は結合依存の式ということになります。次に、別の例について考えてみます。
SELECT s.time_id, y.calendar_year_name FROM sales s, times_d d, times_m m, times_y y WHERE s.time_id = d.time_id AND d.day_id = m.day_id AND m.mon_id = y.mon_id;
ここで、times
表はtimes_d
、times_m
およびtimes_y
の各表に非正規化されています。times_y
表からの式calendar_year_name
は結合依存の式で、times_d
、times_m
およびtimes_y
の各表は結合依存の表です。その理由は、times_y
表がtimes_m
表およびtimes_d
表を介して、そのパーティション化キー列time_id
で間接的にsales表に結合されているためです。
これによりユーザーは、ディテール表のパーティション化キーよりも上位のレベルでの集計を含むマテリアライズド・ビューを作成できます。次に、月次の顧客売上を格納するマテリアライズド・ビューの例を見てみます。
例6-2 マテリアライズド・ビューの作成: 結合依存の式
マテリアライズド・ビュー・ログは定義済であるとすると、このマテリアライズド・ビューは、次のDDLで作成できます。
CREATE MATERIALIZED VIEW cust_mth_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT t.calendar_month_name, p.prod_id, p.prod_name, COUNT(*), SUM(s.quantity_sold), SUM(s.amount_sold), COUNT(s.quantity_sold), COUNT(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY t.calendar_month_name, p.prod_id, p.prod_name;
ここでは、結合依存の表times
と、times.calendar_month_name
はtimes.time_id
によって決定されるディメンション属性であるというリレーションシップを使用して、ディテール表の行を、対応するマテリアライズド・ビューの行に関連付けることができます。これにより、sales
表のパーティション・チェンジ・トラッキングが可能になります。さらに、マテリアライズド・ビューにproducts表のパーティション化キー列prod_id
が含まれているので、products表のPCTも可能になります。
6.1.1.3 パーティション・マーカーおよびパーティション・チェンジ・トラッキングについて
DBMS_MVIEW.PMARKER
ファンクションは、マテリアライズド・ビューのカーディナリティ(表の行数に対する個別値の数の比率)を大幅に削減するように設計されています(例については、例6-3を参照)。このファンクションは、指定されたパーティション表内の指定された行のパーティションまたはサブパーティションを、一意に識別するパーティションの識別子を返します。したがって、DBMS_MVIEW.PMARKER
ファンクションは、SELECT
句およびGROUP
BY
句のパーティション・キー列のかわりに使用されます。
マテリアライズド・ビューでのPL/SQLファンクションの一般的なケースとは異なり、DBMS_MVIEW.PMARKER
を使用すると、リライト・モードがQUERY_REWRITE_INTEGRITY = ENFORCED
であっても、そのマテリアライズド・ビューでのリライトは可能です。
PMARKER
ファンクションの使用例として、指定した年の製品カテゴリ別収益などの典型的な数値の計算を考えてみます。毎月1,000種類の製品が販売されていた場合、マテリアライズド・ビューには12,000行が格納されていることになります。
例6-3 マテリアライズド・ビューにおけるパーティション・マーカーの使用
例として、製品カテゴリごとの年間売上収益を格納するマテリアライズド・ビューを考えます。各製品カテゴリにはおよそ数百種類の製品があるので、products
表のパーティション化キー列prod_id
をマテリアライズド・ビューに含めると、カーディナリティはかなり高くなります。このマテリアライズド・ビューでは、かわりにDBMS_MVIEW.PMARKER
ファンクションを使用します。この場合、マテリアライズド・ビューのカーディナリティは、products
表のパーティションの数だけ倍加します。
CREATE MATERIALIZED VIEW prod_yr_sales_mv BUILD DEFERRED REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS SELECT DBMS_MVIEW.PMARKER(p.rowid), p.prod_category, t.calendar_year, COUNT(*), SUM(s.amount_sold), SUM(s.quantity_sold), COUNT(s.amount_sold), COUNT(s.quantity_sold) FROM sales s, products p, times t WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id GROUP BY DBMS_MVIEW.PMARKER (p.rowid), p.prod_category, t.calendar_year;
prod_yr_sales_mv
のSELECT
構文のリストには、products
表に対するDBMS_MVIEW.PMARKER
ファンクションが含まれています。これにより、パーティション・キー列prod_id
でグルーピングした場合よりもカーディナリティに与える影響を大幅に抑えながら、products
表のパーティション・チェンジ・トラッキングが可能になります。この例では、prod_yr_sales_mv
に必要な集計レベルは、products.prod_category
別にグルーピングすることです。DBMS_MVIEW.PMARKER
ファンクションを使用した場合、マテリアライズド・ビューのカーディナリティは、products
表のパーティションの数だけ倍加するだけで済みます。通常、この方法では、パーティション・キー列を含めた場合より、カーディナリティへの影響が大幅に小さくなります。
sales
表でパーティション・チェンジ・トラッキングが使用可能になることに注意してください。これは、結合依存の式calendar_year
がSELECT
構文のリストに含まれているためです。
6.1.1.4 パーティション・チェンジ・トラッキングでの部分的なリライトについて
後続のINSERT
文で、表sales
のsales_part3
パーティションに新しい行が追加されると仮定します。cust_dly_sales_mv
は、パーティション・キーを使用して表sales
でのPCTが可能になっているため、Oracleはこの時点で、sales_part3
パーティションに対応するマテリアライズド・ビューcust_dly_sales_mv
の失効している行を識別できます(その他の行は最新状態なので変更されません)。クエリー・リライトは、マテリアライズド・ビューcust_mth_sales_mv
およびprod_yr_sales_mv
の最新部分を識別できません。これは、PCTが結合依存の式を使用して表salesで使用可能になっているためです。クエリー・リライトは、PCTがパーティション・キーまたはパーティション・マーカーを使用してディテール表で使用可能になっている場合にのみ、ディテール表への変更発生時にマテリアライズド・ビューの最新部分を識別できます。
6.1.2 マテリアライズド・ビューのパーティション化
次の例に示すように、マテリアライズド・ビューのパーティション化には、Oracleの標準パーティション化句を使用したマテリアライズド・ビューの定義が含まれます。この文では、part_sales_mv
というマテリアライズド・ビューが作成されます。このマテリアライズド・ビューは3つのパーティションを使用し、高速リフレッシュが可能で、クエリー・リライトに使用できます。
CREATE MATERIALIZED VIEW part_sales_mv PARALLEL PARTITION BY RANGE (time_id) (PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf1, PARTITION month2 VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf2, PARTITION month3 VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf3) BUILD DEFERRED REFRESH FAST ENABLE QUERY REWRITE AS SELECT s.cust_id, s.time_id, SUM(s.amount_sold) AS sum_dol_sales, SUM(s.quantity_sold) AS sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id;
6.1.3 事前作成表のパーティション化
マテリアライズド・ビューは、パーティション化された事前作成表に登録できます。マテリアライズド・ビューのパーティション化によるメリットでは、事前作成表のパーティション化の利点について説明します。次に、この例を示します。
CREATE TABLE part_sales_tab_mv(time_id, cust_id, sum_dollar_sales, sum_unit_sale) PARALLEL PARTITION BY RANGE (time_id) (PARTITION month1 VALUES LESS THAN (TO_DATE('31-12-1998', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf1, PARTITION month2 VALUES LESS THAN (TO_DATE('31-12-1999', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf2, PARTITION month3 VALUES LESS THAN (TO_DATE('31-12-2000', 'DD-MM-YYYY')) PCTFREE 0 STORAGE (INITIAL 8M) TABLESPACE sf3) AS SELECT s.time_id, s.cust_id, SUM(s.amount_sold) AS sum_dollar_sales, SUM(s.quantity_sold) AS sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id; CREATE MATERIALIZED VIEW part_sales_tab_mv ON PREBUILT TABLE ENABLE QUERY REWRITE AS SELECT s.time_id, s.cust_id, SUM(s.amount_sold) AS sum_dollar_sales, SUM(s.quantity_sold) AS sum_unit_sales FROM sales s GROUP BY s.time_id, s.cust_id;
この例では、part_sales_tab_mv
表は3か月ごとにパーティション化され、事前作成表を使用するためにマテリアライズド・ビューが登録されています。このマテリアライズド・ビューは、ENABLE
QUERY
REWRITE
句を含んでいるため、クエリー・リライトに使用できます。
6.1.3.1 マテリアライズド・ビューのパーティション化によるメリット
マテリアライズド・ビューをディテール表のパーティション化キー列または結合依存の式でパーティション化すると、TRUNCATE
PARTITION
文を使用してリフレッシュの際にマテリアライズド・ビューの1つ以上のパーティションを削除し、後でそのパーティションに新しいデータを再移入するという作業がより効率的になります。Oracle Databaseは、次の条件とパーティション・チェンジ・トラッキングについてで説明したその他の条件が満たされている場合に、パーティションの切捨てを伴うこの種の高速リフレッシュ(PCTリフレッシュと呼ぶ)を使用します。
-
マテリアライズド・ビューが、ディテール表のパーティション化キー列または結合依存の式でパーティション化されている。
-
パーティション化キー列または結合式を使用してPCTが使用可能になっている場合、マテリアライズド・ビューはレンジ・パーティション化またはリスト・パーティション化されている必要がある。
-
PCTリフレッシュがアトミックではない。
6.1.4 ローリング・マテリアライズド・ビュー
データ・ウェアハウスまたはデータ・マートに時間ディメンションが含まれる場合、通常は最も古い情報をアーカイブしてから、その記憶域を新しい情報に再使用する必要があります。これは、ローリング・ウィンドウ・シナリオと呼ばれます。ファクト表またはマテリアライズド・ビューに時間ディメンションが含まれ、時間属性によって水平にパーティション化されている場合、ロールアウトされるデータの量がレンジ・パーティション化の場合と等しいか、または少なくとも整列していれば、ローリング・マテリアライズド・ビューの管理が、高速で管理コストの低いパーティション管理のみに軽減されます。
データ・ウェアハウスにローリング・マテリアライズド・ビューを作成する場合は、パーティション・メンテナンス操作を実行する頻度を決定する必要があります。また、ファクト表およびマテリアライズド・ビューをパーティション化して、古いデータが必要なくなったときに必要なシステム管理によるオーバーヘッドを削減する必要があります。また、頻繁には更新されないパーティションに対してはデータ圧縮の使用も考慮します。
レンジ・パーティション化の使用には、制限はありません。たとえば、時間値およびキー値の両方を使用したコンポジット・パーティション化が、データに対して適切なパーティション・ソリューションとなる場合もあります。
関連項目:
CONSIDER
FRESH
および圧縮の詳細は、マテリアライズド・ビューのリフレッシュを参照してください。
6.2 分析処理環境でのマテリアライズド・ビューについて
この項では、分析SQLで使用される概念と、リレーショナル・データベースでこのタイプの問合せを処理する方法を説明します。また、一般的な例を使用してマテリアライズド・ビューを作成する最適な方法を示します。
次のトピックでは、各種環境でのマテリアライズド・ビューの詳細を説明しています。
6.2.2 マテリアライズド・ビューと階層的キューブについて
データ・ウェアハウス環境では一般にデータをスター・スキーマ形式で表示しますが、分析SQL問合せではデータを階層的キューブ形式で表示します。階層的キューブにはそのディメンションそれぞれのロールアップ階層に沿って集計されたデータが含まれ、これらの集計はディメンションをまたがって結合されます。ビジネス・インテリジェンス問合せに必要な典型的な集計の集合が含まれます。
例6-4 階層的キューブ
次の2つのディメンションを持つ売上データセットがあり、それぞれのディメンションに4レベルの階層があるとします。
-
時間。(all times)、year、quarterおよびmonthを含みます。
-
製品。(all products)、division、brandおよびitemを含みます。
つまり、階層的キューブ内には16個の集計グループがあることになります。これは、時間の4つのレベルと製品の4つのレベルを掛け合せてキューブが生成されるためです。各ディメンションの4つのレベルを表6-1に示します。
表6-1 時間別および製品別ROLLUP
時間別ROLLUP | 製品別ROLLUP |
---|---|
year、quarter、month |
division、brand、item |
year、quarter |
division、brand |
year |
division |
all times |
all products |
ディメンションとレベルの数を増やすと、計算するグループ数が急激に増えることに注意してください。この例には16個のグループが含まれていますが、同数レベルのディメンションを2つ追加するだけで、4×4×4×4 = 256個のグループになります。また、ディメンションに階層が複数あると、同じようにグループ数が増えることも考慮してください。たとえば、時間ディメンションにもう1つの会計月という階層があるとします。会計月は会計四半期、さらに会計年にロールアップします。爆発的に増えるグループの処理は、歴史的に、オンライン分析処理システム用のデータ格納での大きな課題です。
典型的なオンライン分析問合せでは、キューブの様々な部分をスライスおよびダイスして、1つのレベルの集計と別のレベルの集計を比較します。たとえば、問合せにより、2002年1月の食料雑貨部門の売上を求め、2001年全体の食料雑貨部門の売上合計とこの値を比較する場合があります。
6.2.3 マテリアライズド・ビューのパーティション化によるメリット
複数の集計グループを含むマテリアライズド・ビューのパフォーマンスがリフレッシュおよびクエリー・リライト向けに最大化されるのは、適切にパーティション化されている場合です。
ローリング・ウィンドウ・シナリオでのPCTリフレッシュには、時間ディメンションのいくつかのレベルにおける最上位レベルでのパーティション化が必要です。また、このマテリアライズド・ビューへのクエリー・リライトでパーティション・プルーニングを行うには、GROUPING_ID
列でのパーティション化が必要です。したがって、これらのマテリアライズド・ビューのパーティション化方法として最も効果的なのは、コンポジット・パーティション化((time
, GROUPING_ID
)列でのレンジ-リスト・パーティション化)ということになります。この方法でマテリアライズド・ビューをパーティション化すると、次のことが可能になります。
-
PCTリフレッシュ: リフレッシュのパフォーマンスが向上します。
-
パーティション・プルーニング: 関連する集計グループのみがアクセスされるため、問合せの処理コストが大幅に削減されます。
PCTリフレッシュを使用しない場合は、GROUPING_ID
列のみでリスト・パーティション化します。
6.2.4 マテリアライズド・ビューの圧縮について
冗長性の高いデータ(多数の外部キーを持つ表など)を使用するときは、データ圧縮を考慮するようにします。特に、ROLLUP
句を使用して作成したマテリアライズド・ビューがこの候補になります。
関連項目:
-
データの圧縮の構文および制限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
圧縮の詳細は、マテリアライズド・ビューの記憶域および表の圧縮についてを参照してください。
6.2.5 集合演算子を含むマテリアライズド・ビューについて
Oracle Databaseでは、定義問合せに集合演算子が含まれるマテリアライズド・ビューをサポートしています。集合演算子を含むマテリアライズド・ビューをクエリー・リライト対応として作成できるようになりました。マテリアライズド・ビューは、ON
COMMIT
またはON
DEMAND
リフレッシュのいずれかを使用してリフレッシュできます。
定義問合せの最上位レベルにUNION
ALL
演算子が含まれ、UNION
ALL
内の各問合せブロックが、集計を含むマテリアライズド・ビューまたは結合のみを含むマテリアライズド・ビューの要件を満たす場合は、高速リフレッシュがサポートされます。さらに、マテリアライズド・ビューには、各問合せブロック内の個別の値を含む定数列(UNION
ALL
マーカー)を含める必要があります。これは、次の例では、列1 marker
と2 marker
です。
関連項目:
UNION
ALL
を使用したマテリアライズド・ビューの高速リフレッシュに関する制限の詳細は、UNION ALLを含むマテリアライズド・ビューの高速リフレッシュに関する制限を参照してください。
6.2.5.1 UNION ALLを使用するマテリアライズド・ビューの例
次に、UNION
ALL
を含む、高速リフレッシュ可能なマテリアライズド・ビューの作成例を示します。
例6-5 2つの結合ビューを伴うUNION ALLを使用するマテリアライズド・ビュー
2つの結合ビューを持つUNION
ALL
マテリアライズド・ビューを作成するには、マテリアライズド・ビュー・ログにROWID列を含める必要があります。次の例で、UNION
ALL
マーカーは列1 marker
と2 marker
です。
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW unionall_sales_cust_joins_mv REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS (SELECT c.rowid crid, s.rowid srid, c.cust_id, s.amount_sold, 1 marker FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.cust_last_name = 'Smith') UNION ALL (SELECT c.rowid crid, s.rowid srid, c.cust_id, s.amount_sold, 2 marker FROM sales s, customers c WHERE s.cust_id = c.cust_id AND c.cust_last_name = 'Brown');
例6-6 結合および集計を伴うUNION ALLを使用するマテリアライズド・ビュー
次の例は、結合を含むマテリアライズド・ビューと集計を含むマテリアライズド・ビューのUNION
ALL
を示します。この例で注意することは2つです。対応するSELECT
構文のリストの列のデータ型が一致するようにするには、NULLまたは定数を使用できます。また、UNION
ALL
マーカー列には文字列リテラルを指定できます。この例では、'Year' umarker
、'Quarter' umarker
または'Daily' umarker
です。
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE (amount_sold, time_id) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID, SEQUENCE (time_id, fiscal_year, fiscal_quarter_number, day_number_in_week) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW unionall_sales_mix_mv REFRESH FAST ON DEMAND AS (SELECT 'Year' umarker, NULL, NULL, t.fiscal_year, SUM(s.amount_sold) amt, COUNT(s.amount_sold), COUNT(*) FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.fiscal_year) UNION ALL (SELECT 'Quarter' umarker, NULL, NULL, t.fiscal_quarter_number, SUM(s.amount_sold) amt, COUNT(s.amount_sold), COUNT(*) FROM sales s, times t WHERE s.time_id = t.time_id and t.fiscal_year = 2001 GROUP BY t.fiscal_quarter_number) UNION ALL (SELECT 'Daily' umarker, s.rowid rid, t.rowid rid2, t.day_number_in_week, s.amount_sold amt, 1, 1 FROM sales s, times t WHERE s.time_id = t.time_id AND t.time_id between '01-Jan-01' AND '01-Dec-31');
6.3 マテリアライズド・ビューとモデルについて
マテリアライズド・ビューでは、SQLでの配列ベースの計算を可能にするモデルを使用できます。MODEL
句による計算は高コストになる場合があるので、モデル計算用とSELECT
... GROUP
BY
問合せ用の2つのマテリアライズド・ビューを使用した方がよい場合もあります。たとえば、1つの長いマテリアライズド・ビューを使用するかわりに、次のような2つのマテリアライズド・ビューを作成できます。
CREATE MATERIALIZED VIEW my_groupby_mv REFRESH FAST ENABLE QUERY REWRITE AS SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt, COUNT(*) cntstr FROM sales, times, customers, countries, products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_id GROUP BY country_name, prod_name, calendar_year; CREATE MATERIALIZED VIEW my_model_mv ENABLE QUERY REWRITE AS SELECT country, prod, year, sale, cnt FROM my_groupby_mv MODEL PARTITION BY(country) DIMENSION BY(prod, year) MEASURES(sale s) IGNORE NAV (s['Shorts', 2000] = 0.2 * AVG(s)[CV(), year BETWEEN 1996 AND 1999], s['Kids Pajama', 2000] = 0.5 * AVG(s)[CV(), year BETWEEN 1995 AND 1999], s['Boys Pajama', 2000] = 0.6 * AVG(s)[CV(), year BETWEEN 1994 AND 1999], ... <hundreds of other update rules>);
2つのマテリアライズド・ビューを使用することにより、マテリアライズド・ビューmy_groupby_mv
を段階的にメンテナンスできるようになります。マテリアライズド・ビューmy_model_mv
は、より少量のデータセットに基づいているので(my_groupby_mv
をベースに作成されているため)、完全リフレッシュでメンテナンスできます。
モデルを含むマテリアライズド・ビューでは、完全リフレッシュまたはPCTリフレッシュのみを使用できます。また、テキストの部分一致のクエリー・リライトのみを使用できます。
関連項目:
モデル計算の詳細は、モデリングのSQLを参照してください。
6.4 マテリアライズド・ビューのセキュリティ問題について
独自のスキーマにマテリアライズド・ビューを作成するには、CREATE
MATERIALIZED
VIEW
権限と、別のスキーマにある参照先の表に対するSELECT
またはREAD
権限が必要です。別のスキーマにマテリアライズド・ビューを作成するには、CREATE
ANY
MATERIALIZED
VIEW
権限が必要です。また、参照先の表が別のスキーマにある場合、マテリアライズド・ビューの所有者には、その表に対するSELECT
またはREAD
権限が必要です。さらに、独自のスキーマに含まれない表を参照するマテリアライズド・ビューでクエリー・リライトを有効にするには、GLOBAL
QUERY
REWRITE
権限、または独自のスキーマに含まれない各表に対するQUERY
REWRITE
オブジェクト権限が必要です。
マテリアライズド・ビューがビルトインされたコンテナ上にあり、作成者が所有者とは異なる場合は、コンテナ表に対するREAD WITH GRANT
またはSELECT
WITH
GRANT
権限が必要です。
マテリアライズド・ビューを作成するときに、必要なすべての権限が付与されていると考えられても、権限エラーが継続して発生する場合は、権限が明示的に付与されているのではなく、ロールから権限を継承しようとしている可能性があります。参照される表がそれぞれ異なるスキーマにある場合、マテリアライズド・ビューの所有者には、これらの表へのSELECT
またはREAD
権限が明示的に付与されている必要があります。
ON
COMMIT
REFRESH
を指定してマテリアライズド・ビューを作成する場合に、定義問合せにある表が所有者のスキーマに含まれないときは、マテリアライズド・ビューの所有者には追加の権限が必要です。その場合、所有者には、ON
COMMIT
REFRESH
システム権限、または所有者のスキーマに含まれない各表へのON
COMMIT
REFRESH
オブジェクト権限が必要です。
6.4.1 仮想プライベート・データベース(VPD)を含むマテリアライズド・ビューの問合せ
セキュリティ上の問題はありますが、マテリアライズド・ビューを直接的に問い合せている場合、そのマテリアライズド・ビューは、たまたまマテリアライズド・ビュー化しているビューとして機能します。ビューまたはマテリアライズド・ビューを作成する際、その所有者は、作成しているビューまたはマテリアライズド・ビューの基礎となるベース関係にアクセスするための権限を所有している必要があります。この権限を持つ所有者は、他のユーザーがアクセス可能なビューまたはマテリアライズド・ビューを公開できます(そのビューまたはマテリアライズド・ビューへのアクセス権限がユーザーに付与されている場合)。
仮想プライベート・データベースを含むマテリアライズド・ビューを使用する場合も同様です。マテリアライズド・ビューを作成する際、そのマテリアライズド・ビューの所有者には、マテリアライズド・ビューのベース関係に対して有効になっているVPDポリシーが存在しないようにする必要があります。VPDポリシーが存在する場合、マテリアライズド・ビューを作成するときにUSING TRUSTED CONSTRAINTS
句を使用する必要があります。マテリアライズド・ビューの所有者は、新しいマテリアライズド・ビューにVPDポリシーを設定できます。マテリアライズド・ビューにアクセスするユーザーは、そのマテリアライズド・ビューに設定されているVPDポリシーの対象になります。ただし、これに加えて、マテリアライズド・ビューの基礎となるベース関係のVPDポリシーの対象になることはありません。基礎となるベース関係のセキュリティ処理は、マテリアライズド・ビューの所有者に対して行われます。
この項では、次の項目について説明します。
6.4.1.1 仮想プライベート・データベースを含むクエリー・リライトの使用
クエリー・リライトを使用してマテリアライズド・ビューにアクセスする場合、マテリアライズド・ビューは索引のようなアクセス構造体として機能します。つまり、この方法でアクセスされるマテリアライズド・ビューのセキュリティ処理は索引のセキュリティ処理とほとんど同じであり、すべてのセキュリティ・チェックは問合せで指定されている関係に対して実行されるということです。索引やマテリアライズド・ビューを使用するのは、データへのアクセス速度を向上させるためであり、追加のセキュリティ・チェックを提供するためではありません。索引やマテリアライズド・ビューの存在によって、セキュリティ・チェックが追加されることはありません。
これは、VPDが存在する状態でクエリー・リライトを使用してマテリアライズド・ビューにアクセスする場合にも当てはまります。この問合せは、問合せの中で指定されている関係に設定されたすべてのVPDポリシーの対象になります。クエリー・リライトは、ディテール関係にアクセスするのではなくマテリアライズド・ビューを使用するように問合せをリライトしますが、これは、リライトが行われなかったとしてもまったく同じ行が戻されると保証できる場合にかぎられます。つまり、クエリー・リライトは、問合せの中で指定されている関係に対するすべてのVPDポリシーをそのまま尊重する必要があるということです。ただし、マテリアライズド・ビュー自体に対するVPDポリシーは、クエリー・リライトを使用してそのマテリアライズド・ビューにアクセスする場合には影響を及ぼしません。これは、問合せの中で指定されている関係に対するVPDポリシーによって、データはすでに保護されているためです。
6.4.1.2 マテリアライズド・ビューおよび仮想プライベート・データベースに関する制限
アクティブなVPDポリシーを持つ関係が問合せに含まれている場合、クエリー・リライトは、テキストの完全一致モードおよび部分一致モードを使用しません。ただし、一般的なリライト手法は使用します。これは、VPDが、VPDポリシーに影響を与えるように問合せを透過的に変換するためです。VPDポリシーを伴う問合せに対してクエリー・リライトがテキスト一致の変換を行うとすると、それはVPDポリシーを否定することになります。
また、マテリアライズド・ビューを作成またはリフレッシュする際、そのマテリアライズド・ビューの所有者は、マテリアライズド・ビューのベース関係に対して有効になっているアクティブなVPDポリシーが存在しないようにする必要があります。このようにしない場合、エラーが戻されます。マテリアライズド・ビューの所有者は、このようなVPDポリシーが存在しないようにするか、ポリシーがNULL
を戻すようにする必要があります。これは、VPDがマテリアライズド・ビューの定義問合せを透過的に変更して、マテリアライズド・ビューに格納されている行セットと、マテリアライズド・ビュー定義で指定されている行セットが一致しないようにしてしまうためです。
この制限を回避しながら、必要なVPDが指定された行のサブセットを含むマテリアライズド・ビューを作成する方法の1つは、マテリアライズド・ビューのディテール関係に対するアクティブなVPDポリシーを持たないユーザー・アカウントで、マテリアライズド・ビューを作成することです。また、マテリアライズド・ビューのWHERE
句に、VPDポリシーの効果を盛り込んだ条件を指定することもできます。クエリー・リライトは、そのVPDポリシーを含む問合せをリライトする際、問合せに含まれるVPD生成の条件と、マテリアライズド・ビューの作成時に直接指定された条件とを照合します。
6.5 マテリアライズド・ビューの無効化
マテリアライズド・ビューに関連する依存性は、正しい操作が保証されるように、自動的にメンテナンスされます。あるマテリアライズド・ビューが作成されると、マテリアライズド・ビューはその定義で参照したディテール表に依存します。マテリアライズド・ビューの依存する表に対してINSERT
、DELETE
、UPDATE
などのDML操作またはDDL操作が行われると、そのマテリアライズド・ビューは無効になります。マテリアライズド・ビューを再検証するには、ALTER
MATERIALIZED
VIEW
COMPILE
文を使用します。
マテリアライズド・ビューは、参照されたときに自動的に再検証されます。多くの場合、マテリアライズド・ビューは、正常および透過的に再検証されます。ただし、マテリアライズド・ビューが参照している表の列が削除された場合、またはクエリー・リライト権限を持っていなかったマテリアライズド・ビューの所有者に新たに権限が付与された場合は、次の文を使用してマテリアライズド・ビューを再検証する必要があります。
ALTER MATERIALIZED VIEW mview_name COMPILE;
マテリアライズド・ビューの状態は、データ・ディクショナリ・ビューUSER_MVIEWS
またはALL_MVIEWS
を問い合せることでチェックできます。STALENESS
列に、そのマテリアライズド・ビューが使用可能かどうかを示すFRESH
、STALE
、UNUSABLE
、UNKNOWN
、UNDEFINED
またはNEEDS_COMPILE
のいずれかの値が示されます。状態は自動的にメンテナンスされます。ただし、マテリアライズド・ビューのSTALENESS列がNEEDS_COMPILE
とマークされている場合は、ALTER
MATERIALIZED
VIEW
... COMPILE
文を発行してマテリアライズド・ビューを検証し、正しい失効状態にできます。マテリアライズド・ビューの状態がUNUSABLE
の場合は、完全リフレッシュを実行して、マテリアライズド・ビューの状態をFRESH
に戻す必要があります。リフレッシュしない事前作成表を基にしたマテリアライズド・ビューについては、いったん削除した後、再度作成する必要があります。リモート・マテリアライズド・ビューの失効は追跡されません。そのため、リモート・マテリアライズド・ビューをリライトに使用する場合、これらのビューは信頼できるとみなされます。
6.6 マテリアライズド・ビューの変更
-
リフレッシュ・オプション(
FAST/FORCE/COMPLETE/NEVER
)の変更 -
リフレッシュ・モード(
ON
COMMIT/ON
DEMAND
)の変更 -
再コンパイルによる変更
-
クエリー・リライトに対する使用可能/使用禁止
-
CONSIDER FRESH句による変更
-
パーティションのメンテナンス操作
-
問合せ時計算の有効化
この他の変更は、すべてマテリアライズド・ビューを削除し再作成することで可能になります。変更操作の成功は、変更の要件が満たされているかどうかによって決まります。たとえば、マテリアライズド・ビュー・ログがすべての実表に存在する場合、高速リフレッシュは成功します。
マテリアライズド・ビューが無効化されている場合は、ALTER
MATERIALIZED
VIEW
文のCOMPILE
句を使用できます。このコンパイル処理は高速であり、マテリアライズド・ビューがクエリー・リライトに再使用できるようになります。
関連項目:
-
ALTER
MATERIALIZED
VIEW
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
6.7 リアルタイムのマテリアライズド・ビューの使用
リアルタイムのマテリアライズド・ビューでは、マテリアライズド・ビューが失効とマークされていても、ユーザー問合せに対して最新のデータが提供されます。
6.7.1 リアルタイムのマテリアライズド・ビューの概要
リアルタイムのマテリアライズド・ビューはマテリアライズド・ビューの一種で、データが変更されたためにマテリアライズド・ビューがその実表と同期されていなくても、ユーザー問合せに対して最新のデータを提供します。
SQLセッションが失効許可モードに設定されている場合を除き、失効とマークされているマテリアライズド・ビューをクエリー・リライトで使用することはできません。通常、リアルタイムのデータを必要とする組織では、マテリアライズド・ビューを更新して、実表に加えられた変更を確実に反映するために、ON COMMIT
リフレッシュ・モードを使用します。ただし、実表に対するDMLの変更が大規模で、非常に頻繁に行われる場合、このモードではリソースの競合が発生し、リフレッシュのパフォーマンスが低下することがあります。リアルタイムのマテリアライズド・ビューは、データをその場で再計算することにより、失効したマテリアライズド・ビューから最新のデータを取得するための軽量のソリューションを提供します。
リアルタイムのマテリアライズド・ビューでは、ログ・ベースやPCTベースのリフレッシュなど、使用可能な任意のホーム外リフレッシュ方法を使用できます。これらはオンデマンド・リフレッシュまたはスケジュールされた自動リフレッシュで使用できますが、ON COMMIT
句を使用して指定された自動リフレッシュで使用することはできません。
リアルタイムのマテリアライズド・ビューの利点
-
マテリアライズド・ビューの可用性が向上します。
-
失効している可能性があるマテリアライズド・ビューにアクセスするユーザー問合せに対して最新のデータを提供します。
リアルタイムのマテリアライズド・ビューの仕組み
リアルタイムのマテリアライズド・ビューは、問合せ時計算と呼ばれる手法を使用して、失効したマテリアライズド・ビューに最新のデータを提供します。問合せがリアルタイムのマテリアライズド・ビューにアクセスすると、Oracle Databaseではまず、リアルタイムのマテリアライズド・ビューが失効とマークされているかどうかが確認されます。失効していなければ、リアルタイムのマテリアライズド・ビューをそのまま使用して、必要なデータが提供されます。リアルタイムのマテリアライズド・ビューが失効とマークされている場合は、問合せ時計算手法を使用して最新のデータが生成され、適切な問合せ結果が返されます。
リアルタイムのマテリアライズド・ビューは、失効したマテリアライズド・ビューに最新のデータを提供する際にログ・ベースのリフレッシュに似た手法を使用します。変更ログに記録された変更内容と既存のデータを組み合せて、最新のデータを取得します。ただし、ログ・ベースのリフレッシュとは異なり、リアルタイムのマテリアライズド・ビューは、リアルタイムのマテリアライズド・ビュー内のデータを更新するためにマテリアライズド・ビュー・ログを使用しません。かわりに、失効したリアルタイムのマテリアライズド・ビューに問合せがアクセスすると、問合せ時計算を使用して再計算されたデータを直接使用して、問合せに応答します。
リアルタイムのマテリアライズド・ビューを作成するには、マテリアライズド・ビューの定義でON QUERY COMPUTATION
句を使用します。
6.7.1.1 リアルタイムのマテリアライズド・ビューの使用に関する制限
リアルタイムのマテリアライズド・ビューを使用する際には、一定の制限が適用されます。
-
次の場合、リアルタイムのマテリアライズド・ビューを使用することはできません。
-
実表に作成された1つ以上のマテリアライズド・ビュー・ログが使用できないか、存在しない。
-
変更シナリオでホーム外のログ・ベースまたはPCTリフレッシュを実行できない。
-
ON COMMIT
句を使用して自動リフレッシュが指定されている。
-
-
リアルタイムのマテリアライズド・ビューが、1つ以上のベース・マテリアライズド・ビューの上位に定義されているネステッド・マテリアライズド・ビューである場合、すべてのベース・マテリアライズド・ビューが最新である場合にのみ、クエリー・リライトが発生します。1つ以上のベース・マテリアライズド・ビューが失効している場合、クエリー・リライトは、このリアルタイムのマテリアライズド・ビューを使用して実行されません。
リアルタイムのマテリアライズド・ビューに直接アクセスする問合せのカーソルは共有されません。
6.7.1.2 リアルタイムのマテリアライズド・ビューへのアクセスについて
マテリアライズド・ビューの場合と同様に、リアルタイムのマテリアライズド・ビューに保存されているデータにアクセスするには、複数の方法があります。
リアルタイムのマテリアライズド・ビューに保存されているデータには、次のいずれかの方法でアクセスできます。
-
クエリー・リライト
リアルタイムのマテリアライズド・ビューの定義に似たユーザー問合せは、リアルタイムのマテリアライズド・ビューを使用するようにリライトされます。
-
リアルタイムのマテリアライズド・ビューの直接アクセス
ユーザー問合せは、名前を使用して、リアルタイムのマテリアライズド・ビューを直接参照します。
どちらのシナリオでも、リアルタイムのマテリアライズド・ビューの内容に失効データとしてアクセスしたり、適切な結果の問合せ時計算をトリガーできます。問合せ時計算がトリガーされるかどうかは、環境と実際のSQL文によって決まります。
EXPLAIN PLAN
文の出力には、特定のユーザー問合せで問合せ時計算が使用されたかどうかを示すメッセージが表示されます。
6.7.2 リアルタイムのマテリアライズド・ビューの作成
リアルタイムのマテリアライズド・ビューを作成するには、CREATE MATERIALIZED VIEW
文でON QUERY COMPUTATION
句を使用します。
リアルタイムのマテリアライズド・ビューは、すべての変更シナリオにおいて問合せ時計算に適用できるとはかぎらなくても作成可能です。リアルタイムのマテリアライズド・ビューを作成するための最低限の要件は、INSERT
操作のホーム外リフレッシュをサポートしていることです。複合DML操作など、その他の変更シナリオが発生した場合、すべてのタイプのリアルタイム・マテリアライズド・ビューで問合せ時計算を実行できるとはかぎりません。
リアルタイムのマテリアライズド・ビューでは、ログ・ベースのホーム外リフレッシュ・メカニズム(PCTリフレッシュを含む)を使用する必要があります。リアルタイムのマテリアライズド・ビューでON COMMIT
リフレッシュ・モードを使用することはできません。
リアルタイムのマテリアライズド・ビューを作成するには:
- リアルタイムのマテリアライズド・ビューのすべての実表にマテリアライズド・ビュー・ログが存在することを確認します。
- リアルタイムのマテリアライズド・ビューの基になるすべての表についてマテリアライズド・ビュー・ログを作成します。
CREATE MATERIALIZED VIEW
文でENABLE ON QUERY COMPUTATION
句を指定して、リアルタイムのマテリアライズド・ビューを作成します。
例6-7 リアルタイムのマテリアライズド・ビューの作成
この例では、SH
スキーマ内のSALES
およびPRODUCTS
表から集計されたデータに基づくSUM_SALES_RTMV
というリアルタイムのマテリアライズド・ビューを作成します。リアルタイムのマテリアライズド・ビューを作成する前に、必要な前提条件を満たしていることを確認してください。
-
実表
SALES
およびPRODUCTS
にマテリアライズド・ビュー・ログを作成します。次のコマンドでは、
SALES
表にマテリアライズド・ビュー・ログが作成されます。CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
次のコマンドでは、
PRODUCTS
表にマテリアライズド・ビュー・ログが作成されます。CREATE MATERIALIZED VIEW LOG ON products WITH ROWID (prod_id, prod_name, prod_category, prod_subcategory) INCLUDING NEW VALUES;
-
CREATE MATERIALIZED VIEW
文でON QUERY COMPUTATION
句を指定して、リアルタイムのマテリアライズド・ビューを作成します。このリアルタイムのマテリアライズド・ビューでは高速リフレッシュ方法が使用され、ENABLE QUERY REWRITE
句は、クエリー・リライトが有効である必要があることを示しています。CREATE MATERIALIZED VIEW sum_sales_rtmv REFRESH FAST ON DEMAND ENABLE QUERY REWRITE ENABLE ON QUERY COMPUTATION AS SELECT prod_name, SUM(quantity_sold) AS sum_qty, COUNT(quantity_sold) AS cnt_qty, SUM(amount_sold) AS sum_amt, COUNT(amount_sold) AS cnt_amt, COUNT(*) AS cnt_star FROM sales, products WHERE sales.prod_id = products.prod_id GROUP BY prod_name;
SUM_SALES_RTMV
リアルタイム・マテリアライズド・ビューを作成した後、次の問合せを実行するとします。
SELECT prod_name, SUM(quantity_sold), SUM(amount_sold)
FROM sales, products
WHERE sales.prod_id = products.prod_id
GROUP BY prod_name;
SUM_SALES_RTMV
が失効していない場合、このリアルタイムのマテリアライズド・ビューに保存されているデータを使用して、問合せ結果が返されます。ただし、SUM_SALES_RTMV
が失効しており、問合せをリライトして問合せ時計算でこのマテリアライズド・ビューを使用するコストが実表アクセスより低い場合は、SALES
およびPRODUCTS
表のマテリアライズド・ビュー・ログ内のデルタ変更をリアルタイムのマテリアライズド・ビューSUM_SALES_RTMV
内のデータと組み合せることにより、問合せに応答します。
6.7.3 リアルタイムのマテリアライズド・ビューへの既存のマテリアライズド・ビューの変換
リアルタイムのマテリアライズド・ビューの前提条件を満たしている場合、定義を変更し、問合せ時計算を有効にすることにより、既存のマテリアライズド・ビューをリアルタイムのマテリアライズド・ビューに変換できます。
マテリアライズド・ビューをリアルタイムのマテリアライズド・ビューに変換するには:
- マテリアライズド・ビューの定義を変更し、
ALTER MATERIALIZED VIEW
文でON QUERY COMPUTATION
句を使用して問合せ時計算を有効にします。
リアルタイムのマテリアライズド・ビューを通常のマテリアライズド・ビューに変換するには、ALTER MATERIALIZED VIEW
文でDISABLE ON QUERY COMPUTATION
句を使用して問合せ時計算を無効にします。
例6-8 リアルタイムのマテリアライズド・ビューへのマテリアライズド・ビューの変換
マテリアライズド・ビューSALES_RTMV
は、SALES
、TIMES
およびPRODUCTS
の各表に基づいており、高速リフレッシュを使用します。3つの実表すべてにマテリアライズド・ビュー・ログが存在します。このマテリアライズド・ビューを変更し、リアルタイムのマテリアライズド・ビューに変換することにします。
-
マテリアライズド・ビューの定義を変更し、
ON QUERY COMPUTATION
句を指定してリアルタイムのマテリアライズド・ビューに変換します。ALTER MATERIALIZED VIEW sales_rtmv ENABLE ON QUERY COMPUTATION;
-
DBA_MVIEWS
ビューに問い合せて、SALES_RTMV
で問合せ時計算が有効になっているかどうかを確認します。SELECT mview_name, on_query_computation
FROM dba_mviews WHERE mview_name = 'SALES_RTMV';
6.7.4 リアルタイムのマテリアライズド・ビューを使用するためのクエリー・リライトの有効化
クエリー・リライト・メカニズムによりユーザー問合せをリライトしてリアルタイムのマテリアライズド・ビューを使用するには、リアルタイムのマテリアライズド・ビューでクエリー・リライトを有効にする必要があります。
リアルタイムのマテリアライズド・ビューの作成時にクエリー・リライトを有効にすることも、リアルタイムのマテリアライズド・ビューの定義を変更することにより、後から有効にすることもできます。クエリー・リライトを有効にするには、ENABLE QUERY REWRITE
句を使用します。
既存のリアルタイムのマテリアライズド・ビューでクエリー・リライトを有効にするには:
ALTER MATERIALIZED VIEW
コマンドを実行し、ENABLE QUERY REWRITE
句を指定します。
例6-9 リアルタイムのマテリアライズド・ビューでのクエリー・リライトの有効化
リアルタイムのマテリアライズド・ビューmy_rtmv
は、高速リフレッシュ・メカニズムを使用します。このリアルタイムのマテリアライズド・ビューの定義を変更し、クエリー・リライト・メカニズムで問合せをリライトする際に、このリアルタイムのマテリアライズド・ビューを考慮するように指定することにします。
次のコマンドでは、my_rtmv
でクエリー・リライトが有効化されます。
ALTER MATERIALIZED VIEW my_rtmv ENABLE QUERY REWRITE;
6.7.5 クエリー・リライト時におけるリアルタイムのマテリアライズド・ビューの使用
リアルタイムのマテリアライズド・ビューでクエリー・リライトが有効になっている場合、リアルタイムのマテリアライズド・ビューが失効していても、クエリー・リライトでは、リアルタイムのマテリアライズド・ビューを使用してユーザー問合せに対して結果を提供できます。ネストされたリアルタイムのマテリアライズド・ビューがクエリー・リライトの対象となるのは、そのベースとなるリアルタイムのマテリアライズド・ビューがすべて最新である場合のみです。
ユーザー問合せが実行されると、クエリー・リライトはまず、必要なデータを提供するために最新のマテリアライズド・ビューを使用できるかどうかを確認します。適切なマテリアライズド・ビューがない場合、クエリー・リライトは、ユーザー問合せをリライトするために使用できるリアルタイムのマテリアライズド・ビューを探します。リアルタイムのマテリアライズド・ビューでは最新のデータを計算する際になんらかのオーバーヘッドが発生するため、リアルタイムのマテリアライズド・ビューより最新のマテリアライズド・ビューが優先されます。次に、コスト・ベースのオプティマイザによって、問合せ時計算を使用するSQL問合せのコストが特定され、そのユーザー問合せに応答するためにリアルタイムのマテリアライズド・ビューを使用するかどうかが決定されます。
現在のSQLセッションのQUERY_REWRITE_INTEGRITY
モードがSTALE_TOLERATED
に設定されている場合、クエリー・リライトの際に問合せ時計算は使用されません。STALE_TOLERATED
リライト・モードは、問合せに対応するために最新の結果が必要でないため、問合せ時計算が不要であることを示します。
クエリー・リライトでリアルタイムのマテリアライズド・ビューを使用するには:
例6-10 クエリー・リライト時におけるリアルタイムのマテリアライズド・ビューの使用
この例では、クエリー・リライトを有効にしてリアルタイムのマテリアライズド・ビューを作成し、ユーザー問合せに対してデータを提供するために、それがクエリー・リライトで使用されたことを確認します。
-
作成するリアルタイムのマテリアライズド・ビューの実表である
SALES
表にマテリアライズド・ビュー・ログを作成します。 -
クエリー・リライトを有効にして、リアルタイムのマテリアライズド・ビュー
mav_sum_sales
を作成します。CREATE MATERIALIZED VIEW mav_sum_sales REFRESH FAST ON DEMAND ENABLE ON QUERY COMPUTATION ENABLE QUERY REWRITE AS SELECT prod_id, sum(quantity_sold) as sum_qty, count(quantity_sold) as cnt_qty, sum(amount_sold) sum_amt, count(amount_sold) cnt_amt, count(*) as cnt_star FROM sales GROUP BY prod_id;
-
次の問合せを実行します。
SELECT prod_id, sum(quantity_sold), sum(amount_sold) FROM sales WHERE prod_id < 1000 GROUP BY prod_id;
この問合せが、リアルタイムのマテリアライズド・ビュー
mav_sum_sales
を定義するために使用されたものに似ている点に注目してください。この問合せに似た定義を持つ他のマテリアライズド・ビューが存在しないため、クエリー・リライトでは、mav_sum_sales
リアルタイム・マテリアライズド・ビューを使用して問合せ結果を特定できます。クエリー・リライトが行われたことを確認するには、SQLカーソル・キャッシュをチェックするか(たとえばDBMS_XPLAN
を使用)、SQLモニターを使用するか、またはEXPLAIN PLAN
を使用します。mav_sum_sales
を使用する、内部でリライトされた問合せは、次のような文になります。SELECT prod_id, sum_qty, sum_amt FROM mav_sum_sales WHERE prod_id < 1000;
-
問合せ結果を提供するためにリアルタイムのマテリアライズド・ビューが使用されたことを確認します。
EXPLAIN PLAN
文を使用して、問合せの実行計画を表示します。次の実行計画は、リアルタイムのマテリアライズド・ビューへの直接アクセスを示しています。マテリアライズド・ビューが失効している場合、実行計画はさらに複雑になり、未処理のDML操作に応じて、他のオブジェクト(マテリアライズド・ビュー・ログなど)へのアクセスも含まれます。
EXPLAIN PLAN for SELECT prod_id, sum(quantity_sold), sum(amount_sold) FROM sales WHERE prod_id < 1000 GROUP BY prod_id; SELECT plan_table_output FROM table(dbms_xplan.display('plan_table',null,'serial')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 13616844 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 92 | 3588 | 3 (0) | 00:00:01 | | *1 | MAT_VIEW ACCESS FULL | MAV_SUM_SALES | 92 | 3588 | 3 (0) | 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("PROD_ID"<1000) Note ----- - dynamic statistics used: dynamic sampling (level=2) 17 rows selected.
6.7.6 直接問合せアクセスでのリアルタイムのマテリアライズド・ビューの使用
リアルタイムのマテリアライズド・ビューの名前を問合せで参照することにより、リアルタイムのマテリアライズド・ビューに直接アクセスできます。
ユーザー問合せで指定されたリアルタイムのマテリアライズド・ビューが最新である場合、必要なデータがリアルタイムのマテリアライズド・ビューから直接フェッチされます。リアルタイムのマテリアライズド・ビューが失効している場合は、FRESH_MV
ヒントを使用して問合せ時計算を実行し、最新のデータを取得する必要があります。Oracle Databaseでは、ユーザー問合せで直接アクセスされるリアルタイムのマテリアライズド・ビューについては問合せ時計算が自動的に実行されることはありません。
リアルタイムのマテリアライズド・ビューに直接アクセスする際に、失効したリアルタイムのマテリアライズド・ビューから最新のデータを取得するには:
- ユーザー問合せで
FRESH_MV
ヒントを使用して、問合せ時計算を実行する必要があることを示します。
例6-11 リアルタイムのマテリアライズド・ビューの作成と問合せでの使用
この例では、SALES_NEW
表に基づくリアルタイムのマテリアライズド・ビューMY_RTMV
を作成します。SALES_NEW
表は、SH.SALES
表のコピーとして作成されています。リアルタイムのマテリアライズド・ビューを作成した後、実表に1行を挿入します。次に、ユーザー問合せでマテリアライズド・ビュー名を使用することにより、fresh_mv
ヒントを使用してリアルタイムのマテリアライズド・ビューから最新のデータにアクセスします。
-
実表
sales_new
にマテリアライズド・ビュー・ログを作成します。リアルタイムのマテリアライズド・ビューを作成する場合、実表のマテリアライズド・ビュー・ログは必須です。
CREATE MATERIALIZED VIEW LOG on sales_new WITH sequence, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
-
sales_new
を実表とする、my_rtmv
というリアルタイムのマテリアライズド・ビューを作成します。ON QUERY COMPUTATION
句は、リアルタイムのマテリアライズド・ビューを作成することを示しています。指定されているリフレッシュ・モードは、ログ・ベースの高速リフレッシュです。このリアルタイムのマテリアライズド・ビューでは、クエリー・リライトが有効です。CREATE MATERIALIZED VIEW my_rtmv REFRESH FAST ENABLE ON QUERY COMPUTATION ENABLE QUERY REWRITE AS SELECT prod_id, cust_id, channel_id, sum(quantity_sold) sum_q, count(quantity_sold) cnt_q, avg(quantity_sold) avg_q, sum(amount_sold) sum_a, count(amount_sold) cnt_a, avg(amount_sold) avg_a FROM sales_new GROUP BY prod_id, cust_id, channel_id;
-
リアルタイムのマテリアライズド・ビューの実表である
sales_new
に1行を挿入し、この変更をコミットします。INSERT INTO sales_new (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (116,100450, sysdate,9,9999,10,350); COMMIT;
-
リアルタイムのマテリアライズド・ビューに直接問い合せて、前のステップでリアルタイムのマテリアライズド・ビューの実表に追加された行のデータを表示します。
SELECT * from my_rtmv WHERE prod_id = 116 AND cust_id=100450 AND channel_id = 9; PROD_ID CUST_ID CHANNEL_ID SUM_Q CNT_Q AVG_Q SUM_A CNT_A AVG_A ------- ------- ---------- ----- ----- ----- ----- ----- ------ 116 100450 9 1 1 1 11.99 1 11.99
問合せ結果には、このデータの更新された値が表示されていません。これは、リアルタイムのマテリアライズド・ビューがまだリフレッシュされておらず、その実表に加えられた変更内容が反映されていないためです。
-
リアルタイムのマテリアライズド・ビューに問い合せる際に
FRESH_MV
ヒントを指定して、実表で更新された行を表示します。SELECT /*+ fresh_mv */ * FROM my_rtmv WHERE prod_id = 116 AND cust_id=100450 AND channel_id = 9; PROD_ID CUST_ID CHANNEL_ID SUM_Q CNT_Q AVG_Q SUM_A CNT_A AVG_A ------- ------- ---------- ----- ----- ----- ----- ----- ------ 116 100450 9 11 2 5.5 361.99 2 180.995
ここでは、更新された行が表示されています。これは、
FRESH_MV
ヒントによって、リアルタイムのマテリアライズド・ビューの問合せ時計算がトリガーされ、最新のデータが再計算されるためです。
6.7.7 リアルタイムのマテリアライズド・ビューのリスト
データ・ディクショナリ・ビューALL_MVIEWS
、DBA_MVIEWS
およびUSER_MVIEWS
のON_QUERY_COMPUTATION
列は、マテリアライズド・ビューがリアルタイムのマテリアライズド・ビューであるかどうかを示します。
ON_QUERY_COMPUTATION
列の値Yは、リアルタイムのマテリアライズド・ビューを示します。
USER_MVIEWS
ビューに問い合せ、ON_QUERY_COMPUTATION
列がYに設定されているマテリアライズド・ビューの詳細を表示します。
例6-12 現在のユーザーのスキーマ内のリアルタイムのマテリアライズド・ビューのリスト
SELECT owner, mview_name, rewrite_enabled, staleness
FROM user_mviews
WHERE on_query_computation = 'Y';
OWNER MVIEW_NAME REWRITE_ENABLED STALENESS
------ ------------ ------------------- ------------
SH SALES_RTMV N FRESH
SH MAV_SUM_SALES Y FRESH
SH MY_SUM_SALES_RTMV Y FRESH
SH NEW_SALES_RTMV Y STALE
6.7.8 リアルタイムのマテリアライズド・ビューのパフォーマンスの向上
リアルタイムのマテリアライズド・ビューを使用するユーザー問合せのパフォーマンスを向上させるには、特定のガイドラインに従います。
リアルタイムのマテリアライズド・ビューでは、次のガイドラインを使用します。
-
リアルタイムのマテリアライズド・ビューを使用する可能性がある問合せのパフォーマンスを向上させるために、リアルタイムのマテリアライズド・ビューを頻繁にリフレッシュします。
リアルタイムのマテリアライズド・ビューは、実表に対するデルタ変更を既存のマテリアライズド・ビュー・データと組み合せることで動作するため、計算されるデルタ変更が少なければ、問合せの応答時間が短縮されます。未処理のDML操作が多くなると、問合せ時計算が複雑になる(コストも高くなる)可能性があり、実表の直接アクセスの方が効率的になることもあります(クエリー・リライトの場合)。
-
オプティマイザが問合せのコストを正確に特定できるように、実表、リアルタイムのマテリアライズド・ビューおよびマテリアライズド・ビュー・ログの統計を収集します。
クエリー・リライトの場合、コスト・ベースのリライト・メカニズムでは、オプティマイザを使用して、リライトされた問合せを使用する必要があるかどうかが決定されます。オプティマイザは、統計を使用してコストを特定します。