| Oracle® Databaseデータ・ウェアハウス・ガイド 11gリリース2 (11.2) B56309-04 |
|
 前 |
 次 |
この章では、マテリアライズド・ビューの高度な使用方法について説明します。内容は次のとおりです。
データ・ウェアハウスに格納されているデータの量は膨大であるため、パーティション化は、データベースの設計時に非常に有効なオプションです。ファクト表のパーティション化によって、スケーラビリティの向上とシステム管理の簡素化を実現できます。また、効率的に再作成できるローカル索引を定義できるようになります。ファクト表をパーティション化することにより、マテリアライズド・ビューに対するパーティション・チェンジ・トラッキング(PCT)リフレッシュが可能になるので、マテリアライズド・ビューを高速リフレッシュできる可能性も高くなります。マテリアライズド・ビューのパーティション化は、リフレッシュの面でもメリットがあります。リフレッシュ・プロシージャは、より多くの場合にパラレルDMLを使用できるようになり、PCTベースのリフレッシュは、パーティションの切捨てを使用してマテリアライズド・ビューを効率的にメンテナンスできるようになります。パーティション化の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。
最新の状態かどうかの追跡対象を、マテリアライズド・ビュー全体ではなく、より細かく限定でき、それによるメリットが得られます。特定のディテール表のパーティションによる影響を受けるマテリアライズド・ビュー内の行を識別する機能は、パーティション・チェンジ・トラッキングと呼ばれます。1つ以上のディテール表がパーティション化されている場合は、マテリアライズド・ビュー内で、変更されたディテール・パーティションに対応する特定の行を識別できます。これらの行はパーティションが変更されると失効しますが、他のすべての行は最新のままです。
PCTを使用すると、特定のパーティションに対応するマテリアライズド・ビューの行を識別できます。PCTは、ディテール表に対するパーティション・メンテナンス操作後の高速リフレッシュのサポートにも使用されます。たとえば、ディテール表のパーティションが切り捨てられるか削除されると、マテリアライズド・ビュー内で影響を受ける行が識別され、削除されます。
QUERY_REWRITE_INTEGRITY = ENFORCEDまたはTRUSTEDモードのときは、マテリアライズド・ビュー全体を失効とみなすのではなく、マテリアライズド・ビューの行のうち最新のものと失効しているものが識別され、最新状態の行をクエリー・リライトで使用できるようになります。DBA_MVIEW_DETAIL_PARTITIONなどの一部のビューでは、どのパーティションが失効しているかまたは最新なのかが具体的に示されます。マテリアライズド・ビューに結合依存の式があることによって、変更対象の表のパーティション・チェンジ・トラッキングが使用可能になる場合は、部分的に失効しているマテリアライズド・ビューへのリライトは行われません。詳細は、「結合依存の式」を参照してください。
PCTをサポートするには、マテリアライズド・ビューが次の要件を満たしている必要があります。
マテリアライズド・ビューで参照される1つ以上のディテール表が、パーティション化されている必要があります。
パーティション表には、レンジ、リストまたはコンポジットのいずれかのパーティション化を使用する必要があります。
最上位レベルのパーティション・キーは、単一列のみで構成する必要があります。
マテリアライズド・ビューには、ディテール表のパーティション・キー列、パーティション・マーカー、ROWIDまたは結合依存の式のいずれかを含める必要があります。DBMS_MVIEW.PMARKERファンクションの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
GROUP BY句を使用する場合は、パーティション・キー列、パーティション・マーカー、ROWIDまたは結合依存の式をGROUP BY句に指定する必要があります。
分析ウィンドウ関数またはMODEL句を使用する場合は、パーティション・キー列、パーティション・マーカー、ROWIDまたは結合依存の式を、それぞれのPARTITION BY副次句に指定する必要があります。
データ修正の発生が可能なのは、パーティション表のみです。マテリアライズド・ビューに結合依存の式を含む表に対してPCTリフレッシュを実行する際は、いずれの結合依存の表についても、データ修正が発生していてはなりません。
COMPATIBILITY初期化パラメータは9.0.0.0.0以上に設定する必要があります。
ビュー、リモート表または外部結合を参照するマテリアライズド・ビューの場合、PCTはサポートされません。
パーティション・チェンジ・トラッキングは、マテリアライズド・ビューに必要な情報が存在すれば、ベースとなるディテール表のパーティションの各行を、対応するマテリアライズド・ビューの各行に関連付けることができます。そのためには、ディテール表のパーティション・キー列をSELECT構文のリストに含めます。また、GROUP BYを使用する場合は、GROUP BYリストにも含めます。
例として、日次の顧客売上を格納するマテリアライズド・ビューを考えてみます。次の例では、shサンプル・スキーマおよび3つのディテール表(sales、productsおよびtimes)を使用してマテリアライズド・ビューを作成します。sales表はtime_id列でパーティション化されており、products表はprod_id列でパーティション化されています。times表はパーティション表ではありません。
例10-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表およびproducts表の両方でPCTが使用可能になります。これは、2つの表のパーティション化キー列time_idとprod_idがマテリアライズド・ビューに含まれているためです。
結合依存の式とは、パーティション化キーでの等価結合を介して直接的または間接的にパーティション化ディテール表に結合されている表の列で構成され、結合キーのディメンション属性またはディメンション階層の親である式のことです。ディテール表へのパスに存在する一連の表は、結合依存の表と呼ばれます。次の問合せを考えてみます。
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表に結合されているためです。
これによりユーザーは、ディテール表のパーティション化キーよりも上位のレベルでの集計を含むマテリアライズド・ビューを作成できます。次に、月次の顧客売上を格納するマテリアライズド・ビューの例を見てみます。
例10-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も可能になります。
DBMS_MVIEW.PMARKERファンクションは、マテリアライズド・ビューのカーディナリティを大幅に削減するように設計されています(例については、例10-3を参照)。このファンクションは、指定のパーティション表の指定の行についてパーティションを一意に識別するパーティション識別子を戻します。したがって、DBMS_MVIEW.PMARKERファンクションは、SELECT句およびGROUP BY句のパーティション・キー列のかわりに使用されます。
マテリアライズド・ビューでのPL/SQLファンクションの一般的なケースとは異なり、DBMS_MVIEW.PMARKERを使用すると、リライト・モードがQUERY_REWRITE_INTEGRITY = ENFORCEDであっても、そのマテリアライズド・ビューでのリライトは可能です。
PMARKERファンクションの使用例として、指定した年の製品カテゴリ別収益などの典型的な数値の計算を考えてみます。毎月1,000種類の製品が販売されていた場合、マテリアライズド・ビューには12,000行が格納されていることになります。
例10-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構文のリストに含まれているためです。
後続の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がパーティション・キーまたはパーティション・マーカーを使用してディテール表で使用可能になっている場合にのみ、ディテール表への変更発生時にマテリアライズド・ビューの最新部分を識別できます。
次の例に示すように、マテリアライズド・ビューのパーティション化には、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;
次に示すように、マテリアライズド・ビューは、パーティション化された事前作成表に登録できます。
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句を含んでいるため、クエリー・リライトに使用できます。
マテリアライズド・ビューをディテール表のパーティション化キー列または結合依存の式でパーティション化すると、TRUNCATE PARTITION文を使用してリフレッシュの際にマテリアライズド・ビューの1つ以上のパーティションを削除し、後でそのパーティションに新しいデータを再移入するという作業がより効率的になります。Oracle Databaseは、次の条件と「パーティション・チェンジ・トラッキング」で説明したその他の条件が満たされている場合に、パーティションの切捨てを伴うこの種の高速リフレッシュ(PCTリフレッシュと呼ぶ)を使用します。
マテリアライズド・ビューが、ディテール表のパーティション化キー列または結合依存の式でパーティション化されている。
パーティション化キー列または結合式を使用してPCTが使用可能になっている場合、マテリアライズド・ビューはレンジ・パーティション化またはリスト・パーティション化されている必要がある。
PCTリフレッシュがアトミックではない。
データ・ウェアハウスまたはデータ・マートに時間ディメンションが含まれる場合、通常は最も古い情報をアーカイブしてから、その記憶域を新しい情報に再使用する必要があります。これは、ローリング・ウィンドウ・シナリオと呼ばれます。ファクト表またはマテリアライズド・ビューに時間ディメンションが含まれ、時間属性によって水平にパーティション化されている場合、ロールアウトされるデータの量がレンジ・パーティション化の場合と等しいか、または少なくとも整列していれば、ローリング・マテリアライズド・ビューの管理が、高速で管理コストの低いパーティション管理のみに軽減されます。
データ・ウェアハウスにローリング・マテリアライズド・ビューを作成する場合は、パーティション・メンテナンス操作を実行する頻度を決定する必要があります。また、ファクト表およびマテリアライズド・ビューをパーティション化して、古いデータが必要なくなったときに必要なシステム管理によるオーバーヘッドを削減する必要があります。また、頻繁には更新されないパーティションに対してはデータ圧縮の使用も考慮します。
レンジ・パーティション化の使用には、制限はありません。たとえば、時間値およびキー値の両方を使用したコンポジット・パーティション化が、データに対して適切なパーティション・ソリューションとなる場合もあります。
CONSIDER FRESHおよび圧縮の詳細は、第16章「データ・ウェアハウスのメンテナンス」を参照してください。
この項では、分析SQLで使用される概念と、リレーショナル・データベースでこのタイプの問合せを処理する方法を説明します。また、一般的な例を使用してマテリアライズド・ビューを作成する最適な方法を示します。
データ・ウェアハウス環境では一般にデータをスター・スキーマ形式で表示しますが、分析SQL問合せではデータを階層的キューブ形式で表示します。階層的キューブにはそのディメンションそれぞれのロールアップ階層に沿って集計されたデータが含まれ、これらの集計はディメンションをまたがって結合されます。ビジネス・インテリジェンス問合せに必要な典型的な集計の集合が含まれます。
時間。(all times)、year、quarterおよびmonthを含みます。
製品。(all products)、division、brandおよびitemを含みます。
つまり、階層的キューブ内には16個の集計グループがあることになります。これは、時間の4つのレベルと製品の4つのレベルを掛け合せてキューブが生成されるためです。各ディメンションの4つのレベルを表10-1に示します。
| 時間別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年全体の食料雑貨部門の売上合計とこの値を比較する場合があります。
複数の集計グループを含むマテリアライズド・ビューのパフォーマンスがリフレッシュおよびクエリー・リライト向けに最大化されるのは、適切にパーティション化されている場合です。
ローリング・ウィンドウ・シナリオでのPCTリフレッシュには、時間ディメンションのいくつかのレベルにおける最上位レベルでのパーティション化が必要です。また、このマテリアライズド・ビューへのクエリー・リライトでパーティション・プルーニングを行うには、GROUPING_ID列でのパーティション化が必要です。したがって、これらのマテリアライズド・ビューのパーティション化方法として最も効果的なのは、コンポジット・パーティション化((time, GROUPING_ID)列でのレンジ-リスト・パーティション化)ということになります。この方法でマテリアライズド・ビューをパーティション化すると、次のことが可能になります。
PCTリフレッシュ: リフレッシュのパフォーマンスが向上します。
パーティション・プルーニング: 関連する集計グループのみがアクセスされるため、問合せの処理コストが大幅に削減されます。
PCTリフレッシュを使用しない場合は、GROUPING_ID列のみでリスト・パーティション化します。
冗長性の高いデータ(多数の外部キーを持つ表など)を使用するときは、データ圧縮を考慮するようにします。特に、ROLLUP句を使用して作成したマテリアライズド・ビューがこの候補になります。データ圧縮の構文および制限については、『Oracle Database SQL言語リファレンス』を参照してください。圧縮の詳細は、「記憶域および表の圧縮」を参照してください。
Oracle Databaseでは、定義問合せに集合演算子が含まれるマテリアライズド・ビューをサポートしています。集合演算子を含むマテリアライズド・ビューをクエリー・リライト対応として作成できるようになりました。マテリアライズド・ビューは、ON COMMITまたはON DEMANDリフレッシュのいずれかを使用してリフレッシュできます。
定義問合せの最上位レベルにUNION ALL演算子が含まれ、UNION ALL内の各問合せブロックが、集計を含むマテリアライズド・ビューまたは結合のみを含むマテリアライズド・ビューの要件を満たす場合は、高速リフレッシュがサポートされます。さらに、マテリアライズド・ビューには、各問合せブロック内の個別の値を含む定数列(UNION ALLマーカー)を含める必要があります。これは、次の例では、列1 markerと2 markerです。
UNION ALLを使用したマテリアライズド・ビューの高速リフレッシュに関する制限の詳細は、「UNION ALLを含むマテリアライズド・ビューの高速リフレッシュに関する制限」を参照してください。
次に、UNION ALLを含む、高速リフレッシュ可能なマテリアライズド・ビューの作成例を示します。
例10-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');
例10-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');
マテリアライズド・ビューでは、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リフレッシュのみを使用できます。また、テキストの部分一致のクエリー・リライトのみを使用できます。
マテリアライズド・ビューに関連する依存性は、正しい操作が保証されるように、自動的にメンテナンスされます。あるマテリアライズド・ビューが作成されると、マテリアライズド・ビューはその定義で参照したディテール表に依存します。マテリアライズド・ビューの依存する表に対して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に戻す必要があります。リフレッシュしない事前作成表を基にしたマテリアライズド・ビューについては、いったん削除した後、再度作成する必要があります。リモート・マテリアライズド・ビューの失効は追跡されません。そのため、リモート・マテリアライズド・ビューをリライトに使用する場合、これらのビューは信頼できるとみなされます。
独自のスキーマにマテリアライズド・ビューを作成するには、CREATE MATERIALIZED VIEW権限と、別のスキーマにある参照先の表に対するSELECT権限が必要です。別のスキーマにマテリアライズド・ビューを作成するには、CREATE ANY MATERIALIZED VIEW権限が必要です。また、参照先の表が別のスキーマにある場合、マテリアライズド・ビューの所有者には、その表に対するSELECT権限が必要です。さらに、独自のスキーマに含まれない表を参照するマテリアライズド・ビューでクエリー・リライトを有効にするには、GLOBAL QUERY REWRITE権限、または独自のスキーマに含まれない各表に対するQUERY REWRITEオブジェクト権限が必要です。
マテリアライズド・ビューがビルトインされたコンテナ上にあり、作成者が所有者とは異なる場合は、コンテナ表に対するSELECT WITH GRANT権限が必要です。
マテリアライズド・ビューを作成するときに、必要なすべての権限が付与されていると考えられても、権限エラーが継続して発生する場合は、権限が明示的に付与されているのではなく、ロールから権限を継承しようとしている可能性があります。参照される表がそれぞれ異なるスキーマにある場合、マテリアライズド・ビューの所有者には、これらの表へのSELECT権限が明示的に付与されている必要があります。
ON COMMIT REFRESHを指定してマテリアライズド・ビューを作成する場合に、定義問合せにある表が所有者のスキーマに含まれないときは、マテリアライズド・ビューの所有者には追加の権限が必要です。その場合、所有者には、ON COMMIT REFRESHシステム権限、または所有者のスキーマに含まれない各表へのON COMMIT REFRESHオブジェクト権限が必要です。
セキュリティ上の問題はありますが、マテリアライズド・ビューを直接的に問い合せている場合、そのマテリアライズド・ビューは、たまたまマテリアライズド・ビュー化しているビューとして機能します。ビューまたはマテリアライズド・ビューを作成する際、その所有者は、作成しているビューまたはマテリアライズド・ビューの基礎となるベース関係にアクセスするための権限を所有している必要があります。この権限を持つ所有者は、他のユーザーがアクセス可能なビューまたはマテリアライズド・ビューを公開できます(そのビューまたはマテリアライズド・ビューへのアクセス権限がユーザーに付与されている場合)。
仮想プライベート・データベースを含むマテリアライズド・ビューを使用する場合も同様です。マテリアライズド・ビューを作成する際、そのマテリアライズド・ビューの所有者には、マテリアライズド・ビューのベース関係に対して有効になっているVPDポリシーが存在しないようにする必要があります。ただし、マテリアライズド・ビューの所有者が新しいマテリアライズド・ビューにVPDポリシーを設定することは可能です。マテリアライズド・ビューにアクセスするユーザーは、そのマテリアライズド・ビューに設定されているVPDポリシーの対象になります。ただし、これに加えて、マテリアライズド・ビューの基礎となるベース関係のVPDポリシーの対象になることはありません。基礎となるベース関係のセキュリティ処理は、マテリアライズド・ビューの所有者に対して行われます。
クエリー・リライトを使用してマテリアライズド・ビューにアクセスする場合、マテリアライズド・ビューは索引のようなアクセス構造体として機能します。つまり、この方法でアクセスされるマテリアライズド・ビューのセキュリティ処理は索引のセキュリティ処理とほとんど同じであり、すべてのセキュリティ・チェックは問合せで指定されている関係に対して実行されるということです。索引やマテリアライズド・ビューを使用するのは、データへのアクセス速度を向上させるためであり、追加のセキュリティ・チェックを提供するためではありません。索引やマテリアライズド・ビューの存在によって、セキュリティ・チェックが追加されることはありません。
これは、VPDが存在する状態でクエリー・リライトを使用してマテリアライズド・ビューにアクセスする場合にも当てはまります。この問合せは、問合せの中で指定されている関係に設定されたすべてのVPDポリシーの対象になります。クエリー・リライトは、ディテール関係にアクセスするのではなくマテリアライズド・ビューを使用するように問合せをリライトしますが、これは、リライトが行われなかったとしてもまったく同じ行が戻されると保証できる場合にかぎられます。つまり、クエリー・リライトは、問合せの中で指定されている関係に対するすべてのVPDポリシーをそのまま尊重する必要があるということです。ただし、マテリアライズド・ビュー自体に対するVPDポリシーは、クエリー・リライトを使用してそのマテリアライズド・ビューにアクセスする場合には影響を及ぼしません。これは、問合せの中で指定されている関係に対するVPDポリシーによって、データはすでに保護されているためです。
アクティブなVPDポリシーを持つ関係が問合せに含まれている場合、クエリー・リライトは、テキストの完全一致モードおよび部分一致モードを使用しません。ただし、一般的なリライト手法は使用します。これは、VPDが、VPDポリシーに影響を与えるように問合せを透過的に変換するためです。VPDポリシーを伴う問合せに対してクエリー・リライトがテキスト一致の変換を行うとすると、それはVPDポリシーを否定することになります。
また、マテリアライズド・ビューを作成またはリフレッシュする際、そのマテリアライズド・ビューの所有者は、マテリアライズド・ビューのベース関係に対して有効になっているアクティブなVPDポリシーが存在しないようにする必要があります。このようにしない場合、エラーが戻されます。マテリアライズド・ビューの所有者は、このようなVPDポリシーが存在しないようにするか、ポリシーがNULLを戻すようにする必要があります。これは、VPDがマテリアライズド・ビューの定義問合せを透過的に変更して、マテリアライズド・ビューに格納されている行セットと、マテリアライズド・ビュー定義で指定されている行セットが一致しないようにしてしまうためです。
この制限を回避しながら、必要なVPDが指定された行のサブセットを含むマテリアライズド・ビューを作成する方法の1つは、マテリアライズド・ビューのディテール関係に対するアクティブなVPDポリシーを持たないユーザー・アカウントで、マテリアライズド・ビューを作成することです。また、マテリアライズド・ビューのWHERE句に、VPDポリシーの効果を盛り込んだ条件を指定することもできます。クエリー・リライトは、そのVPDポリシーを含む問合せをリライトする際、問合せに含まれるVPD生成の条件と、マテリアライズド・ビューの作成時に直接指定された条件とを照合します。
マテリアライズド・ビューでは、次の6つの変更が可能です。次の操作が可能です。
リフレッシュ・オプション(FAST/FORCE/COMPLETE/NEVER)の変更
リフレッシュ・モード(ON COMMIT/ON DEMAND)の変更
再コンパイルによる変更
クエリー・リライトに対する使用可能/使用禁止
CONSIDER FRESH句による変更
パーティションのメンテナンス操作
この他の変更は、すべてマテリアライズド・ビューを削除し再作成することで可能になります。
マテリアライズド・ビューが無効化されている場合は、ALTER MATERIALIZED VIEW文のCOMPILE句を使用できます。このコンパイル処理は高速であり、マテリアライズド・ビューがクエリー・リライトに再使用できるようになります。
