| Oracle® Databaseデータ・ウェアハウス・ガイド 12c リリース1 (12.1) B71318-06 |
|
 前 |
 次 |
この章では、Oracleのクエリー・リライトについて説明します。次の内容が含まれます。
実表にデータが大量に格納されていると、必要な集計やこれらの表間の結合の計算にコストや時間が多くかかります。こうした場合の問合せは数分から数時間を要することもあります。マテリアライズド・ビューにはすでに計算された集計と結合が含まれているため、Oracle Databaseでは、クエリー・リライトと呼ばれる非常に強力なプロセスが採用されており、マテリアライズド・ビューを使用した問合せに迅速に応答します。
クエリー・リライトが利用可能になることは、マテリアライズド・ビューを作成しメンテナンスすることで得られる主要なメリットの1つです。クエリー・リライトでは、複数の表やビューに対するSQL文を、ディテール表に定義された1つ以上のマテリアライズド・ビューに対してアクセスする文に変換します。この変換はエンド・ユーザーやアプリケーションに対して透過的に処理され、SQL文内のマテリアライズド・ビューに対する介入や参照は不要です。クエリー・リライトは透過的な処理なので、マテリアライズド・ビューは、アプリケーション・コード内のSQLを無効にせず索引のように追加したり削除したりできます。Oracleによるクエリー・リライト条件では、リライト対象の問合せが満たす必要がある条件について説明します。
問合せは、その問合せにクエリー・リライトが必要かどうかを判断するチェックを受けます。チェック結果に問題があると、その問合せはマテリアライズド・ビューではなく、ディテール表に対する問合せになります。この形式は、応答時間や処理能力の点で効率的でない場合があります。
オプティマイザでは、マテリアライズド・ビューが関わる問合せをリライトする場合の判断に2つの方法が使用されます。はじめに、問合せのSQLテキストとマテリアライズド・ビュー定義のSQLテキストとの一致により判断する方法が使用されます。この方法で判断できない場合、問合せとマテリアライズド・ビューの結合、選択、データ列、グルーピング列および集計関数を比較するという、より一般的な方法が取られます。
クエリー・リライトは、次のSQL文の問合せおよび副問合せに対応します。
SELECT
CREATE TABLE … AS SELECT
INSERT INTO … SELECT
クエリー・リライトは、集合演算子UNION、UNION ALL、INTERSECT、MINUS、およびDML文の副問合せ(INSERT、DELETE、UPDATEなど)にも対応します。
特定の問合せをリライトして、1つ以上のマテリアライズド・ビューを使用するようにするかどうかには、ディメンション、制約、およびリライトの整合性レベルが影響します。また、問合せのリライトはREWRITEヒントやNOREWRITEヒント、およびQUERY_REWRITE_ENABLEDセッション・パラメータを使用して有効化したり無効化したりできます。
問合せでクエリー・リライトが可能かどうか、また可能な場合はどのマテリアライズド・ビューが使用されるかについては、DBMS_MVIEW.EXPLAIN_REWRITEプロシージャで示されます。また、このプロシージャでは問合せをリライトできない理由もわかります。
問合せは、次の一定の条件が満たされた場合にのみリライトされます。
セッションで、クエリー・リライトが使用可能である必要があります。
マテリアライズド・ビューに対するクエリー・リライトが使用可能である必要があります。
リライトの整合性レベルが、マテリアライズド・ビューの使用を許可している必要があります。たとえば、マテリアライズド・ビューが最新のものではなく、かつクエリー・リライトの整合性がENFORCEDに設定されている場合、マテリアライズド・ビューは使用できません。
問合せの要求結果の一部またはすべてが、1つ以上のマテリアライズド・ビューに格納されている、事前計算された結果から取得可能である必要があります。
オプティマイザでは、こうした条件のテストに、ユーザーが制約やディメンションを使用して宣言したデータ関係が使用される場合があります。そうしたデータ関係には、階層、参照整合性、キー・データの一意性などがあります。
DBMS_ADVISOR.TUNE_MVIEWプロシージャを使用すると、CREATE MATERIALIZED VIEW文を最適化して、一般的なQUERY REWRITEを使用可能にできます。
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と併用)を使用すると、クエリー・リライトを使用できるマテリアライズド・ビューを、ヒントで指定するビューのみに制限できます。
クエリー・リライトの動作は、次の3種類の初期化パラメータの設定によって制御します。
OPTIMIZER_MODE = ALL_ROWS(デフォルト)、FIRST_ROWSまたはFIRST_ROWS_n
OPTIMIZER_MODEをFIRST_ROWSに設定すると、コストと発見的方法の組合せによって、最初の数行を高速に配信するために最適な計画が求められます。FIRST_ROWS_nに設定すると、コストベースのアプローチが使用され、初めのn行(n = 1、10、100、1000)を戻すまでの時間が最も速くなるように最適化されます。
QUERY_REWRITE_ENABLED = TRUE(デフォルト)、FALSEまたはFORCE
このオプションを使用すると、オプティマイザのクエリー・リライト機能が有効化されてマテリアライズド・ビューを利用できるようになり、パフォーマンスが向上します。FALSEに設定すると、オプティマイザのクエリー・リライト機能が無効になり、リライトされていない問合せの見積り問合せコストの方が低くなる場合にも、マテリアライズド・ビューを使用した問合せのリライトが行われません。
FORCEに設定すると、オプティマイザのクエリー・リライト機能が有効になり、リライトされない問合せの見積り問合せコストの方が低くなる場合にも、マテリアライズド・ビューを使用して問合せがリライトされます。
QUERY_REWRITE_INTEGRITY
このパラメータはオプションですが、設定する場合は、STALE_TOLERATED、TRUSTEDまたはENFORCED(デフォルト)に設定する必要があります(クエリー・リライトの精度についてを参照)。
デフォルトでは、整合性レベルはENFORCEDに設定されます。このモードでは、すべての制約の妥当性チェックを行う必要があります。そのため、ENABLE NOVALIDATE RELYを使用した場合、一部のクエリー・リライトが動作しないことがあります。この環境でクエリー・リライトを使用可能にする(つまり、制約の妥当性チェックが行われないようにする)には、整合性レベルをTRUSTEDやSTALE_TOLERATEDのような低いレベルに設定する必要があります。
マテリアライズド・ビューをクエリー・リライトに使用できるのは、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;
クエリー・リライトには、セッション・パラメータQUERY_REWRITE_INTEGRITYによって制御されるリライト整合性レベルが3つ用意されています。この整合性レベルは、パラメータ・ファイルに設定するか、ALTER SYSTEM文またはALTER SESSION文を使用して制御できます。この3つの値は次のとおりです。
ENFORCED
これはデフォルトのモードです。オプティマイザでは、マテリアライズド・ビューの最新データのみが使用され、ENABLED VALIDATEDになっている主/一意/外部キー制約に基づいた関係のみが使用されます。
TRUSTED
TRUSTEDモードの場合、オプティマイザでは、ディメンションで宣言された関係およびRELY制約が適切であることが信頼の対象になります。このモードでは、事前作成マテリアライズド・ビューや、ビューに基づくマテリアライズド・ビューが使用され、施行された関係と同様に施行されていない関係も使用されます。また、宣言されたがENABLED VALIDATEDでない主/一意キー制約、およびディメンションを使用して指定されたデータ関係も信頼されます。このモードではより高度なクエリー・リライト機能を使用できますが、ユーザーが宣言し、信頼された関係に不正確なものがあった場合、不正確な結果が生成される危険性もあります。
STALE_TOLERATED
STALE_TOLERATEDモードの場合、オプティマイザでは最新データを含むマテリアライズド・ビューの他に、有効だが失効データを含むマテリアライズド・ビューも使用されます。このモードでは、リライト機能を最大限に使用できますが、不正確な結果が生成される危険性もあります。
リライト整合性が最も安全なレベルであるENFORCEDに設定されている場合、オプティマイザでは、問合せの結果がディテール表に直接アクセスした場合と同じであることを保証するために、施行された主キー制約および参照整合性制約のみが使用されます。リライト整合性をENFORCED以外のレベルに設定すると、次のような状況において、リライトした場合とリライトしなかった場合の出力が異なることがあります。
マテリアライズド・ビューが、データのマスター・コピーと同期されていない場合。これは、通常、マテリアライズド・ビューの1つ以上のディテール表に対するバルク・ロードまたはDML操作の後に、マテリアライズド・ビューのリフレッシュ・プロシージャが保留状態にあるために発生します。データ・ウェアハウス・サイトによっては、この状況が最適な場合もあります。これは、一部のマテリアライズド・ビューでは一定の間隔でリフレッシュされることが一般的であるためです。
ディメンション・オブジェクトに含まれる関係が無効の場合。たとえば、階層内のあるレベルの値が、正確に1つの親の値にロールアップされないことがあります。
事前作成マテリアライズド・ビュー表に格納された値が不適切な場合。
施行されていない表またはビューの制約により不正なデータ関係が定義されているため、間違った答えが生じている場合。
マテリアライズド・ビューの使用は、そのマテリアライズド・ビューに対してユーザーが保持する権限ではなく、問合せ内のディテール表またはビューに対してユーザーが保持する権限に基づきます。
GRANT QUERY REWRITEシステム権限では、自スキーマ内のマテリアライズド・ビューから直接参照される表がすべて自スキーマ内にある場合にのみ、そのマテリアライズド・ビューに対するクエリー・リライトが有効になります。GRANT GLOBAL QUERY REWRITE権限では、マテリアライズド・ビューが別のスキーマ内のオブジェクトを参照する場合にも、マテリアライズド・ビューに対するクエリー・リライトが有効になります。また、自スキーマの外部にある表およびビューの場合、QUERY REWRITEオブジェクト権限を使用することもできます。
クエリー・リライト用にマテリアライズド・ビューを使用するための権限は、定義者権限のプロシージャに対する権限と似ています。
次の項では、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);
クエリー・リライトは透過的に行われるので、問合せがリライトされたかどうかを確認するには、特別なステップを実行する必要があります。問合せが高速に実行された場合、リライトが発生したと考えられますが、これは確認にはなりません。そのため、EXPLAIN PLAN文またはDBMS_MVIEW.EXPLAIN_REWRITEプロシージャを使用して、クエリー・リライトが発生したことを確認します。詳細は、クエリー・リライトが発生したことの確認を参照してください。
次の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;
前述のマテリアライズド・ビューおよびクエリー・リライト機能がない場合、sales表が直接アクセスされ、総販売数量が計算された上でその結果が返されることになります。その場合、sales表から膨大な数の行が読み込まれ、対象となるディスクへのアクセスに伴って問合せの応答時間は必ず増大します。また、問合せに結合があるので、膨大な数の行に対する結合の計算が必要になり、問合せへの応答はさらに遅くなります。ここで、マテリアライズド・ビューcal_month_sales_mvがあると、クエリー・リライトによって前述の問合せが透過的にリライトされ、次の問合せに書き換えられます。
SELECT calendar_month, dollars FROM cal_month_sales_mv;
マテリアライズド・ビューcal_month_sales_mvの行数はほんの数十行で、結合も存在しないため、Oracle Databaseにより結果は即座に戻されます。このような単純な例からも、マテリアライズド・ビューを使用したクエリー・リライトの効果は明らかです。
