| Oracle® Databaseデータ・ウェアハウス・ガイド 12c リリース1 (12.1) B71318-06 |
|
 前 |
 次 |
この章では、マテリアライズド・ビューの使用方法について説明します。内容は次のとおりです。
一般的に、データは月、週または日単位で、1つ以上のオンライン・トランザクション処理(OLTP)データベースからデータ・ウェアハウスに送られます。データは通常、データ・ウェアハウスに追加される前に ステージング・ファイルで処理されます。データ・ウェアハウスのサイズは、一般的に、数百GBから数TBの範囲にわたります。通常、データの大半はいくつかの非常に大規模なファクト表に格納されます。
パフォーマンス向上のためにデータ・ウェアハウスで使用されている1つのテクニックに、サマリーの作成があります。これは、特殊なタイプの集計ビューであり、問合せを実行する前に、効率が悪い結合および集計操作を事前に計算し、その結果をデータベース内の表に格納することで、問合せ実行時間を短縮します。たとえば、サマリー表が、地域別および製品別の売上合計を含むように作成できます。
このマニュアルおよびデータ・ウェアハウスに関するマニュアルで言及されているサマリーまたは集計は、マテリアライズド・ビューと呼ばれるスキーマ・オブジェクトを使用して、Oracle Databaseに作成されます。マテリアライズド・ビューは、問合せパフォーマンスの改善、レプリケートされたデータの提供などの多くの役割で使用できます。
データベース管理者は、サマリーと同じ結果を格納するマテリアライズド・ビューを1つ以上作成します。エンド・ユーザーは、表やビューをディテール・データ・レベルで問い合せます。SQL問合せは、Oracleサーバーのクエリー・リライト・メカニズムにより、サマリー・テーブルを使用するように自動的にリライトされます。このメカニズムにより、問合せから結果を戻すための応答時間が短縮されます。データ・ウェアハウス内のマテリアライズド・ビューは、エンド・ユーザーやデータベース・アプリケーションに対して透過的です。
マテリアライズド・ビューは、通常、クエリー・リライト機能を使用してアクセスされますが、エンド・ユーザーまたはデータベース・アプリケーションは、マテリアライズド・ビューに直接アクセスする問合せも作成できます。ただし、マテリアライズド・ビューに変更があるとそれを参照する問合せに影響するため、ユーザーにこれを許可するかどうかについては慎重に検討する必要があります。
この項には次のトピックが含まれます:
データ・ウェアハウスでは、マテリアライズド・ビューを使用して、売上合計などの集計データを事前に計算し格納できます。これらの環境では、マテリアライズド・ビューにサマリー・データが格納されるため、マテリアライズド・ビューは、サマリーとして参照されます。また、マテリアライズド・ビューを使用して、集計の有無にかかわらず、結合を事前に計算できます。マテリアライズド・ビューによって、大規模または重要な問合せの、コストの高い結合や集計によって発生するオーバーヘッドを回避できます。
分散環境では、マテリアライズド・ビューを使用して、分散サイトでデータをレプリケートし、各サイトで競合解消方法を使用して実行された更新を同期できます。複製としてのマテリアライズド・ビューによって、本来はリモート・サイトからアクセスする必要があるデータに、ローカルにアクセスできます。マテリアライズド・ビューは、リモート・データ・マートでも有効です。
関連項目:
『Oracle Database Heterogeneous Connectivityユーザーズ・ガイド』
『Oracle Databaseアドバンスト・レプリケーション』
マテリアライズド・ビューを使用して、クライアントとセントラル・サーバー間で定期的にリフレッシュおよび更新を行い、データのサブセットをセントラル・サーバーからモバイル・クライアントにダウンロードすることもできます。この章では、データ・ウェアハウスでのマテリアライズド・ビューの使用方法を中心に説明しています。
関連項目:
『Oracle Database Heterogeneous Connectivityユーザーズ・ガイド』
『Oracle Databaseアドバンスト・レプリケーション』
マテリアライズド・ビューを使用すると、非常に大規模なデータベースに対する問合せの速度が上がります。大規模データベースへの問合せには、多くの場合、表間の結合またはSUMなどの集計(あるいはその両方)が伴います。これらの操作は、時間および処理能力の点でコストが高くなります。作成するマテリアライズド・ビューのタイプによって、マテリアライズド・ビューのリフレッシュ方法およびクエリー・リライトによる使用方法が決まります。
マテリアライズド・ビューは、問合せを実行する前にコストの高い結合および集計操作をデータベース上で事前に計算し、その結果をデータベースに格納することで、問合せのパフォーマンスを改善します。問合せオプティマイザでは、問合せの要求を満たすのに既存のマテリアライズド・ビューが使用可能かどうか、また必要かどうかが自動的に認識されます。そして、使用可能であれば問合せオプティマイザは、マテリアライズド・ビューを使用するように、問合せを透過的にリライトします。その結果問合せは、ベースとなるディテール表ではなく、マテリアライズド・ビューに対して直接実行されます。一般に、ディテール表ではなくマテリアライズド・ビューを使用するように問合せをリライトすると、応答のパフォーマンスが改善されます。クエリー・リライトがどのように機能するかを図5-1に示します。
クエリー・リライトを使用する場合は、できるだけ多くの問合せを満たすマテリアライズド・ビューを作成します。たとえば、ディテール表またはファクト表に共通して適用される20の問合せがわかっている場合、適切に作成されたマテリアライズド・ビューが5、6個あればこれらの問合せを満たせる場合があります。マテリアライズド・ビュー定義には、任意の数の集計(SUM、COUNT(x)、COUNT(*)、COUNT(DISTINCT x)、AVG、VARIANCE、STDDEV、MINおよびMAX)を含めることができます。また、任意の数の結合を含めることもできます。どのようなマテリアライズド・ビューを作成する必要があるかわからない場合のために、Oracle DatabaseにはSQLアクセス・アドバイザが用意されています。これは、DBMS_ADVISORパッケージに含まれる一連のアドバイザ・プロシージャで、クエリー・リライトのためにマテリアライズド・ビューを設計および評価する場合に有効です。
マテリアライズド・ビューがクエリー・リライトによって使用される場合、そのマテリアライズド・ビューは、基になるディテール表と同じデータベースに格納する必要があります。マテリアライズド・ビューはパーティション化することも、パーティション表にマテリアライズド・ビューを定義することも可能です。また、マテリアライズド・ビューに1つ以上の索引を定義することもできます。
索引とは異なり、SELECT文を使用してマテリアライズド・ビューに直接アクセスできます。ただし、アプリケーションに影響を与えずにSQL文を変更することが難しくなるので、直接的にマテリアライズド・ビューを参照するSQL文は使用しないようにすることをお薦めします。かわりに、問合せがマテリアライズド・ビューを使用するように、クエリー・リライトで透過的にリライトすることをお薦めします。
この章で説明しているのは、データ・ウェアハウスでマテリアライズド・ビューを使用する方法です。マテリアライズド・ビューは、Oracle Replicationでも使用できます。
関連項目:
『Oracle Databaseアドバンスト・レプリケーション』
マテリアライズド・ビューおよびディメンションの定義機能。
すべてのマテリアライズド・ビューに最新データが確実に含まれるようにするリフレッシュ機能。
マテリアライズド・ビューを使用するために問合せを透過的にリライトするクエリー・リライト機能。
作成するべきマテリアライズド・ビュー、パーティションおよび索引を推奨するSQLアクセス・アドバイザ。
マテリアライズド・ビューを高速リフレッシュ可能にする方法、および一般的なクエリー・リライトの使用方法を提示するTUNE_MVIEWパッケージ。
サマリー管理機能を使用する際に、スキーマに関する制限事項はありません。また、この機能を使用すると、データベースまたはアプリケーションを再設計しなくても、いくつかの既存DSSデータベース・アプリケーションのパフォーマンスを改善できます。
図5-2に、ウェアハウス・サイクルでのサマリー管理の使用例を示します。データがウェアハウスのディテール・データに変換、ステージングおよびロードされると、サマリー管理プロセスを起動できます。まず、SQLアクセス・アドバイザを使用して、マテリアライズド・ビューをどのように使用するかを計画します。次に、マテリアライズド・ビューを作成し、クエリー・リライトの方法を設計します。マテリアライズド・ビューの動作に問題がある場合は、TUNE_MVIEWを使用すると、マテリアライズド・ビューを最適化できます。
データ・ウェアハウス設計の初期の段階でサマリー管理プロセスを理解しておくと、後で、パフォーマンスの向上、サマリー管理コストの削減および必要な記憶域の削減という大きなメリットを得ることができます。
データ・ウェアハウスの基本的な用語の定義は、次のとおりです。
ディメンション表とは、企業のビジネス・エンティティを表します。通常、時間、部門、場所、製品などの階層およびカテゴリ情報として表されます。ディメンション表は、参照表とも呼ばれます。
ディメンション表は、通常、時間をかけてゆっくり変化し、定期的に変更されることはありません。長時間実行される意思決定支援問合せで、問合せから戻されるデータをディメンション階層の該当レベルに集計するために使用されます。
階層には、データベースでのビジネスの関係と共通のアクセス・パターンが記述されます。典型的な作業負荷を理解し、ディメンションの分析をすることで、マテリアライズド・ビューを作成できます。詳細は、ディメンションを参照してください。
ファクト表とは、企業のビジネス・トランザクションを表します。
データ・ウェアハウスのほとんどのデータは、少数の非常に大きなファクト表に格納されます。これらの表は、1つ以上の業務系OLTPデータベースからのデータで定期的に更新されます。
ファクト表には、売上、個数、在庫などのファクト(メジャーとも呼ぶ)が含まれます。
単純メジャーは、1つの表の数値または文字の列(fact.salesなど)です。
計算済メジャーは、1つの表のメジャーを含む式(fact.revenues - fact.expensesなど)です。
マルチ表メジャーは、複数の表で定義される計算済メジャー(fact_a.revenues - fact_b.expensesなど)です。
ファクト表には、時間、製品、市場など、関連するビジネス・エンティティごとにビジネス・トランザクションを編成する1つ以上の外部キーが含まれます。ほとんどの場合、これらの外部キーはNULLでなく、ファクト表の複合一意キーを形成します。外部キーはそれぞれ ディメンション表の1つの行のみと結合します。
マテリアライズド・ビューは事前に計算された表で、ファクト表および場合によってはディメンション表からの集計データおよび結合データで構成されます。
データ・ウェアハウス・デザインがこれらのガイドラインに従わない場合でも、サマリー管理によって、クエリー・リライトおよびマテリアライズド・ビューのリフレッシュを含む多くの有効な機能が実行できます。ただし、スキーマ・デザインがこれらのガイドラインに従う場合は、問合せ実行パフォーマンスおよびマテリアライズド・ビューのリフレッシュ・パフォーマンスが大幅に向上し、必要なマテリアライズド・ビューの数を削減できます。
マテリアライズド・ビューの定義には、任意の数の集計および結合を含めることができます。いくつかの点で、マテリアライズド・ビューは索引と同じように動作します。
マテリアライズド・ビューの目的は、問合せ実行パフォーマンスを向上させることです。
マテリアライズド・ビューの存在は、SQLアプリケーションに対して透過的であるため、データベース管理者は、SQLアプリケーションの妥当性に影響を与えることなく、いつでもマテリアライズド・ビューを作成または削除できます。
マテリアライズド・ビューは、記憶領域を消費します。
マテリアライズド・ビューの内容は、ベースとなるディテール表が変更された場合に、更新される必要があります。
この項には次のトピックが含まれます:
正規化または部分的に正規化されているディメンション表(複数の表に格納されているディメンション)の場合は、これらの表がどのように結合されているかを識別します。ディメンション表同士の結合においては、親表の各行と子表の各行の間に1対多の関係が保証されているかどうかに注意してください。また非正規化ディメンションの場合、子の列は親(または属性)の列を一意に決定できなくてはなりません。これらの制約で表されている関係が他の方法で保証されている場合、NOVALIDATEオプションおよびRELYオプションで使用可能にできます。ファクト表とディメンション表の間の結合がこの親子関係をサポートしない場合でも、CREATE DIMENSION文でディメンションを定義することで、パフォーマンスが大幅に向上します。制限を施行する別の方法には、マテリアライズド・ビューの定義(CREATE MATERIALIZED VIEW文)で外部結合を使用することがあります。
これらのリレーションシップを満たさないスキーマでは、ディメンションを作成しないでください。作成すると、問合せで不適切な結果が戻される場合があります。
サマリー管理の様々なコンポーネントを定義して使用する前に、スキーマ・デザインを調べて、できるだけ次のガイドラインに従う必要があります。ガイドライン1および2は、ガイドライン3より重要です。スキーマ・デザインがガイドライン1および2に従っていない場合、ガイドライン3に従っているかどうかは問題ではありません。ガイドライン1、2および3は、クエリー・リライトのパフォーマンスおよびマテリアライズド・ビューのリフレッシュ・パフォーマンスの両方に影響します。
ディメンションのガイドライン1
(各ディメンションが1つの表に収まるように)ディメンションが非正規化されるか、正規化または部分的に正規化されたディメンションの表間の結合において、親表の各行と子表の各行の間に1対多の関係が保証される必要があります。
子の結合キーに外部キー制約およびNOT NULL制約を追加し、親の結合キーに主キー制約を追加すると、この条件を規定できます。
ディメンションのガイドライン2
ディメンションが非正規化または部分的に非正規化されている場合、ディメンション表のキー列間で階層整合性を保つ必要があります。それぞれの子キー値は、ディメンション表が非正規化されていても、その親キー値を一意に識別する必要があります。非正規化ディメンションの階層整合性は、DBMS_DIMENSIONパッケージのVALIDATE_DIMENSIONプロシージャをコールすることで検証できます。
ディメンションのガイドライン3
ファクト表およびディメンション表では、同様に、各ファクト表の行がディメンション表の1つの行のみと結合することを保証する必要があります。この条件を宣言し、オプションで規定する必要があります。それには、ファクト表のキー列に外部キー制約およびNOT NULL制約を追加し、ディメンション表のキー列に主キー制約を追加するか、外部結合を使用します。データ・ウェアハウスでは、制約規定によるパフォーマンス・オーバーヘッドを回避するために、通常、NOVALIDATE句およびRELY句を使用して制約を使用可能にします。
ディメンションのガイドライン4
各ロード後、マテリアライズド・ビューをリフレッシュする前に、DBMS_DIMENSIONパッケージのVALIDATE_DIMENSIONプロシージャを使用してディメンションの整合性を増分検証します。
増分ロードのガイドライン
ディテール・データの増分ロードは、SQL*Loaderダイレクト・パス・オプション、またはOracleのダイレクト・パス・インタフェースを使用するバルク・ロード・ユーティリティを使用して実行する必要があります。これには、INSERT ... AS SELECT(APPENDまたはPARALLELヒントを利用)などがあり、このヒントによりダイレクト・ローダーのログが挿入時に使用されます。
パーティションのガイドライン
可能な場合は、単調に増加する(できればDATE型の)時間列によって、表のレンジ・パーティション化/コンポジット・パーティション化を行います。
時間ディメンションのガイドライン
時間ディメンションがマテリアライズド・ビューに時間列として表示される場合は、ファクト表の場合と同じ方法でマテリアライズド・ビューをパーティション化して索引を付けます。
制約を使用可能にするために必要な時間、および制約に違反する場合を考慮する必要がある場合は、ENABLE NOVALIDATE文でRELY句を使用して、既存の制約の妥当性チェックを行わずに、制約チェックをONにします。この方法には、制約が1つでも損われた場合、不正確な問合せ結果が戻される可能性があるというデメリットがあります。そのため、データがどれだけ正確か、また、不正確な結果が戻される可能性が大きすぎないかどうかを設計時に判断する必要があります。
関連項目:
親表と子表の各行の結合をメンテナンスするメリットの詳細は、ディメンションの作成を参照してください。
『Oracle Database SQL言語リファレンス』
データ・ウェアハウスまたはデータ・マートにデータをロードする一般的で効率的な方法には、SQL*LoaderでDIRECTまたはPARALLELオプションを使用する方法、データ・ポンプを使用する方法、またはOracleのダイレクト・パスAPIを使用する別のローダー・ツールを使用する方法があります。DIRECTまたはPARALLELキーワードを指定してSQL*Loaderを使用する場合の制限および考慮点については、『Oracle Databaseユーティリティ』を参照してください。
ロード方法は、1フェーズまたは2フェーズに分類できます。1フェーズ・ロードでは、データはターゲット表に直接ロードされ、品質保証テストが実行されます。エラーは、マテリアライズド・ビューをリフレッシュする前にDML操作を実行することによって解決されます。多くの削除が行われる場合、ディスク使用率に悪影響を及ぼすことがありますが、一時領域要件およびロード時間が最小化されます。
2フェーズ・ロード・プロセスでは、次の処理が行われます。
データがウェアハウスの一時表にロードされます。
品質保証プロシージャがデータに適用されます。
ターゲット表に対する参照整合性制約が使用禁止となり、ターゲット・パーティションのローカル索引がUNUSABLEとマークされます。
INSERT AS SELECTを使用し、PARALLELまたはAPPENDヒントを指定することによって、データが一時領域からターゲット表の適切なパーティションにコピーされます。その後、一時表が削除されます。あるいは、ターゲット表がパーティション化されている場合は、ターゲット表に新しい(空の)パーティションを作成し、ALTER TABLE ... EXCHANGE PARTITIONを使用して一時表をターゲット表に取り込むこともできます。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
通常、NOVALIDATEオプション付きで制約が使用可能になります。
ディテール・データのロードおよびディテール・データ上の索引の更新を行うと、必要に応じて、データベースに対する操作ができるようになります。ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE文を発行することにより、すべてのマテリアライズド・ビューがリフレッシュされるまで、クエリー・リライトをシステム・レベルで使用禁止にできます。
QUERY_REWRITE_INTEGRITYをSTALE_TOLERATEDと設定した場合、ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE文を発行すると、最新ロードのデータが反映されたマテリアライズド・ビューを必要としないユーザーに対して、マテリアライズド・ビューへのアクセスをセッション・レベルで許可できます。この使用例は、QUERY_REWRITE_INTEGRITYがENFORCEDまたはTRUSTEDの場合には適用されません。これは、この2つのモードでは、更新されたデータを持つマテリアライズド・ビューのみがクエリー・リライトで使用されるためです。
マテリアライズド・ビューの使用目的はパフォーマンスの向上ですが、マテリアライズド・ビュー管理によるオーバーヘッドが、システム管理上の大きな問題になる場合があります。マテリアライズド・ビューに必要な管理アクティビティをレビューまたは評価するときは、次のことを考慮します。
最初に作成するマテリアライズド・ビューの特定
マテリアライズド・ビューの索引付け
データベース更新のたびに、すべてのマテリアライズド・ビューおよびその索引が適切にリフレッシュされたかどうかの確認
使用されたマテリアライズド・ビューのチェック
作業負荷パフォーマンスに対する各マテリアライズド・ビューの効率の判断
マテリアライズド・ビューが使用した領域の計算
作成が必要な新しいマテリアライズド・ビューの判断
削除が必要な既存のマテリアライズド・ビューの判断
有効ではなくなった古いディテールおよびマテリアライズド・ビュー・データのアーカイブ
データ・ウェアハウスまたはデータ・マートを最初に作成および移入した後の主な管理オーバーヘッドは更新処理です。これには次が含まれます。
業務系システムによる増分変更の定期的な抽出
データの変換
増分変更が正しく、一貫性があり、完全であるかどうかの検証
ウェアハウスへのデータのバルク・ロード
ディテール・データに対する一貫性を保つための索引とマテリアライズド・ビューのリフレッシュ
通常、更新処理は、更新ウィンドウと呼ばれる制限時間内に実行される必要があります。更新ウィンドウは、更新頻度(毎日、毎週など)およびビジネスの特性によって異なります。更新頻度が毎日の場合、更新ウィンドウは2から6時間になります。
次のアクティビティの更新ウィンドウを知る必要があります。
ディテール・データのロード
ディテール・データに対する索引の更新または再作成
データに対する品質保証テストの実行
マテリアライズド・ビューのリフレッシュ
マテリアライズド・ビューに対する索引の更新
マテリアライズド・ビュー作成文のSELECT句で、マテリアライズド・ビューに含めるデータが定義されます。指定できる内容に関する制限はごく少数です。必要な数の表を結合できます。表のみでなく、ビュー、インライン・ビュー(SELECT文のFROM句の副問合せ)、副問合せおよびマテリアライズド・ビューなど、他の要素もすべてSELECT句で結合または参照できます。ただし、定義問合せのSELECT構文のリストに副問合せを持つマテリアライズド・ビューは定義できません。WHERE句など、定義問合せのその他の場所に副問合せを記述することは可能です。
マテリアライズド・ビューには、次のタイプがあります。
データ・ウェアハウスでは、通常、マテリアライズド・ビューには例5-1のような集計が含まれています。高速リフレッシュを可能にするには、SELECT構文のリストにすべてのGROUP BY列(ある場合)を含める必要があります。また、集計列にCOUNT(*)およびCOUNT(column)が必要です。さらに、マテリアライズド・ビュー・ログは、マテリアライズド・ビューを定義する問合せで参照されるすべての表に関して存在する必要があります。有効な集計関数は、SUM、COUNT(x)、COUNT(*)、AVG、VARIANCE、STDDEV、MIN、MAXです。また、任意のSQL値式を集計できます。集計を含むマテリアライズド・ビューの高速リフレッシュに関する制限を参照してください。
結合と集計を含むマテリアライズド・ビューの高速リフレッシュは、実表に対してDML(ダイレクト・ロードまたは従来型のINSERT、UPDATEまたはDELETE)を実行した後に可能になります。ON COMMITまたはON DEMANDでリフレッシュされるように定義できます。REFRESH ON COMMITを指定した場合は、マテリアライズド・ビューのディテール表の1つに対してDMLを実行するトランザクションがコミットされると、マテリアライズド・ビューが自動的にリフレッシュされます。この方法を選択すると、コミット完了までの所要時間は通常より少し長くなることがあります。これは、リフレッシュ操作がコミット・プロセスの一部として実行されるためです。したがって、この方法は、多数のユーザーがマテリアライズド・ビューの実表を同時に変更する場合には適していません。
次に、集計を含むマテリアライズド・ビューの例をいくつか示します。この例のマテリアライズド・ビューは高速リフレッシュされるため、マテリアライズド・ビュー・ログの作成は必須であることに注意してください。
例5-1 マテリアライズド・ビューの作成(合計売上数と合計売上金額)
CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, prod_category, prod_category_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW product_sales_mv PCTFREE 0 TABLESPACE demo STORAGE (INITIAL 8M) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales, COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;
この例では、製品の合計売上数と合計売上金額を計算するマテリアライズド・ビューproduct_sales_mvが作成されます。これは、prod_id列で表salesおよびproductsを結合することで導出されます。このマテリアライズド・ビューは、作成方法がIMMEDIATEであるため、データがすぐに移入され、クエリー・リライトに使用できます。この例では、デフォルトのリフレッシュ方法はFASTです。これが許されるのは、表productsおよびsalesに関して適切なマテリアライズド・ビュー・ログが作成されているためです。
WITH COMMIT SCN句を含むマテリアライズド・ビュー・ログを使用すると、ローカル・マテリアライズド・ビューの高速リフレッシュのパフォーマンスが向上します。次に例を示します。
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID(prod_id, cust_id, time_id), COMMIT SCN INCLUDING NEW VALUES;
例5-2 マテリアライズド・ビューの作成 (計算された売上の合計)
CREATE MATERIALIZED VIEW product_sales_mv PCTFREE 0 TABLESPACE demo STORAGE (INITIAL 8M) BUILD DEFERRED REFRESH COMPLETE ON DEMAND ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;
この例では、prod_nameごとの合計売上を計算するマテリアライズド・ビューproduct_sales_mvが作成されます。これは、prod_id列で表salesおよびproductsを結合することで導出されます。このマテリアライズド・ビューは、作成方法がDEFERREDであるため、最初は、どのデータも含みません。作成方法がDEFERREDのマテリアライズド・ビューの最初のリフレッシュには、完全リフレッシュが必要です。このマテリアライズド・ビューがリフレッシュされ、移入されると、クエリー・リライトに使用できます。
例5-3 マテリアライズド・ビューの作成(単一表集計)
CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW sum_sales PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp, SUM(s.amount_sold) AS sum_dollar_sales, COUNT(s.amount_sold) AS count_dollar_sales, SUM(s.quantity_sold) AS sum_quantity_sales, COUNT(s.quantity_sold) AS count_quantity_sales FROM sales s GROUP BY s.prod_id, s.time_id;
この例では、単一表集計を含むマテリアライズド・ビューが作成されます。マテリアライズド・ビューの定義問合せの中ですべての参照列にマテリアライズド・ビュー・ログが作成されているので、このマテリアライズド・ビューは高速リフレッシュが可能です。DMLがsales表に対して適用される場合、コミットが発行されたときに変更がマテリアライズド・ビューに反映されます。
関連項目:
CREATE MATERIALIZED VIEWおよびCREATE MATERIALIZED VIEW LOG文の構文は、『Oracle Database SQL言語リファレンス』を参照してください。
表5-1に、マテリアライズド・ビューの集計要件を示します。集計Xがある場合、集計Yが必要であり、集計Zはオプションです。
表5-1 集計を含むマテリアライズド・ビューの要件
| X | Y | Z |
|---|---|---|
|
|
|
|
||
|
||
|
|
|
|
- |
|
|
|
|
|
|
|
|
|
|
すべてのタイプの高速リフレッシュを保証するには、常にCOUNT(*)が必要です。さもないと、挿入後の高速リフレッシュのみに制限される場合があります。集計を最も効率よく正確に高速リフレッシュできるように、マテリアライズド・ビューにZ列のオプションの集計も含めることをお薦めします。
例5-4のように、マテリアライズド・ビューに結合のみが含まれ、集計は含まれない場合があります。この例では、sales表をtimes表とcustomers表に結合するマテリアライズド・ビューが作成されます。このタイプのマテリアライズド・ビューを作成するメリットは、コストの高い結合が事前に計算されることです。
結合のみを含むマテリアライズド・ビューの高速リフレッシュは、実表に対してDML(ダイレクト・パスまたは従来型のINSERT、UPDATEまたはDELETE)を実行した後に可能になります。
結合のみを含むマテリアライズド・ビューは、ON COMMITまたはON DEMANDでリフレッシュされるように定義できます。ON COMMITの場合、リフレッシュは、マテリアライズド・ビューにある1つのディテール表上でDMLを実行するトランザクションのコミット時に実行されます。
REFRESH FASTを指定する場合、Oracle Databaseは、問合せ定義をさらに検証して、いずれかのディテール表が変更された場合の高速リフレッシュの実行を保証します。これらの追加チェックには、次の制限が含まれます。
表がパーティション・チェンジ・トラッキング(PCT)をサポートしないかぎり、マテリアライズド・ビュー・ログがディテール表ごとに存在する必要があります。また、マテリアライズド・ビュー・ログが必須の場合は、ROWID列が各マテリアライズド・ビュー・ログに存在していること。
すべてのディテール表のROWIDが、マテリアライズド・ビュー問合せ定義のSELECT構文のリストにあること。
これらの制限で満たされないものがある場合は、マテリアライズド・ビューをREFRESH FORCEとして作成し、可能なときに高速リフレッシュの効果を得ることができます。表の1つがすべての基準を満たさなくても、他の表がすべての基準を満たしている場合は、すべての基準が満たされている他の表に関しては、マテリアライズド・ビューを高速リフレッシュできます。
最も効率的にリフレッシュが実行されるようにするには、内部結合のように動作する外部結合を定義問合せで使用しないようにします。このような結合が定義問合せに含まれている場合は、内部結合を使用するように定義問合せをリライトすることを検討してください。
関連項目:
リフレッシュ・パフォーマンスを低下させる条件の詳細は、結合のみを含むマテリアライズド・ビューの高速リフレッシュに関する制限を参照してください。
マテリアライズド・ビューに含まれているのが結合のみである場合は、各表のROWID列(およびFROM句のリストに複数回指定されている表)を、マテリアライズド・ビューのSELECT構文のリストに指定する必要があります。
マテリアライズド・ビューのFROM句にリモート表が指定されている場合、そのFROM句の表はすべて同じサイトに配置されている必要があります。また、リモート表を含むマテリアライズド・ビューでは、ON COMMITによるリフレッシュはサポートされません。SCNベースのマテリアライズド・ビュー・ログを除き、マテリアライズド・ビューの各ディテール表のマテリアライズド・ビュー・ログはリモート・サイトに作成されている必要があり、ROWID列は、次の例に示すようにマテリアライズド・ビューのSELECT構文のリストに指定されている必要があります。
例5-4 結合のみを含むマテリアライズド・ビュー
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv
PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
また、前述の例にtimes_rid列およびcustomers_rid列が含まれず、リフレッシュ方法がREFRESH FORCEであった場合、このマテリアライズド・ビューを高速リフレッシュできるのは、sales表が更新された場合のみです。times表またはcustomers表が更新された場合は、高速リフレッシュできません。
CREATE MATERIALIZED VIEW detail_sales_mv PARALLEL BUILD IMMEDIATE REFRESH FORCE AS SELECT s.rowid "sales_rid", c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id FROM sales s, times t, customers c WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
ネステッド・マテリアライズド・ビューとは、その定義が別のマテリアライズド・ビューに基づいているマテリアライズド・ビューです。ネステッド・マテリアライズド・ビューは、マテリアライズド・ビューの他に、データベース内の他のリレーションも参照する場合があります。
この項には次のトピックが含まれます:
データ・ウェアハウスでは、通常、単一の結合上に多数の集計ビュー(たとえば、異なるディメンションに沿ったロールアップ)を作成します。これらの個別の結合と集計を含むマテリアライズド・ビューに対する増分メンテナンスは、ベースとなる結合が何度も実行される必要があるため、かなりの時間がかかります。
ネステッド・マテリアライズド・ビューを使用すると、結合のみを含む1つのマテリアライズド・ビューに基づいて複数の単一表マテリアライズド・ビューを作成できます。さらに、この種類の単一表集計マテリアライズド・ビューには最適化が実行され、リフレッシュが非常に効率的になります。
例5-5 ネステッド・マテリアライズド・ビュー
マテリアライズド・ビューに対してネステッド・マテリアライズド・ビューを作成できますが、親およびベースのマテリアライズド・ビューには結合または集計が含まれている必要があります。マテリアライズド・ビューの定義問合せに結合または集計が含まれない場合はネストできません。マテリアライズド・ビューの定義のベースとなるすべてのオブジェクト(マテリアライズド・ビューまたは表)には、マテリアライズド・ビュー・ログが必要です。ベースとなるオブジェクトは、すべて表と同様に扱われます。また、マテリアライズド・ビュー用のオプションはすべて使用できます。
次のマテリアライズド・ビューは、shサンプル・スキーマの表と列を使用するネステッド・マテリアライズド・ビューの作成方法を示しています。
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
/*create materialized view join_sales_cust_time as fast refreshable at
COMMIT time */
CREATE MATERIALIZED VIEW join_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid
FROM sales s, customers c, times t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;
join_sales_cust_time表に対するネステッド・マテリアライズド・ビューを作成するには、その表に対してマテリアライズド・ビュー・ログを作成する必要があります。これはjoin_sales_cust_time表に対する単一表集計マテリアライズド・ビューになるため、必要なすべての列をログに記録して、INCLUDING NEW VALUES句を使用する必要があります。
/* create materialized view log on join_sales_cust_time */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time
WITH ROWID (cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;
/* create the single-table aggregate materialized view sum_sales_cust_time
on join_sales_cust_time as fast refreshable at COMMIT time */
CREATE MATERIALIZED VIEW sum_sales_cust_time
REFRESH FAST ON COMMIT AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)
cnt_sales, cust_last_name, day_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;
ネステッド・マテリアライズド・ビューの中には、高速リフレッシュできないタイプがあります。このようなタイプのマテリアライズド・ビューを特定するには、EXPLAIN_MVIEWを使用します。DBMS_MVIEW.REFRESHパラメータとともにnested = TRUEパラメータを指定すると、適切な依存順序でネステッド・マテリアライズド・ビューのツリーをリフレッシュできます。たとえば、DBMS_MVIEW.REFRESH ('SUM_SALES_CUST_TIME', nested => TRUE)とコールすると、REFRESHプロシージャによって、まずjoin_sales_cust_timeマテリアライズド・ビューがリフレッシュされ、次にsum_sales_cust_timeマテリアライズド・ビューがリフレッシュされます。
ネステッド・マテリアライズド・ビューを使用するかどうかを決定する場合、次の点に注意する必要があります。
高速リフレッシュが必要な場合は、依存しているすべてのマテリアライズド・ビューも高速リフレッシュする必要があります。
最高レベルのマテリアライズド・ビューをディテール表と同じ更新レベルに保つには、最高レベルのマテリアライズド・ビューをリフレッシュする前に、ツリー内のすべてのマテリアライズド・ビューが正しい依存順序でリフレッシュされるようにする必要があります。結合および集計を含むマテリアライズド・ビューのネストについてで説明しているように、nested = TRUEパラメータを使用すると、ネスト階層内の中間マテリアライズド・ビューを自動的にリフレッシュできます。nested = TRUEを指定せず、最高レベルのマテリアライズド・ビューの下にあるマテリアライズド・ビューが失効している場合、最高レベルのみのリフレッシュは正常に終了しますが、これはベースとなるマテリアライズド・ビューの観点から更新されているのみで、ツリーの基礎であるディテール表の観点から更新されているわけではありません。
マテリアライズド・ビューをリフレッシュするときは、ツリー内のすべてのマテリアライズド・ビューがリフレッシュされるようにする必要があります。最高レベルのマテリアライズド・ビューのみをリフレッシュした場合、その下にあるマテリアライズド・ビューは失効するため、これらは明示的にリフレッシュする必要があります。nestedパラメータの値をTRUEに設定してREFRESHプロシージャを実行した場合、指定したマテリアライズド・ビューと、ツリー内の子マテリアライズド・ビューのみがリフレッシュされます。そのツリー内の最高レベルのマテリアライズド・ビューはリフレッシュされません。ツリー内のすべてのマテリアライズド・ビューを確実にリフレッシュする必要がある場合は、nestedパラメータの値をTRUEに設定してREFRESH_DEPENDENTプロシージャを実行します。
特定のネステッド・マテリアライズド・ビューでサポートされている唯一のオプションが完全リフレッシュである場合、高速リフレッシュが指定されても、完全リフレッシュが実行されます。
マテリアライズド・ビューが最新であるかどうかは、そのマテリアライズド・ビューが直接的に参照しているオブジェクトとの比較によって決められます。マテリアライズド・ビューが別のマテリアライズド・ビューを参照している場合、最高レベルのマテリアライズド・ビューが最新であるかどうかは、そのマテリアライズド・ビューが直接的に参照しているマテリアライズド・ビューでの変更と比較することによって決められます。
マテリアライズド・ビューは、CREATE MATERIALIZED VIEW文またはEnterprise Managerを使用して作成できます。例5-6では、cust_sales_mvというマテリアライズド・ビューが作成されます。
例5-6 マテリアライズド・ビューの作成
CREATE MATERIALIZED VIEW cust_sales_mv PCTFREE 0 TABLESPACE demo STORAGE (INITIAL 8M) PARALLEL BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT c.cust_last_name, SUM(amount_sold) AS sum_amount_sold FROM customers c, sales s WHERE s.cust_id = c.cust_id GROUP BY c.cust_last_name;
一般的に、データ・ウェアハウスにはすでにサマリー表または集計表が作成されており、新しいマテリアライズド・ビューを作成して、この作業を繰り返すことはありません。この場合、すでにデータベースに存在する表は、事前作成マテリアライズド・ビューとして登録できます。この方法については、既存のマテリアライズド・ビューの登録を参照してください。
作成するマテリアライズド・ビューを選択した後、各マテリアライズド・ビューに対して次の手順を実行します。
マテリアライズド・ビューを設計します。既存のユーザー定義のマテリアライズド・ビューについてはこの手順は不要です。マテリアライズド・ビューに多数の行が含まれる場合、適切であれば、マテリアライズド・ビューをパーティション化します(可能な場合)。このパーティション化は、最大または最も頻繁に更新されるディテール表またはファクト表のパーティション化と一致させる必要があります(可能な場合)。パーティション化により、リフレッシュのパフォーマンスが向上します。これは、パラレルDML機能と、場合によってはPCTベースのリフレッシュを利用できるようになるためです。
CREATE MATERIALIZED VIEW文を使用して、マテリアライズド・ビューを作成および移入(オプション)します。すでにユーザー定義マテリアライズド・ビューが存在する場合は、CREATE MATERIALIZED VIEW文のON PREBUILT TABLE句を使用します。それ以外の場合は、BUILD IMMEDIATE句を使用してマテリアライズド・ビューにすぐに移入するか、BUILD DEFERRED句を使用して後から移入します。BUILD DEFERREDで作成したマテリアライズド・ビューは、最初のCOMPLETE REFRESHが行われるまで、クエリー・リライトで使用できません。ENABLE QUERY REWRITE句を指定していれば、最初のリフレッシュの後で自動的にクエリー・リライトが使用可能になります。
現在のバージョンでは、マテリアライズド・ビューの作成時、定義問合せのSELECT構文のリストに同じ名前の列が複数含まれている場合は、それらに一意の別名を付与して名前の競合を解消する必要があります。競合を解消しないと、列の定義が曖昧であるというエラー・メッセージが表示され、CREATE MATERIALIZED VIEW文が失敗します。しかし、名前解決のために通常の方法でSELECT句に別名を指定すると、テキストの完全一致のクエリー・リライトの使用が制限され、マテリアライズド・ビューの定義問合せのテキストとユーザーの入力問合せのテキストが同一である場合にのみ、テキストの完全一致のリライトが行われることになります。したがって、マテリアライズド・ビューの定義問合せのSELECT構文のリストに別名を指定し、問合せに別名が指定されなかった場合、テキストの完全一致の比較は失敗します。これは特に、列の別名を大量に使用するDiscovererからの問合せで問題になります。
次に、問題の例を示します。sales_mvを作成する際、SELECT句に列の別名が指定されていますが、入力問合せQ1には別名が指定されていません。このため、テキストの完全一致のリライトは失敗します。マテリアライズド・ビューは次のとおりです。
CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE AS
SELECT s.time_id sales_tid, c.time_id costs_tid
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);
入力問合せ文Q1は次のとおりです。
SELECT s.time_id, c1.time_id
FROM sales s, products p, costs c1
WHERE s.prod_id = p.prod_id AND c1.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);
マテリアライズド・ビューの定義問合せは、ユーザーの入力問合せとほとんど同一であり、論理的には同等ですが、クエリー・リライトは行われません。これは、一部の問合せ(WHERE句の副問合せなど)ではリライトが行われる唯一の可能性であるテキストの完全一致に失敗しているためです。
CREATE MATERIALIZED VIEW文には、列の別名リストを追加できます。列の別名リストを使用すると、マテリアライズド・ビューのSELECT句に別名を指定することなく、列名の競合を明示的に解消できます。次の例に、マテリアライズド・ビューの列の別名リストの構文を示します。
CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid)
ENABLE QUERY REWRITE AS
SELECT s.time_id, c.time_id
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);
この例では、sales_mvの定義問合せが、ユーザー問合せQ1と完全に一致しています。このため、テキストの完全一致のリライトが行われます。
別名をSELECT句と新しい別名リスト句の両方に指定した場合は、SELECT句に指定した別名ではなく、別名リスト句が使用されます。
マテリアライズド・ビューの名前は、Oracleの標準のネーミング規則に従っている必要があります。ただし、マテリアライズド・ビューがユーザー定義の事前作成表を基にしている場合は、マテリアライズド・ビューの名前はその表名と一致させる必要があります。
すでに表および索引のネーミング規則がある場合は、このネーミング計画をマテリアライズド・ビューに拡張して、マテリアライズド・ビューを簡単に識別できるようにすることが可能です。たとえば、マテリアライズド・ビューをsum_of_salesではなく、sum_of_sales_mvとネーミングすることで、これがマテリアライズド・ビューであり、表またはビューではないことを表すことができます。
マテリアライズド・ビューは、ユーザー定義の事前作成表を基にしていないかぎり、データベース内の記憶域を必要とし、これを占有します。したがって、マテリアライズド・ビューの記憶域が必要な場合、それが存在する表領域およびエクステントのサイズを指定する必要があります。
マテリアライズド・ビューに必要な領域のサイズがわからない場合は、DBMS_MVIEW.ESTIMATE_MVIEW_SIZEパッケージを使用して、圧縮されていないマテリアライズド・ビューの格納に必要なバイト数を見積ります。この情報は、設計者が、どの表領域にマテリアライズド・ビューを常駐させる必要があるかを判断する場合の参考になります。
表の圧縮は、冗長性の高いデータ(多数の外部キーを持つ表など)で使用します。これは、ROLLUP句を使用して作成したマテリアライズド・ビューには特に役立ちます。表の圧縮によりディスクの使用とメモリーの使用(具体的にはバッファ・キャッシュ)が削減され、読取り専用操作のスケールアップが向上します。また、表の圧縮によって、更新コストと引換えに問合せの実行速度が向上します。
関連項目:
表の圧縮の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。
表の圧縮の詳細は、『Oracle Database管理者ガイド』を参照してください。
STORAGEセマンティクスの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
表5-2に示すように、マテリアライズド・ビューを作成する方法は2つあります。BUILD IMMEDIATEで作成すると、マテリアライズド・ビューの定義がデータ・ディクショナリ内のスキーマ・オブジェクトに追加されます。その後、ファクト表またはディテール表がSELECT文に従ってスキャンされ、その結果がマテリアライズド・ビューに格納されます。スキャンされる表のサイズによっては、作成処理にかなりの時間がかかる場合があります。
BUILD DEFERRED句を使用することもできます。この句は、データなしでマテリアライズド・ビューを作成するため、DBMS_MVIEW.REFRESHパッケージを使用して、後でデータを移入できます。
関連項目:
表5-2 作成方法
| 作成方法 | 説明 |
|---|---|
|
マテリアライズド・ビューを作成して、データを移入します。 |
|
マテリアライズド・ビューの定義は作成しますが、データは移入しません。 |
マテリアライズド・ビューを作成する前に、プロシージャDBMS_MVIEW.EXPLAIN_MVIEWをコールすると、使用可能なクエリー・リライトのタイプを確認できます。また、DBMS_ADVISOR.TUNE_MVIEWを使用すると、マテリアライズド・ビューを最適化して、多くのタイプのクエリー・リライトを使用可能にできます。マテリアライズド・ビューの作成後は、DBMS_MVIEW.EXPLAIN_REWRITEを使用して、特定の問合せがリライトされるかどうか(および、リライトされない理由)を調べることができます。
マテリアライズド・ビューが定義された場合でも、クエリー・リライト機能が自動的に使用されることはありません。クエリー・リライトがデフォルトで使用可能にされている場合でも、マテリアライズド・ビューをクエリー・リライトで使用可能にするには、ENABLE QUERY REWRITE句も指定する必要があります。
マテリアライズド・ビューの作成時に、この句を省略するか、DISABLE QUERY REWRITEとして指定した場合は、後でALTER MATERIALIZED VIEW文を使用して、マテリアライズド・ビューのクエリー・リライトを使用可能にできます。
マテリアライズド・ビューをBUILD DEFERREDとして定義する場合は、完全リフレッシュによってマテリアライズド・ビューにデータを移入しないかぎり、クエリー・リライトは使用できません。
すべてのマテリアライズド・ビューでクエリー・リライトが可能なわけではありません。クエリー・リライトが予想どおりに実行されない場合は、DBMS_MVIEW.EXPLAIN_REWRITEを使用すると、特定の問合せがリライトに適していない理由がわかります。このプロシージャによって、一部のクエリー・リライトが使用可能でないことが示された場合は、プロシージャDBMS_ADVISOR.TUNE_MVIEWを使用すると、マテリアライズド・ビューの定義を変更してクエリー・リライトを使用可能にできるかどうかを確認できます。また、マテリアライズド・ビューが次の条件をすべて満たしているかどうかを確認してください。
次の制限を考慮する必要があります。
マテリアライズド・ビューの定義問合せに、結果の再現が不可能な式(ROWNUM、SYSDATE、結果の再現が不可能なPL/SQLファンクションなど)を含めることはできません。
問合せにLONGまたはLONG RAWデータ型やREFオブジェクトの参照を含めることはできません。
マテリアライズド・ビューがPREBUILTとして登録された場合、WITH REDUCED PRECISIONでオーバーライドされないかぎり、列の精度は対応するSELECT文の精度と一致する必要があります。
定義する問合せにオブジェクトやXMLTYPEへの参照を含めることはできません。
マテリアライズド・ビューは非エディション・オブジェクトであり、エディション・オブジェクトの名前を解決する必要のある評価エディションが指定されていないかぎり、エディション・オブジェクトに依存できません。
マテリアライズド・ビューは、特定範囲のエディションでのみクエリー・リライトが可能です。CREATEまたはALTER MATERIALIZED VIEW文の中のquery_rewrite_clauseで、マテリアライズド・ビューがクエリー・リライト可能なエディションの範囲を指定できます。
関連項目:
『Oracle Database SQL言語リファレンス』
次の制限を考慮する必要があります。
問合せはローカル表およびリモート表の両方を参照する場合があります。このような問合せは、同じ表を参照している適格なマテリアライズド・ビューがローカルで使用できる場合にはリライトできます。
SYSは、ディテール表もマテリアライズド・ビューも所有できません。
マテリアライズド・ビューのGROUP BY句に列または式を指定する場合は、その列または式をSELECT構文のリストにも指定する必要があります。
集計関数は、式の最も外側でのみ使用する必要があります。つまり、AVG(AVG(x))やAVG(x)+ AVG(x)などの集計は実行できません。
CONNECT BY句は使用できません。
関連項目:
『Oracle Database SQL言語リファレンス』
マテリアライズド・ビューを定義する際は、リフレッシュ方法、リフレッシュのタイプ、トラステッド制約を使用可能にするかどうかの3つのリフレッシュ・オプションを指定できます。オプションを指定しない場合は、デフォルトとして、それぞれON DEMAND、FORCEおよびENFORCED制約が使用されます。
リフレッシュ実行モードは、ON COMMITおよびON DEMANDの2つです。作成するマテリアライズド・ビューによっては、一部のオプションを使用できない場合があります。次の表に、リフレッシュ・モードの説明を示します。
表5-3 リフレッシュ・モード
| リフレッシュ・モード | 説明 |
|---|---|
マテリアライズド・ビューのディテール表の1つを変更したトランザクションをコミットした場合、リフレッシュが自動的に実行されます。このモードを使用できるのは、マテリアライズド・ビューが高速リフレッシュ可能な場合(つまり複雑でない場合)のみです。このモードを使用するには、 |
|
ユーザーが |
マテリアライズド・ビューがON COMMITメソッドを使用してメンテナンスされる場合は、コミット完了までの所要時間が通常より少し長くなることがあります。これは、リフレッシュ操作がコミット・プロセスの一部として実行されるためです。したがって、この方法は、多数のユーザーがマテリアライズド・ビューの実表を同時に変更する場合には適していません。
マテリアライズド・ビューで参照される表に対する挿入、更新または削除操作が、そのマテリアライズド・ビューのリフレッシュと同時に実行されると予想され、そのマテリアライズド・ビューに結合と集計が含まれている場合は、ON DEMAND高速リフレッシュではなくON COMMIT高速リフレッシュを使用することをお薦めします。
マテリアライズド・ビューがリフレッシュされなかったと考えられる場合は、アラート・ログまたはトレース・ファイルをチェックしてください。
COMMITによるリフレッシュ時にマテリアライズド・ビューがリフレッシュされなかった場合は、トレース・ファイルに示されたエラーを解決してから、DBMS_MVIEWパッケージを使用してリフレッシュ・プロシージャを明示的に起動する必要があります。この操作を行わないかぎり、マテリアライズド・ビューはコミット時に自動的にリフレッシュされません。
COMPLETE、FAST、FORCEおよびNEVERの4つのオプションのいずれかを選択すると、ディテール表からのマテリアライズド・ビューのリフレッシュ方法を指定できます。表5-4にリフレッシュ・オプションを示します。
表5-4 リフレッシュ・オプション
| リフレッシュ・オプション | 説明 |
|---|---|
|
マテリアライズド・ビューの定義問合せを再計算することでリフレッシュします。 |
|
マテリアライズド・ビュー・ログに記録された情報を使用するか、SQL*Loaderダイレクト・パスまたはパーティション・メンテナンス操作によって、マテリアライズド・ビューに増分変更を適用してリフレッシュします。 |
|
可能な場合は、 |
|
マテリアライズド・ビューはリフレッシュ機能によりリフレッシュされないことを示します。 |
高速リフレッシュ・オプションが使用可能かどうかは、マテリアライズド・ビューのタイプによって異なります。プロシージャDBMS_MVIEW.EXPLAIN_MVIEWをコールすると、高速リフレッシュが可能かどうかを判断できます。
また、リフレッシュ時にトラステッド制約およびQUERY_REWRITE_INTEGRITY = TRUSTEDが使用可能かどうかも特定できます。妥当性チェックが行われていないRELY制約は、すべてトラステッド制約です。たとえば、ディメンションまたはマテリアライズド・ビューに定義された、妥当性チェックが行われていない外部キー/主キーのリレーションシップまたは機能依存性は、UNKNOWN状態です。リフレッシュ時のクエリー・リライトが使用可能な場合は、よりパフォーマンスの高いクエリー・リライトを使用可能にすると、これらによってリフレッシュのパフォーマンスを向上できます。リフレッシュにTRUSTED制約を使用できるマテリアライズド・ビューは、リフレッシュの後も、最新であると信頼できる状態(UNKNOWN状態)のままになります。
この状態は、ビューUSER_MVIEWSの列STALENESSに反映されます。同じビューの列UNKNOWN_TRUSTED_FDも、Y(Yes)に設定されます。
マテリアライズド・ビューのこのプロパティは、作成時にREFRESH USING TRUSTED [ENFORCED] CONSTRAINTSを指定するか、またはALTER MATERIALIZED VIEW DDLを使用して定義できます。
表5-5 制約
| 使用する制約 | 説明 |
|---|---|
TRUSTED CONSTRAINTS |
リフレッシュの際、トラステッド制約および
|
ENFORCED CONSTRAINTS |
リフレッシュの際、妥当性チェック済の制約および |
マテリアライズド・ビューの高速リフレッシュは、結合列で使用可能な主キー制約および外部キー制約を使用して最適化されます。この外部キー/主キーによる最適化によって、リフレッシュのパフォーマンスは格段に向上します。たとえば、ファクト表とディメンション表の結合を含むマテリアライズド・ビューがあり、最後のリフレッシュ以降、ディメンション表のみに新しい行が挿入されファクト表は変更されていない場合、このマテリアライズド・ビューに関しては何もリフレッシュされません。これは、ディメンション表の結合列の主キー制約およびファクト表の結合列の外部キー制約により、ディメンション表に挿入された新しい行がファクト表のいずれの行とも結合せず、リフレッシュ対象がないためです。リフレッシュの最適化に関する別の例として、ファクト表とディメンション表の両方に対して、最後のリフレッシュ以降に挿入が実行されたとします。この場合、Oracle Databaseによってデルタ・ファクト表とディメンション表の結合のみが実行されます。外部キー/主キーによる最適化を行わない場合、リフレッシュ時に2つの結合が必要になります。デルタ・ファクトとディメンション表の結合、およびデルタ・ディメンションと挿入前のファクト表のイメージの結合です。
このように結合列で主キー制約および外部キー制約を使用して最適化された高速リフレッシュは、制約を強制して、または強制せずに使用できます。前者の場合は、主キー制約および外部キー制約がOracle Databaseによって強制されます。ただし、制約の管理コストがかかります。後者の場合は、アプリケーションによって主キーと外部キーの関係が保証されるため、制約はRELY NOVALIDATEを使用して宣言され、マテリアライズド・ビューはREFRESH FAST USING TRUSTED CONSTRAINTSオプションを使用して定義されます。
マテリアライズド・ビューの定義問合せは、次のように制限されています。
マテリアライズド・ビューには、SYSDATEやROWNUMなど、結果の再現が不可能な式への参照を含めることはできません。
マテリアライズド・ビューには、RAWまたはLONG RAWデータ型への参照を含めることはできません。
SELECT構文のリストに副問合せを含めることはできません。
SELECT句には分析関数(RANKなど)を指定できません。
XMLIndex索引が定義されている表を参照することはできません。
MODEL句を含めることはできません。
副問合せでHAVING句を指定できません。
ANY、ALLまたはNOT EXISTSを含むネストした問合せは使用できません。
[START WITH …] CONNECT BY句を含めることはできません。
別々のサイトに複数のディテール表を含めることはできません。
ON COMMITのマテリアライズド・ビューにリモート・ディテール表を含めることはできません。
ネステッド・マテリアライズド・ビューには結合または集計が必須です。
GROUP BY句を含むマテリアライズド結合ビューおよびマテリアライズド集計ビューは索引構成表から選択できません。
結合のみを含み、集計を含まないマテリアライズド・ビューの定義問合せには、高速リフレッシュに関して次の制限があります。
高速リフレッシュにおける一般的な制限のすべての制限が適用されます。
GROUP BY句または集計を含めることはできません。
FROMリスト内のすべての表のROWIDが、問合せのSELECT構文のリストにある必要があります。
マテリアライズド・ビュー・ログが、問合せのFROMリストにあるすべての実表のROWIDを含む必要があります。
オブジェクト型の列を含む単純結合がSELECT文に指定されている場合、複数の表から高速リフレッシュ可能なマテリアライズド・ビューを作成できません。
また、次に該当する場合は、選択したリフレッシュ方法が効率的に実行されません。
定義問合せに、内部結合のように動作する外部結合が使用されている。このような結合が定義問合せに含まれている場合は、内部結合を使用するように定義問合せをリライトすることを検討してください。
マテリアライズド・ビューのSELECT構文のリストに複数の表の列の式が含まれている。
集計または結合を含むマテリアライズド・ビューの定義問合せには、高速リフレッシュに関して次の制限があります。
高速リフレッシュにおける一般的な制限のすべての制限が適用されます。
高速リフレッシュは、ON COMMITおよびON DEMANDマテリアライズド・ビューの両方についてサポートされますが、次の制限が適用されます。
マテリアライズド・ビューのすべての表にはマテリアライズド・ビュー・ログが必要であり、マテリアライズド・ビュー・ログには次のことが必要です。
マテリアライズド・ビューで参照される表のすべての列を含んでいること。
ROWIDおよびINCLUDING NEW VALUESで指定すること。
表に挿入/ダイレクト・ロード、削除および更新が混在する場合は、SEQUENCE句を指定すること。
高速リフレッシュについてサポートされる集計関数は、SUM、COUNT、AVG、STDDEV、VARIANCE、MINおよびMAXのみです。
COUNT(*)を指定する必要があります。
集計関数は、式の最も外側でのみ使用する必要があります。つまり、AVG(AVG(x))やAVG(x)+ AVG(x)などの集計は実行できません。
AVG(expr)などの集計ごとに、対応するCOUNT(expr)が存在している必要があります。さらにSUM(expr)を指定することをお薦めします。詳細は、集計を含むマテリアライズド・ビューの使用要件を参照してください。
VARIANCE(expr)またはSTDDEV(expr)が指定された場合は、COUNT(expr)およびSUM(expr)を指定する必要があります。さらにSUM(expr *expr)を指定することをお薦めします。詳細は、集計を含むマテリアライズド・ビューの使用要件を参照してください。
定義問合せのSELECT列は、複数の実表の列を含む複合式にはできません。これに対する解決策の1つとして、ネステッド・マテリアライズド・ビューを使用します。
SELECT構文のリストには、すべてのGROUP BY列が含まれる必要があります。
マテリアライズド・ビューは1つまたは複数のリモート表に基づいていないものとします。
マテリアライズド・ビュー・ログのフィルタ列でCHARデータ型を使用する場合、マスター・サイトおよびマテリアライズド・ビューのキャラクタ・セットは同じである必要があります。
マテリアライズド・ビューに次のいずれかが含まれる場合は、従来のDMLの挿入およびダイレクト・ロードに対してのみ高速リフレッシュがサポートされます。
MINまたはMAX集計を含むマテリアライズド・ビュー
SUM(expr)を含むがCOUNT(expr)を含まないマテリアライズド・ビュー
COUNT(*)を含まないマテリアライズド・ビュー
このようなマテリアライズド・ビューは、挿入専用マテリアライズド・ビューと呼ばれます。
MAXまたはMINを含むマテリアライズド・ビューは、WHERE句がなければ、削除文または混在型のDML文の後に高速リフレッシュできます。
削除または複合DMLの後の最大/最小高速リフレッシュには、挿入専用の場合と同じ動作はありません。影響されたグループの最大値/最小値を削除し、再計算します。パフォーマンスへの影響を注意する必要があります。
定義問合せのFROM句にビューまたは副問合せを含むマテリアライズド・ビューは、そのビューを完全にマージできれば、高速リフレッシュできます。マージされるビューの詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。
外部結合がない場合は、WHERE句に任意の絞込み選択および結合を使用できます。
外部結合を含み、集計を含むマテリアライズド・ビューは、外部表のみが変更される場合は、従来のDMLおよびダイレクト・ロードの後に高速リフレッシュできます。また、内部結合表の結合列に一意制約が必要です。外部結合がある場合、すべての結合は、ANDで接続し、等価(=)演算子を使用する必要があります。
CUBE、ROLLUP、グルーピング・セットまたはその連結を含むマテリアライズド・ビューの場合は、次の制限が適用されます。
SELECT構文のリストには、グルーピング識別子を含める必要があります。すなわち、GROUP BYのすべての式にGROUPING_ID関数を含めるか、GROUP BYの各式に対して1つずつGROUPING関数を含める必要があります。たとえば、マテリアライズド・ビューのGROUP BY句が「GROUP BY CUBE(a, b)」の場合、マテリアライズド・ビューを高速リフレッシュ可能にするには、SELECT構文のリストに「GROUPING_ID(a, b)」または「GROUPING(a) AND GROUPING(b)」を含める必要があります。
GROUP BYの結果、グルーピングが重複しないようにします。たとえば、「GROUP BY a, ROLLUP(a, b)」は、グルーピングの結果が「(a), (a, b), AND (a)」と重複するため、高速リフレッシュできません。
UNION ALL集合演算子を含むマテリアライズド・ビューは、次の条件が満たされる場合に、REFRESH FASTオプションをサポートします。
定義問合せの最上位レベルにUNION ALL演算子を含める必要があります。
UNION ALL演算子は、1つの例外を除き、副問合せ内に埋め込むことはできません。この例外とは、定義問合せの形式がSELECT * FROM(UNION ALLを含むビューまたは副問合せ)の場合に、UNION ALLを副問合せのFROM句に指定できるということです。この例を次に示します。
CREATE VIEW view_with_unionall AS (SELECT c.rowid crid, c.cust_id, 2 umarker FROM customers c WHERE c.cust_last_name = 'Smith' UNION ALL SELECT c.rowid crid, c.cust_id, 3 umarker FROM customers c WHERE c.cust_last_name = 'Jones'); CREATE MATERIALIZED VIEW unionall_inside_view_mv REFRESH FAST ON DEMAND AS SELECT * FROM view_with_unionall;
ビューview_with_unionallは、高速リフレッシュの要件を満たしていることに注意してください。
UNION ALL問合せ内の各問合せブロックは、集計を含む高速リフレッシュ可能マテリアライズド・ビューまたは結合を含む高速リフレッシュ可能マテリアライズド・ビューの要件を満たす必要があります。
対応するタイプの高速リフレッシュ可能マテリアライズド・ビューで必要となる、適切なマテリアライズド・ビュー・ログを表に作成する必要があります。
Oracle Databaseでは、SELECT構文のリストとマテリアライズド・ビュー・ログにROWID列が含まれている場合にのみ、結合を含む単一表マテリアライズド・ビューも特殊なケースとして使用できます。これは、ビューview_with_unionallの定義問合せに示されています。
各問合せのSELECT構文のリストにはUNION ALLマーカーを含める必要があり、UNION ALL列の各UNION ALLブランチには個別の定数値または文字列値を含める必要があります。さらに、マーカー列は、各問合せブロックのSELECT構文のリスト内で同じ順序の場所に指定する必要があります。UNION ALLマーカーの詳細は、UNION ALLマーカーとクエリー・リライトを参照してください。
外部結合、挿入専用集計マテリアライズド・ビューの問合せ、リモート表などの機能は、UNION ALLを含むマテリアライズド・ビューではサポートされません。ただし、レプリケーションで使用されるマテリアライズド・ビューで、結合または集計を含まないものは、UNION ALLまたはリモート表が使用されるときに高速リフレッシュが可能です。
UNION ALLを含む高速リフレッシュ可能マテリアライズド・ビューを作成するには、COMPATIBILITY初期化パラメータを9.2.0以上に設定する必要があります。
この章を通して説明しているEXPLAIN_MVIEWプロシージャの他に、DBMS_ADVISOR.TUNE_MVIEWプロシージャを使用してCREATE MATERIALIZED VIEW文を最適化し、REFRESH FASTおよびENABLE QUERY REWRITEという目的を達成できます。
事前作成表のマテリアライズド・ビューのリフレッシュを参照してください。
事前作成オプションで作成されたマテリアライズド・ビューについては、デフォルトでは、索引I_snap$は作成されません。この索引は、高速リフレッシュのパフォーマンスの向上に役立ちます。この索引の作成方法の詳細は、マテリアライズド・ビューに対する索引付けの選択に示されています。
ネステッド・マテリアライズド・ビューは、そのデータが、基になるディテール表のデータと同期されていれば、最新であるとみなされます。これは、一部のディテール表が失効したマテリアライズド・ビューになる可能性がある場合でも同様です。
ネステッド・マテリアライズド・ビューは、2つの方法でリフレッシュできます。nestedフラグをTRUEに設定したDBMS_MVIEW.REFRESHと、nestedフラグをTRUEに設定した、実表に対するREFRESH_DEPENDENTです。DBMS_MVIEW.REFRESHを使用する場合は、マテリアライズド・ビュー・チェーン全体が、指定したマテリアライズド・ビューからトップダウン方式でリフレッシュされます。つまり、指定したマテリアライズド・ビューと、依存階層にあるそのすべての子マテリアライズド・ビューが順番にリフレッシュされます。DBMS_MVIEW.REFRESH_DEPENDENTを使用する場合は、チェーン全体がボトムアップでリフレッシュされます。つまり、依存階層にあるすべての親マテリアライズド・ビューは、指定した表から順番にリフレッシュされます。
例5-7 ネステッド・マテリアライズド・ビューのリフレッシュの例
次に、ネステッド・マテリアライズド・ビューをリフレッシュする文の例を示します。
DBMS_MVIEW.REFRESH('SALES_MV,COST_MV', nested => TRUE);
この文ではまず、依存性分析に基づいてsales_mvおよびcost_mvのすべての子マテリアライズド・ビューがリフレッシュされ、次に、指定された2つのマテリアライズド・ビューがリフレッシュされます。
*_MVIEWSビューのSTALE_SINCE列を問い合せると、マテリアライズド・ビューがいつ失効になったのかを確認できます。
ORDER BY句は、CREATE MATERIALIZED VIEW文で使用できます。これは、マテリアライズド・ビューを最初に作成するときのみ使用されます。完全リフレッシュまたは高速リフレッシュ時には使用されません。
大規模なマテリアライズド・ビューに対する問合せのパフォーマンスを向上させるには、ORDER BY句に指定されている順序で、マテリアライズド・ビューに行を格納します。このように最初に順序付けると、データを物理クラスタ化できます。マテリアライズド・ビューが順序付けられた列上に索引を作成する場合、その索引を使用してマテリアライズド・ビューの行にアクセスすると、物理クラスタ化によるディスクI/Oに対する時間が削減されます。
ORDER BY句は、マテリアライズド・ビューの定義の一部とはみなされません。そのため、Oracle Databaseが様々なタイプのマテリアライズド・ビュー(集計を含まないマテリアライズド結合ビューなど)を検出する方法に違いはありません。同じ理由で、クエリー・リライトは、ORDER BY句の影響を受けません。これは、CREATE TABLE ... ORDER BYの特性と似ています。
マテリアライズド・ビューは、Enterprise Managerのマテリアライズド・ビュー・オブジェクトを選択することで、作成することもできます。この方法が使用された場合でも、必要な情報は同じです。
ある種のマテリアライズド・ビューを使用する場合は、NLSパラメータが作成時と同じに設定されているかどうかを確認する必要があります。この制限を伴うマテリアライズド・ビューは、次のとおりです。
NLSパラメータの設定に応じて異なる値を戻す式。たとえば、(date > "01/02/03")や(rate <= "2.150")は、NLSパラメータに依存する式です。
結合の一方の側が文字データである等価結合。この等価結合の結果は照合に依存します。また、クエリー・リライトの場合に不適切な結果となったり、リフレッシュ操作後にマテリアライズド・ビューの一貫性がなくなるなど、セッションごとに状態が変化することがあります。
マテリアライズド・ビューのSELECT構文のリスト内、または集計を含むマテリアライズド・ビューの集計内で、文字データへの内部変換を生成する式。この制限は、aとbが数値フィールドである場合のa+bのように、数値データのみを伴う式には適用されません。
マテリアライズド・ビューにコメントを追加することができます。
例: マテリアライズド・ビューへのコメントの追加
次の文は、既存のマテリアライズド・ビューに関するコメントをデータ・ディクショナリ・ビューに追加します。
COMMENT ON MATERIALIZED VIEW sales_mv IS 'sales materialized view';
この文の実行後にコメントを表示するには、カタログ・ビューの{USER, DBA} ALL_MVIEW_COMMENTSを問い合せます。たとえば、次の例を考えてみます。
SELECT MVIEW_NAME, COMMENTS FROM USER_MVIEW_COMMENTS WHERE MVIEW_NAME = 'SALES_MV';
出力は次のようになります。
MVIEW_NAME COMMENTS ----------- ----------------------- SALES_MV sales materialized view
注意: COMPATIBILITYが10.0.1以上に設定されている場合、マテリアライズド・ビューのコンテナ表に対してCOMMENT ON TABLEは発行できません。この文を発行すると、次のエラー・メッセージが戻されます。
ORA-12098: cannot comment on the materialized view.
事前作成表にコメントが設定されている場合にマテリアライズド・ビューを作成すると、そのコメントがマテリアライズド・ビューに継承されます。既存のコメントには、'(from table)'という接頭辞が付けられます。たとえば、売上のサマリー情報を格納するための表sales_summaryが作成されているとします。この表には、'Sales summary data'というコメントがすでに関連付けられています。ここで、この事前作成表をコンテナ表として使用する同じ名前のマテリアライズド・ビューを作成するとします。マテリアライズド・ビューの作成後、コメントは'(from table) Sales summary data'になります。
ただし、事前作成表sales_summaryにコメントが設定されていない場合は、'Sales summary data'というコメントが追加されます。その後、このマテリアライズド・ビューを削除すると、そのコメントが'(from materialized view) Sales summary data'となって事前作成表に渡されます。
高速リフレッシュを使用するにはマテリアライズド・ビュー・ログが必要ですが、パーティション・チェンジ・トラッキング・リフレッシュの場合は例外です。つまり、ディテール表でマテリアライズド・ビューのパーティション・チェンジ・トラッキングがサポートされている場合、そのマテリアライズド・ビューを高速リフレッシュするために、そのディテール表にマテリアライズド・ビュー・ログは必要ありません。しかし原則的に、高速リフレッシュを使用する場合は、マテリアライズド・ビュー・ログを作成する必要があります。マテリアライズド・ビュー・ログは、CREATE MATERIALIZED VIEW LOG文を使用して、変更が行われる実表に定義します。対象のマテリアライズド・ビューの上位に別のマテリアライズド・ビューがない場合は、前者のマテリアライズド・ビューにマテリアライズド・ビュー・ログは作成されません。これはネステッド・マテリアライズド・ビューの場合に該当します。マテリアライズド・ビューを高速リフレッシュするには、通常、マテリアライズド・ビュー・ログの定義でROWID句を指定する必要があります。また、集計を含むマテリアライズド・ビューの場合は、マテリアライズド・ビューで参照される表のすべての列、INCLUDING NEW VALUES句およびSEQUENCE句を含む必要があります。集計または結合を含むローカル・マテリアライズド・ビューは、通常、WITH COMMIT SCN句を使用することにより高速リフレッシュのパフォーマンスを向上させることができます。
次のマテリアライズド・ビュー・ログの例は、sales表に対して作成されています。
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
または、次のようにしてコミットSCNベースのマテリアライズド・ビュー・ログを作成することも可能です。
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold), COMMIT SCN INCLUDING NEW VALUES;
混在型のDML操作(複数の表に対するINSERT、UPDATEまたはDELETE操作の組合せ)を実行しないことが確実でないかぎり、キーワードSEQUENCEをマテリアライズド・ビュー・ログ文に含めることをお薦めします。複数の表に対するINSERT文、UPDATE文またはDELETE文の組合せでの高速リフレッシュをサポートするには、マテリアライズド・ビュー・ログにSEQUENCE列が必要です。ただし、マテリアライズド・ビュー・ログの作成後にSEQUENCE列を追加することも可能です。
混在型DML操作の境界は、マテリアライズド・ビューがON COMMITであるかON DEMANDであるかによって決定されます。
ON COMMITの場合、マテリアライズド・ビューのリフレッシュはトランザクションのコミット時に発生するため、混在型DML文は同じトランザクション内で発生します。
ON DEMANDの場合、混在型DML文はリフレッシュとリフレッシュの間に発生します。次のマテリアライズド・ビュー・ログの例は、SEQUENCEキーワードを含むsales表に対して作成されています。
CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
この項には次のトピックが含まれます:
FORCEを指定し、ADD句で指定した項目がすでにそのマテリアライズド・ビュー・ログに指定されていた場合、Oracleはエラーを戻しません。Oracleは、既存の要素をそのまま無視し、ログに存在しない項目をマテリアライズド・ビュー・ログに追加します。たとえば、cust_idなどのフィルタ列を追加したが、この列がすでに存在していた場合、Oracle Databaseはこの重複を無視し、エラーも戻しません。
マテリアライズド・ビュー・ログは、マテリアライズド・ビューのリフレッシュ・プロセス中にパージ(消去)できますが、後でパージすることも可能です。後でパージすれば、リフレッシュのパフォーマンスに影響を与えません。パージを実行するタイミングは、次のようにPURGE句を使用して指定できます。
CREATE MATERIALIZED VIEW LOG ON sales PURGE START WITH sysdate NEXT sysdate+1 WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
次のような文を発行すると、パージ情報についてUSER_MVIEW_LOGSを問い合せることもできます。
SELECT PURGE_DEFERRED, PURGE_INTERVAL, LAST_PURGE_DATE, LAST_PURGE_STATUS FROM USER_MVIEW_LOGS WHERE LOG_OWNER "SH" AND MASTER = 'SALES';
マテリアライズド・ビュー・ログの作成時にパージを設定できるだけでなく、次のような文を発行することにより既存のマテリアライズド・ビュー・ログを変更することもできます。
ALTER MATERIALIZED VIEW LOG ON sales PURGE IMMEDIATE;
関連項目:
マテリアライズド・ビュー・ログの構文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
一部のデータ・ウェアハウスでは、通常のユーザー表にマテリアライズド・ビューが実装されています。このソリューションによって、マテリアライズド・ビューのパフォーマンスが向上しますが、次のような問題があります。
すべてのSQLアプリケーションで、クエリー・リライトができるわけではありません。
あるアプリケーションで定義されたマテリアライズド・ビューには、別のアプリケーションから透過的にアクセスできません。
一般に、高速パラレルまたは高速マテリアライズド・ビュー・リフレッシュはサポートされていません。
これらの制限があり、既存のマテリアライズド・ビューが非常に大きく、再作成にコストがかかりすぎる場合があるため、できるだけ、既存のマテリアライズド・ビューの表を登録する必要があります。ユーザー定義のマテリアライズド・ビューは、CREATE MATERIALIZED VIEW ... ON PREBUILT TABLE文で登録できます。一度登録されたマテリアライズド・ビューでは、クエリー・リライトが使用できるか、いずれかのリフレッシュ方法でメンテナンスできるか、あるいはその両方が可能です。
表の内容は、定義問合せをマテリアライズド・ビューとして登録したときに、定義問合せのマテリアライズ化を反映している必要があります。また、定義問合せの各列は、一致するデータ型を持つ表の列に対応している必要があります。ただし、WITH REDUCED PRECISIONを指定して、定義問合せの列の精度が表の列の精度とは異なるようにすることは可能です。
表およびマテリアライズド・ビューの名前は同じである必要がありますが、表は、表としての個別性を維持し、マテリアライズド・ビューの定義問合せで参照されない列を含むことができます。このような列は、非管理列と呼ばれます。リフレッシュ操作中に行が挿入されると、その行の各非管理列はそのデフォルト値に設定されます。したがって、非管理列は、デフォルト値を持たないかぎり、NOT NULL制約を持つことはできません。
事前作成表に基づくマテリアライズド・ビューは、パラメータQUERY_REWRITE_INTEGRITYがSTALE_TOLERATEDまたはTRUSTEDに設定されている場合に、クエリー・リライトによる選択の対象になります。
関連項目:
整合性レベルの詳細は、マテリアライズド・ビューのための基本的なクエリー・リライトを参照してください。
事前作成表に作成されたマテリアライズド・ビューを削除しても、その表は残り、マテリアライズド・ビューのみが削除されます。
次の例では、ユーザー定義表の登録に必要な2つのステップを示しています。まず、表が作成され、次にマテリアライズド・ビューが表と同じ名前で定義されます。このマテリアライズド・ビューsum_sales_tab_mvは、クエリー・リライトで使用できます。
CREATE TABLE sum_sales_tab
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 8M) AS
SELECT s.prod_id, SUM(amount_sold) AS dollar_sales,
SUM(quantity_sold) AS unit_sales
FROM sales s GROUP BY s.prod_id;
CREATE MATERIALIZED VIEW sum_sales_tab_mv
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
ENABLE QUERY REWRITE AS
SELECT s.prod_id, SUM(amount_sold) AS dollar_sales,
SUM(quantity_sold) AS unit_sales
FROM sales s GROUP BY s.prod_id;
この表は領域を節約するために圧縮することもできます。
ユーザー定義のマテリアライズド・ビューは、データの更新サイクルより長いスケジュールでリフレッシュされる場合があります。たとえば、月単位のマテリアライズド・ビューは各月の月末にしか更新されないことがあります。また、マテリアライズド・ビューの値は、常に、リフレッシュが完了した期間のみを参照します。これらのマテリアライズド・ビューに対して直接作成されたレポートでは、現行の(リフレッシュが未完了の)期間にはないデータのみが暗黙的に選択されます。ユーザー定義のマテリアライズド・ビューに時間ディメンションがすでに含まれる場合、次のことに従う必要があります。
ユーザー定義のマテリアライズド・ビューは、登録してから、更新サイクルごとに高速リフレッシュする必要があります。
リフレッシュが完了した期間を選択するビューを作成できます。
レポートが、ユーザー定義のマテリアライズド・ビューを直接参照するのではなく、ビューを参照するように変更する必要があります。
ユーザー定義のマテリアライズド・ビューに時間ディメンションが含まれていない場合、時間ディメンションを含む新しいマテリアライズド・ビューを作成する必要があります(可能な場合)。また、この場合、新しいマテリアライズド・ビューの時間列を、ビューに集計する必要があります。
マテリアライズド・ビューに対して行う最も一般的な操作は、問合せの実行および高速リフレッシュですが、各操作には、異なるパフォーマンス要件があります。問合せの実行では、マテリアライズド・ビュー・キー列のすべてのサブセットがアクセスされる必要があり、これらの列のサブセット上で結合および集計が行われる必要がある場合があります。そのため、問合せの実行では、通常、各マテリアライズド・ビュー・キー列上に単一列のビットマップ索引が定義されている場合に、パフォーマンスが最適化されます。
結合のみを含むマテリアライズド・ビューに高速リフレッシュ・オプションを使用する場合は、ROWIDを含む列上に索引を作成して、リフレッシュ操作のパフォーマンスを向上させることをお薦めします。
集計を使用するマテリアライズド・ビューが高速リフレッシュ可能な場合は、CREATE MATERIALIZED VIEW文にUSING NO INDEXが指定されていないかぎり、高速リフレッシュ・プロシージャに適切な索引が作成されます。
マテリアライズド・ビューがパーティション化されている場合、マテリアライズド・ビューに対するパーティション・メンテナンス操作の実行後は索引が使用できなくなるため、高速リフレッシュを行うには索引を再作成する必要があります。
事前作成オプションでマテリアライズド・ビューを作成した場合、I_snap$索引は自動的に作成されません。この索引を使用すると、高速リフレッシュのパフォーマンスが大幅に向上し、次のような文を発行することにより、手動で作成することができます。
CREATE UNIQUE INDEX <OWNER>."I_SNAP$_<MVIEW_NAME>" ON <OWNER>.<MVIEW_NAME>
(SYS_OP_MAP_NONNULL("LOG_DATE"))
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE <TABLESPACE_NAME>;
関連項目:
SQLアクセス・アドバイザを使用してマテリアライズド・ビューに最適な索引を判断する方法については、『Oracle Database SQLチューニング・ガイド』を参照してください。
マテリアライズド・ビューを削除するには、DROP MATERIALIZED VIEW文を使用します。たとえば、次の文を考えてみます。
DROP MATERIALIZED VIEW sales_sum_mv;
この文では、マテリアライズド・ビューsales_sum_mvが削除されます。ある表に対して作成されていたマテリアライズド・ビューが削除されても、元の表は削除されませんが、リフレッシュ・メカニズム機能を使用したメンテナンスやクエリー・リライトによる使用はできなくなります。また、Oracle Enterprise Managerを使用して、マテリアライズド・ビューを削除できます。
DBMS_MVIEW.EXPLAIN_MVIEWプロシージャを使用すると、マテリアライズド・ビューまたは作成前のマテリアライズド・ビューで可能なことを調べることができます。特に、このプロシージャにより次のことを判断できます。
マテリアライズド・ビューが高速リフレッシュ可能かどうか
このマテリアライズド・ビューで実行できるクエリー・リライトのタイプ
パーティション・チェンジ・トラッキング・リフレッシュの可否
このプロシージャを使用する方法が最も簡単で、DBMS_MVIEW.EXPLAIN_MVIEWプロシージャの使用で説明しています。DBMS_MVIEW.EXPLAIN_MVIEWをコールし、既存のマテリアライズド・ビューのスキーマ名とマテリアライズド・ビュー名を単一パラメータとして渡すのみで済みます。あるいは、作成前のマテリアライズド・ビューについてはSELECT文字列を指定したり、完全なCREATE MATERIALIZED VIEW文を指定したりできます。マテリアライズド・ビューまたは作成前のマテリアライズド・ビューが分析され、結果がデフォルトの表MV_CAPABILITIES_TABLEまたは配列MSG_ARRAYに書き込まれます。
結果をMSG_ARRAYに入れるとき以外は、EXPLAIN_MVIEWをコールする前にutlxmv.sqlスクリプトを実行する必要があるので注意してください。このスクリプトはadminディレクトリにあります。このスクリプトは、現在のスキーマにMV_CAPABILITIES_TABLEを作成します。各種機能の説明は表5-6、可能なすべてのメッセージは表5-7を参照してください。
stmt_id
オプション・パラメータ。出力行を特定のEXPLAIN_MVIEWコールと関連付けるためにクライアントが提供する一意識別子です。
mv
分析対象となる既存のマテリアライズド・ビューの名前、あるいは作成前のマテリアライズド・ビューの問合せ定義またはCREATE MATERIALIZED VIEW文全体。
msg-array
出力を受け取るPL/SQLのVARRAY。
EXPLAIN_MVIEWでは、指定したマテリアライズド・ビューのリフレッシュ機能とリライト機能が分析され、その結果が(複数行形式で)MV_CAPABILITIES_TABLEまたはMSG_ARRAYに挿入されます。
関連項目:
DBMS_MVIEWパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
この項には次のトピックが含まれます:
次のPL/SQL宣言はDBMS_MVIEWパッケージで行われるもので、結果を表またはVARRAYに出力する場合の、既存のマテリアライズド・ビューと作成前のマテリアライズド・ビューを説明するパラメータの順序とデータ型を示します。
MV_CAPABILITIES_TABLEへの出力を指定して、既存または作成前のマテリアライズド・ビューを説明します。
DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2,
stmt_id IN VARCHAR2:= NULL);
VARRAYへの出力を指定して、既存または作成前のマテリアライズド・ビューを説明します。
DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2,
msg_array OUT SYS.ExplainMVArrayType);
DBMS_MVIEW.EXPLAIN_MVIEWの最も単純な使用方法の1つは、次の構造を持つ、MV_CAPABILITIES_TABLEを使用することです。
CREATE TABLE MV_CAPABILITIES_TABLE
(STATEMENT_ID VARCHAR(30), -- Client-supplied unique statement identifier
MVOWNER VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
MVNAME VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW
CAPABILITY_NAME VARCHAR(30), -- A descriptive name of the particular
-- capability:
-- REWRITE
-- Can do at least full text match
-- rewrite
-- REWRITE_PARTIAL_TEXT_MATCH
-- Can do at least full and partial
-- text match rewrite
-- REWRITE_GENERAL
-- Can do all forms of rewrite
-- REFRESH
-- Can do at least complete refresh
-- REFRESH_FROM_LOG_AFTER_INSERT
-- Can do fast refresh from an mv log
-- or change capture table at least
-- when update operations are
-- restricted to INSERT
-- REFRESH_FROM_LOG_AFTER_ANY
-- can do fast refresh from an mv log
-- or change capture table after any
-- combination of updates
-- PCT
-- Can do Enhanced Update Tracking on
-- the table named in the RELATED_NAME
-- column. EUT is needed for fast
-- refresh after partitioned
-- maintenance operations on the table
-- named in the RELATED_NAME column
-- and to do non-stale tolerated
-- rewrite when the mv is partially
-- stale with respect to the table
-- named in the RELATED_NAME column.
-- EUT can also sometimes enable fast
-- refresh of updates to the table
-- named in the RELATED_NAME column
-- when fast refresh from an mv log
-- or change capture table is not
-- possible.
-- See Table 5-6
POSSIBLE CHARACTER(1), -- T = capability is possible
-- F = capability is not possible
RELATED_TEXT VARCHAR(2000), -- Owner.table.column, alias name, and so on
-- related to this message. The specific
-- meaning of this column depends on the
-- NSGNO column. See the documentation for
-- DBMS_MVIEW.EXPLAIN_MVIEW() for details.
RELATED_NUM NUMBER, -- When there is a numeric value
-- associated with a row, it goes here.
MSGNO INTEGER, -- When available, QSM message # explaining
-- why disabled or more details when
-- enabled.
MSGTXT VARCHAR(2000), -- Text associated with MSGNO.
SEQ NUMBER); -- Useful in ORDER BY clause when
-- selecting from this table.
MV_CAPABILITIES_TABLEを作成するには、adminディレクトリにあるutlxmv.sqlスクリプトを使用できます。
関連項目:
パーティション・チェンジ・トラッキングの詳細は、マテリアライズド・ビューのリフレッシュを参照してください。
パーティション・チェンジ・トラッキングの詳細は、マテリアライズド・ビューのための高度なクエリー・リライトを参照してください。
例5-8 DBMS_MVIEW.EXPLAIN_MVIEW
まず、マテリアライズド・ビューを作成します。あるいは、作成前のマテリアライズド・ビューのSELECT文またはCREATE MATERIALIZED VIEW文全体を使用して、そのマテリアライズド・ビューにEXPLAIN_MVIEWを実行することもできます。
CREATE MATERIALIZED VIEW cal_month_sales_mv BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars FROM sales s, times t WHERE s.time_id = t.time_id GROUP BY t.calendar_month_desc;
次に、マテリアライズド・ビューを指定してEXPLAIN_MVIEWを起動します。各行が論理順に表示されるように、ORDER BY句にSEQ列を使用する必要があります。機能が使用可能でない場合は、P列にN、MSGTXT列に説明が表示されます。複数の理由で機能が使用可能でない場合は、理由ごとに表示されます。
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');
SELECT capability_name, possible, SUBSTR(related_text,1,8)
AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY seq;
CAPABILITY_NAME P REL_TEXT MSGTXT
--------------- - -------- ------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N SALES no partition key or PMARKER in select list
PCT_TABLE N TIMES relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have new values
REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have ROWID
REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log does not have all necessary columns
REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have new values
REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have ROWID
REFRESH_FAST_AFTER_INSERT N SH.SALES mv log does not have all necessary columns
REFRESH_FAST_AFTER_ONETAB_DML N DOLLARS SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why
REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why
REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_AFTER_ANY_DML N SH.TIMES mv log must have sequence
REFRESH_FAST_AFTER_ANY_DML N SH.SALES mv log must have sequence
REFRESH_PCT N PCT is not possible on any of the detail
tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N PCT is not possible on any detail tables
表5-6に、CAPABILITY_NAME列の値の説明を示します。
表5-6 CAPABILITY_NAME列の詳細
| CAPABILITY_NAME | 説明 |
|---|---|
|
この機能が使用可能な場合は、1つ以上のディテール関係のパーティション・チェンジ・トラッキングが可能です。この機能が使用可能でない場合、マテリアライズド・ビューで参照されるディテール関係のパーティション・チェンジ・トラッキングは不可能です。 |
|
この機能が使用可能な場合は、マテリアライズド・ビューの完全リフレッシュが可能です。 |
|
この機能が使用可能な場合は、少なくとも特定の状況下での高速リフレッシュが可能です。 |
|
この機能が使用可能な場合は、少なくともテキストの完全一致のクエリー・リライトが可能です。この機能が使用可能でない場合、クエリー・リライトは形式を問わず不可能です。 |
|
この機能が使用可能な場合は、トップ・レベルの PCTは、 また、PCTはマテリアライズド・ビュー・ログからの高速リフレッシュが不可能な場合でも、 また、PCTは、 この機能が使用不可能な場合、PCTは |
|
この機能が使用可能な場合は、トップ・レベルの この機能は、 この機能が使用不可能で、このマテリアライズド・ビューが、 |
|
この機能が使用可能であれば、少なくとも更新が |
|
この機能が使用可能であれば、すべての更新操作が単一表に対して実行される場合は、更新操作のタイプを問わずマテリアライズド・ビュー・ログからの高速リフレッシュが可能です。この機能が使用可能でなければ、更新操作が複数の表に対して実行される場合、マテリアライズド・ビュー・ログからの高速リフレッシュが不可能な場合があります。 |
|
この機能が使用可能であれば、更新操作のタイプや更新される表の数を問わず、マテリアライズド・ビュー・ログからの高速リフレッシュが可能です。この機能が使用可能でなければ、更新操作( |
|
この機能が使用可能な場合は、PCTを使用した高速リフレッシュが可能です。通常、これは、PCTが可能として示されているディテール表に対するパーティション・メンテナンス操作後に、リフレッシュが可能であることを意味します。 |
|
この機能が使用可能な場合は、テキストの完全一致のクエリー・リライトが可能です。この機能が使用可能でない場合、テキストの完全一致のクエリー・リライトは不可能です。 |
|
この機能が使用可能な場合は、少なくともテキストの完全一致および部分一致のクエリー・リライトが可能です。この機能が使用可能でない場合、少なくともテキストの部分一致と一般的なクエリー・リライトは不可能です。 |
|
この機能が使用可能な場合は、一般的なクエリー・リライトと、テキストの完全一致および部分一致のクエリー・リライトを含め、クエリー・リライト機能がすべて使用可能です。この機能が使用可能でない場合、少なくとも一般的なクエリー・リライトは不可能です。 |
|
この機能が使用可能な場合、クエリー・リライトでは、 |
表5-7に、RELATED_TEXTおよびRELATED_NUM列の意味を示します。
表5-7 MV_CAPABILITIES_TABLE列の詳細
| MSGNO | MSGTXT | RELATED_NUM | RELATED_TEXT |
|---|---|---|---|
|
|
PCT機能専用。PCTが使用可能になっている表の |
|
2066 |
Oracleエラー内に記載されます |
発生したOracleエラー番号 |
|
2067 |
|
PCTがサポートされていない関係の |
|
2068 |
リレーションがパーティション化されていません |
PCTがサポートされていない関係の |
|
2069 |
PCTでは複数列で構成されるパーティション・キーをサポートしていません |
PCTがサポートされていない関係の |
|
2070 |
PCTではこのタイプのパーティション化をサポートしていません |
PCTがサポートされていない関係の |
|
2071 |
内部エラー: PCT障害コードが定義されていません |
認識されない数値PCT障害コード |
PCTがサポートされていない関係の |
2072 |
ネストされたマテリアライズド・ビューの高速リフレッシュのための要件は満たされていません |
||
2077 |
マテリアライズド・ビュー・ログは最新の全体リフレッシュよりも新しいです |
マテリアライズド・ビュー・ログが必要な表の |
|
2078 |
マテリアライズド・ビュー・ログは新しい値を持つ必要があります |
マテリアライズド・ビュー・ログが必要な表の |
|
2079 |
マテリアライズド・ビュー・ログは |
マテリアライズド・ビュー・ログが必要な表の |
|
2080 |
マテリアライズド・ビュー・ログは主キーを持つ必要があります |
マテリアライズド・ビュー・ログが必要な表の |
|
2081 |
マテリアライズド・ビュー・ログは必要な列をすべて持っているわけではありません |
マテリアライズド・ビュー・ログが必要な表の |
|
2082 |
マテリアライズド・ビュー・ログの問題 |
マテリアライズド・ビュー・ログが必要な表の |
|
2099 |
マテリアライズド・ビューは |
|
問題の表またはビューの |
2126 |
複数のマスター・サイトです |
最初の異なるノードの名前、または、最初の異なるノードがローカルの場合は |
|
2129 |
結合またはフィルタ条件が複合しています |
結合またはフィルタ条件に関連する表の |
|
2130 |
式が高速リフレッシュのためにサポートされていません |
|
問題の式の |
2150 |
|
|
|
2182 |
PCTは結合依存性を介して使用できます |
|
|
2183 |
PCTを有効化する式が分析関数の |
認識されない数値PCT障害コード |
PCTが使用可能になっていない関係の |
2184 |
PCTを有効化する式はロールアップできません |
PCTが使用可能になっていない関係の |
|
2185 |
|
|
|
2186 |
|
||
2187 |
外部表のマテリアライズド・ビュー |
