11 マテリアライズド・ビューのための基本的なクエリー・リライト
この章では、Oracleのクエリー・リライトについて説明します。次の内容が含まれます。
11.1 クエリー・リライトの概要
実表にデータが大量に格納されていると、必要な集計やこれらの表間の結合の計算にコストや時間が多くかかります。こうした場合の問合せは数分から数時間を要することもあります。マテリアライズド・ビューにはすでに計算された集計と結合が含まれているため、Oracle Databaseでは、クエリー・リライトと呼ばれる非常に強力なプロセスが採用されており、マテリアライズド・ビューを使用した問合せに迅速に応答します。
クエリー・リライトが利用可能になることは、マテリアライズド・ビューを作成しメンテナンスすることで得られる主要なメリットの1つです。クエリー・リライトでは、複数の表やビューに対するSQL文を、ディテール表に定義された1つ以上のマテリアライズド・ビューに対してアクセスする文に変換します。この変換はエンド・ユーザーやアプリケーションに対して透過的に処理され、SQL文内のマテリアライズド・ビューに対する介入や参照は不要です。クエリー・リライトは透過的な処理なので、マテリアライズド・ビューは、アプリケーション・コード内のSQLを無効にせず索引のように追加したり削除したりできます。Oracleによるクエリー・リライト条件では、リライト対象の問合せが満たす必要がある条件について説明します。
11.1.1 クエリー・リライトとオプティマイザについて
問合せは、その問合せにクエリー・リライトが必要かどうかを判断するチェックを受けます。チェック結果に問題があると、その問合せはマテリアライズド・ビューではなく、ディテール表に対する問合せになります。リライトできないと、応答時間や処理能力についての効率が低下する場合があります。
オプティマイザでは、マテリアライズド・ビューが関わる問合せをリライトする場合の判断に2つの方法が使用されます。最初の方法で、問合せのSQLテキストとマテリアライズド・ビュー定義のSQLテキストが照合されます。この方法で判断できない場合、問合せとマテリアライズド・ビューの結合、選択、データ列、グルーピング列および集計関数を比較するという、より一般的な方法が取られます。
クエリー・リライトは、次のSQL文の問合せおよび副問合せに対応します。
-
SELECT
-
CREATE TABLE … AS SELECT
-
INSERT INTO … SELECT
また、集合演算子UNION
、UNION ALL
、, INTERSECT
、MINUS
、およびDML文の副問合せ(INSERT
、DELETE
、UPDATE
など)にも対応します。
マテリアライズド・ビューを使用するように問合せがリライトされるかどうかには、ディメンション、制約およびリライトの整合性レベルが影響します。また、問合せのリライトはREWRITE
ヒントやNOREWRITE
ヒント、およびQUERY_REWRITE_ENABLED
セッション・パラメータを使用して有効化したり無効化したりできます。
問合せでクエリー・リライトが可能かどうか、また可能な場合はどのマテリアライズド・ビューが使用されるかについては、DBMS_MVIEW.EXPLAIN_REWRITE
プロシージャで示されます。また、このプロシージャでは問合せをリライトできない理由もわかります。
11.1.2 Oracleによるクエリー・リライト条件
問合せは、次の一定の条件が満たされた場合にのみリライトされます。
-
セッションで、クエリー・リライトが使用可能である必要があります。
-
マテリアライズド・ビューに対するクエリー・リライトが使用可能である必要があります。
-
リライトの整合性レベルが、マテリアライズド・ビューの使用を許可している必要があります。たとえば、マテリアライズド・ビューが最新のものではなく、かつクエリー・リライトの整合性が
ENFORCED
に設定されている場合、マテリアライズド・ビューは使用できません。 -
問合せの要求結果の一部またはすべてが、1つ以上のマテリアライズド・ビューに格納されている、事前計算された結果から取得可能である必要があります。
オプティマイザでは、こうした条件のテストに、ユーザーが制約やディメンションを使用して宣言したデータ関係が使用される場合があります。そうしたデータ関係には、階層、参照整合性、キー・データの一意性などがあります。
11.2 クエリー・リライトの有効化
DBMS_ADVISOR.TUNE_MVIEW
プロシージャを使用すると、CREATE
MATERIALIZED
VIEW
文を最適化して、一般的なQUERY
REWRITE
を使用可能にできます。
11.2.1 マテリアライズド・ビューのクエリー・リライトの有効化
ENABLE
QUERY
REWRITE
は、ALTER
MATERIALIZED
VIEW
文を使用して指定するか、マテリアライズド・ビューの作成時に次のように指定できます。
CREATE MATERIALIZED VIEW join_sales_time_product_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day, s.channel_id, s.promo_id, s.cust_id, s.amount_sold FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id;
NOREWRITE
ヒントを使用すると、QUERY_REWRITE_ENABLED
パラメータをオーバーライドして、SQL文のクエリー・リライトを使用禁止にできます。REWRITE
ヒント(mv_name
と併用)を使用すると、クエリー・リライトを使用できるマテリアライズド・ビューを、ヒントで指定するビューのみに制限できます。
11.2.2 クエリー・リライトの初期化パラメータについて
クエリー・リライトの動作は、特定のデータベース初期化パラメータによって制御されます。
表11-1 クエリー・リライト動作を制御する初期化パラメータ
初期化パラメータ名 | 初期化パラメータ値 | クエリー・リライトの動作 |
---|---|---|
OPTIMIZER_MODE |
ALL_ROWS (デフォルト)、FIRST_ROWS またはFIRST_ROWS_ n |
|
QUERY_REWRITE_ENABLED |
TRUE (デフォルト)、FALSE またはFORCE |
このオプションを使用すると、オプティマイザのクエリー・リライト機能が有効化されてマテリアライズド・ビューを利用できるようになり、パフォーマンスが向上します。
|
QUERY_REWRITE_INTEGRITY |
STALE_TOLERATED 、TRUSTED またはENFORCED (デフォルト)
|
このパラメータはオプションです。ただし、設定されている場合は、「初期化パラメータ値」列に指定されている値のいずれかを指定する必要があります。 デフォルトでは、整合性レベルは |
関連トピック
11.2.3 クエリー・リライトの制御
マテリアライズド・ビューをクエリー・リライトに使用できるのは、ENABLE
QUERY
REWRITE
句が指定されている場合のみです。この句はマテリアライズド・ビューを最初に作成するときに指定するか、後でALTER
MATERIALIZED
VIEW
文を使用して指定します。
前述のセッション・パラメータは、すべてのセッションについて、ALTER
SYSTEM
SET
文を使用して設定でき、また初期化ファイルでも設定できます。所定のユーザー・セッションでALTER
SESSION
を使用すると、そのセッションのみでクエリー・リライトの使用を禁止または可能にできます。次に例を示します。
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
クエリー・リライトのレベルはセッションごとに設定できるので、各ユーザーが異なる整合性レベルで作業できます。次のような文を使用できます。
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED; ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED;
11.2.4 クエリー・リライトの精度について
クエリー・リライトは、初期化パラメータQUERY_REWRITE_INTEGRITY
によって制御される3つのレベルのリライト整合性を提供します。
QUERY_REWRITE_INTEGRITY
パラメータに対して設定できる値は、次のとおりです。
-
ENFORCED
これがデフォルト・モードです。オプティマイザでは、マテリアライズド・ビューの最新データのみが使用され、
ENABLED VALIDATED
になっている主/一意/外部キー制約に基づいた関係のみが使用されます。 -
TRUSTED
TRUSTED
モードの場合、オプティマイザでは、ディメンションで宣言された関係およびRELY
制約が適切であることが信頼の対象になります。このモードでは、事前作成マテリアライズド・ビューや、ビューに基づくマテリアライズド・ビューが使用され、施行された関係と同様に施行されていない関係も使用されます。また、宣言されたがENABLED VALIDATED
でない主/一意キー制約、およびディメンションを使用して指定されたデータ関係も信頼されます。このモードではより高度なクエリー・リライト機能を使用できますが、ユーザーが宣言し、信頼された関係に不正確なものがあった場合、不正確な結果が生成される危険性もあります。 -
STALE_TOLERATED
STALE_TOLERATED
モードの場合、オプティマイザでは最新データを含むマテリアライズド・ビューの他に、有効だが失効データを含むマテリアライズド・ビューも使用されます。このモードでは、リライト機能を最大限に使用できますが、不正確な結果が生成される危険性もあります。
リライト整合性が最も安全なレベルであるENFORCED
に設定されている場合、オプティマイザでは、問合せの結果がディテール表に直接アクセスした場合と同じであることを保証するために、施行された主キー制約および参照整合性制約のみが使用されます。
リライト整合性をENFORCED
以外のレベルに設定すると、次のような状況において、リライトした場合とリライトしなかった場合の出力が異なることがあります。
-
マテリアライズド・ビューが、データのマスター・コピーと同期されていない場合。これは、通常、マテリアライズド・ビューの1つ以上のディテール表に対するバルク・ロードまたはDML操作の後に、マテリアライズド・ビューのリフレッシュ・プロシージャが保留状態にあるために発生します。データ・ウェアハウス・サイトによっては、この状況が最適な場合もあります。これは、一部のマテリアライズド・ビューでは一定の間隔でリフレッシュされることが一般的であるためです。
-
ディメンション・オブジェクトに含まれる関係が無効の場合。たとえば、階層内のあるレベルの値が、正確に1つの親の値にロールアップされないことがあります。
-
事前作成マテリアライズド・ビュー表に格納された値が不適切な場合。
-
施行されていない表またはビューの制約により不正なデータ関係が定義されているため、間違った答えが生じている場合。
QUERY_REWRITE_INTEGRITY
は、初期化パラメータ・ファイルで設定するか、ALTER SYSTEM
文またはALTER SESSION
文を使用して設定できます。
11.2.5 クエリー・リライトの有効化の権限について
マテリアライズド・ビューの使用は、そのマテリアライズド・ビューに対してユーザーが保持する権限ではなく、問合せ内のディテール表またはビューに対してユーザーが保持する権限に基づきます。
GRANT
QUERY
REWRITE
システム権限では、自スキーマ内のマテリアライズド・ビューから直接参照される表がすべて自スキーマ内にある場合にのみ、そのマテリアライズド・ビューに対するクエリー・リライトが有効になります。GRANT
GLOBAL
QUERY
REWRITE
権限では、マテリアライズド・ビューが別のスキーマ内のオブジェクトを参照する場合にも、マテリアライズド・ビューに対するクエリー・リライトが有効になります。また、自スキーマの外部にある表およびビューの場合、QUERY
REWRITE
オブジェクト権限を使用することもできます。
クエリー・リライト用にマテリアライズド・ビューを使用するための権限は、定義者権限のプロシージャに対する権限と似ています。
11.2.6 サンプル・スキーマおよびマテリアライズド・ビュー
次の項では、sh
サンプル・スキーマおよびいくつかのマテリアライズド・ビューを使用して、オプティマイザでデータ関係を利用してクエリー・リライトが行われる仕組みを説明します。
この章におけるクエリー・リライトの例では、主として次に示すマテリアライズド・ビューを参照します。これらのマテリアライズド・ビューは、必ずしもsh
スキーマの最も効率的な実装を表しているわけではありません。リライト機能を表すためのベースにすぎません。この章には、特定の機能についての例も記載されています。
次に示すのは、結合および集計を含むマテリアライズド・ビューです。
CREATE MATERIALIZED VIEW sum_sales_pscat_week_mv ENABLE QUERY REWRITE AS SELECT p.prod_subcategory, t.week_ending_day, SUM(s.amount_sold) AS sum_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 p.prod_subcategory, t.week_ending_day; CREATE MATERIALIZED VIEW sum_sales_prod_week_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, t.week_ending_day, s.cust_id, SUM(s.amount_sold) AS sum_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 p.prod_id, t.week_ending_day, s.cust_id; CREATE MATERIALIZED VIEW sum_sales_pscat_month_city_mv ENABLE QUERY REWRITE AS SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold, COUNT(s.amount_sold) AS count_amount_sold FROM sales s, products p, times t, customers c WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND s.cust_id=c.cust_id GROUP BY p.prod_subcategory, t.calendar_month_desc, c.cust_city;
次に示すのは、結合のみを含むマテリアライズド・ビューです。
CREATE MATERIALIZED VIEW join_sales_time_product_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day, s.channel_id, s.promo_id, s.cust_id, s.amount_sold FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id; CREATE MATERIALIZED VIEW join_sales_time_product_oj_mv ENABLE QUERY REWRITE AS SELECT p.prod_id, p.prod_name, t.time_id, t.week_ending_day, s.channel_id, s.promo_id, s.cust_id, s.amount_sold FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id(+);
これは必須ではありませんが、できるだけマテリアライズド・ビューに関する統計情報を収集して、問合せをリライトするかどうかをオプティマイザで判断できるようにすることをお薦めします。収集する場合、オブジェクトごとに収集するか、統計情報のない新規に作成したオブジェクトすべてを対象として収集するかを選択できます。オブジェクトごとの場合を次のjoin_sales_time_product_mv
の例で示します。
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ( - 'SH','JOIN_SALES_TIME_PRODUCT_MV', estimate_percent => 20, - block_sample => TRUE, cascade => TRUE);
次に、統計情報のない新規作成したオブジェクトすべてを収集対象にする場合の例を示します。
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ( 'SH', - options => 'GATHER EMPTY', - estimate_percent => 20, block_sample => TRUE, - cascade => TRUE);
11.2.7 クエリー・リライトの発生を確認する方法
クエリー・リライトは透過的に行われるので、問合せがリライトされたかどうかを確認するには、特別なステップを実行する必要があります。問合せが高速に実行された場合、リライトが発生したと考えられますが、これは確認にはなりません。そのため、EXPLAIN
PLAN
文またはDBMS_MVIEW.EXPLAIN_REWRITE
プロシージャを使用して、クエリー・リライトが発生したことを確認します。詳細は、クエリー・リライトが発生したことの確認を参照してください。
11.3 クエリー・リライトの例
このような例からも、マテリアライズド・ビューを使用したクエリー・リライトの効果は明らかです。
次のcal_month_sales_mv
というマテリアライズド・ビューの場合を考えてみます。このマテリアライズド・ビューを使用すると、月ごとの販売額(ドル)の合計を表示できます。
CREATE MATERIALIZED VIEW cal_month_sales_mv
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;
たとえば、その店舗における販売数量が通常の月では100万前後だとします。この場合、このマテリアライズド集計ビューには、事前に計算された月ごとの合計販売額(ドル)が用意されます。
次の問合せについて考えてみます。この問合せは、その店舗における会計月ごとの総販売数量を問い合せるためのものです。
SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM sales s, times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;
前述のマテリアライズド・ビューおよびクエリー・リライト機能がない場合、Oracle Databaseがsales
表に直接アクセスし、総販売数量を計算した上でその結果を返します。その場合、sales
表から膨大な数の行が読み込まれ、対象となるディスクへのアクセスに伴って問合せの応答時間は必ず増大します。また、問合せに結合があるので、膨大な数の行に対する結合の計算が必要になり、問合せへの応答はさらに遅くなります。
ここで、マテリアライズド・ビューcal_month_sales_mv
があると、クエリー・リライトによって前述の問合せが透過的にリライトされ、次の問合せに書き換えられます。
SELECT calendar_month, dollars
FROM cal_month_sales_mv;
マテリアライズド・ビューcal_month_sales_mv
の行数はほんの数十行で、結合も存在しないため、Oracle Databaseにより結果は即座に戻されます。