| Oracle® Databaseデータ・ウェアハウス・ガイド 11gリリース2 (11.2) B56309-04 |
|
 前 |
 次 |
この章では、Oracleの高度なクエリー・リライトについて説明します。次の内容が含まれます。
オプティマイザが問合せをリライトするには、いくつかの方法があります。クエリー・リライトが可能かどうかを判断する最初のステップは、問合せが次の前提条件を満たしているかどうかを確認することです。
マテリアライズド・ビュー内の結合がSQLで使用されている。
問合せに回答するのに十分なデータがマテリアライズド・ビューまたは複数のビューにある。
次に、オプティマイザは問合せをリライトする方法を判断する必要があります。最も簡単な例は、マテリアライズド・ビューに格納されている結果が、問合せによって要求されているものと正確に一致する場合です。オプティマイザは、問合せのテキストとマテリアライズド・ビュー定義のテキストを比較して、このような判断を行います。このテキスト一致の方法は最も簡単ですが、このタイプのクエリー・リライトに使用できる問合せの数は多くありません。
テキスト比較によるテストで判断できなかった場合、オプティマイザは、結合、選択、グルーピング、集計およびフェッチされた列データに基づいて、一連の一般的なチェックを実行します。これは、問合せの様々な句(SELECT、FROM、WHERE、HAVINGまたはGROUP BY)をマテリアライズド・ビューのものと比較することによって行われます。
この項では、オプティマイザの詳細およびクエリー・リライトの次のタイプについて説明します。
問合せがリライトされると、Oracleのコストベース・オプティマイザによって、リライトされた問合せのコストと元の問合せのコストが比較され、コストの低い方の実行計画が選択されます。
クエリー・リライトは、コストベース・オプティマイザとともに使用できます。Oracle Databaseは、リライトを使用または使用せずに入力問合せを最適化し、最も効率的な方法を選択します。オプティマイザは、1つ以上の問合せブロックを一度に1つずつリライトすることで、問合せをリライトします。
クエリー・リライトが複数のマテリアライズド・ビューの中から問合せブロックをリライトするものを選択できる場合、読み込まれるデータ量の最も少ないものが選択されます。リライト用のマテリアライズド・ビューが選択されると、オプティマイザはリライトされた問合せがさらに別のマテリアライズド・ビューでリライト可能かどうかをテストします。このプロセスは、リライトができなくなるまで繰り返されます。その後、リライトされた問合せは最適化され、元の問合せも最適化されます。オプティマイザでは、この2つの最適化したものが比較され、コストの低い方が選択されます。
最適化はコストを基準にするため、問合せに関係する表と、マテリアライズド・ビューを示す表の両方についての統計情報を収集することが重要です。表の行数などの統計情報は、リライトされた問合せのコスト計算に使用する基本的な尺度です。その作成にはDBMS_STATSパッケージを使用します。
インライン・ビューまたは名前付きビューを含む問合せも、クエリー・リライトの対象になります。問合せに名前付きビューが含まれている場合、マテリアライズド・ビューと問合せを一致させるために、ビュー名が使用されます。問合せにインライン・ビューが含まれている場合、インライン・ビューは、マテリアライズド・ビューと問合せを一致させる前に、マージされる場合があります。
図19-1に、リライト処理中に使用されるコストベースのアプローチを図示します。
オプティマイザでは、様々なクエリー・リライト方法の中から選択して、問合せに対する回答を行います。テキストの一致によるリライトが不可能な場合、一般的なクエリー・リライトと呼ばれるリライト方法が使用されます。この高度なテクニックを使用するメリットは、多数の異なる問合せに対する回答に1つ以上のマテリアライズド・ビューを使用できる点です。したがって、クエリー・リライトを発生させるために、問合せとマテリアライズド・ビューが常に一致している必要はありません。
一般的なクエリー・リライト方法を使用する場合、オプティマイザでは、主キー制約、外部キー制約、ディメンション・オブジェクトなど、依存可能なデータ関係を使用します。たとえば、主キーと外部キーの関係によって、外部キー表の各行が主キー表の1つ以下の行と結合することがオプティマイザに示されます。さらに、外部キーにNOT NULL制約がある場合は、外部キー表の各行が主キー表の1つの行と正確に結合することが示されます。ディメンション・オブジェクトは、日、月、年などの関係を表し、これは、データを日レベルから月レベルにロールアップするために使用できます。
データの結合、グルーピングまたは集計操作によって生成される結果の種類が示されるため、このデータ関係は特に重要です。そのため、このようなデータ関係がデータベースに存在する場合、大規模な問合せ集合のリライトを可能にするには、制約およびディメンションを宣言する必要があります。
表19-1に、異なるタイプのクエリー・リライトにディメンションおよび制約が必要な場合を示します。クエリー・リライトのこれらのタイプについては、この章の中で説明しています。
クエリー・リライトが発生するには、データが様々なチェックをパスする必要があります。これらのチェックには、次のものがあります。
このチェックでは、問合せの結合がマテリアライズド・ビューの結合と比較されます。一般に、この比較によって、結合は次の3つに分類されます。
問合せおよびマテリアライズド・ビューの両方に発生する共通結合。この結合は、共通のサブグラフを形成します。
問合せのみで発生し、マテリアライズド・ビューでは発生しないデルタ結合。この結合は、問合せのデルタ・サブグラフを形成します。
マテリアライズド・ビューのみで発生し、問合せでは発生しないデルタ結合。この結合は、マテリアライズド・ビューのデルタ・サブグラフを形成します。
図19-2に、これらの結合を示します。
両者間の共通結合の組は同型であるか、または問合せの結合がマテリアライズド・ビューの結合から導出可能である必要があります。たとえば、マテリアライズド・ビューが表Aの表Bとの外部結合を含み、かつ、問合せが表Aの表Bとの内部結合を含んでいる場合、内部結合の結果は、外部結合の結果からアンチ結合行をフィルタすることで導出できます。たとえば、次の問合せを考えてみます。
SELECT p.prod_name, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id
AND mv.week_ending_day BETWEEN TO_DATE('01-AUG-1999', 'DD-MON-YYYY')
AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY')
GROUP BY p.prod_name, mv.week_ending_day;
次に、この問合せとマテリアライズド・ビューjoin_sales_time_product_mvとの間の共通結合を示します。
s.time_id = t.time_id AND s.prod_id = p.prod_id
これらは正確に一致し、次のようにリライトできます。
SELECT p.prod_name, mv.week_ending_day, SUM(s.amount_sold)
FROM join_sales_time_product_mv
WHERE mv.week_ending_day BETWEEN TO_DATE('01-AUG-1999','DD-MON-YYYY')
AND TO_DATE('10-AUG-1999','DD-MON-YYYY')
GROUP BY mv.prod_name, mv.week_ending_day;
問合せは、マテリアライズド・ビューjoin_sales_time_product_oj_mvを使用しても結果を得ることができます。この場合、問合せの内部結合がマテリアライズド・ビューの外部結合から導出可能です。リライトされた問合せでは、(ユーザーには透過的に)アンチ結合行がフィルタによって排除されます。リライトされた問合せの構造は次のとおりです。
SELECT mv.prod_name, mv.week_ending_day, SUM(mv.amount_sold)
FROM join_sales_time_product_oj_mv mv
WHERE mv.week_ending_day BETWEEN TO_DATE('01-AUG-1999','DD-MON-YYYY')
AND TO_DATE('10-AUG-1999','DD-MON-YYYY') AND mv.prod_id IS NOT NULL
GROUP BY mv.prod_name, mv.week_ending_day;
一般に、結合のみを含むマテリアライズド・ビューに外部結合を使用する場合は、マテリアライズド・ビューの外部結合の右側に主キーまたはROWIDを指定する必要があります。たとえば、前述の例のjoin_sales_time_product_oj_mvでは、salesとproductsの両方に主キーがあります。
結合のみを含むマテリアライズド・ビューの別の例に、セミ結合リライトの例があります。つまり、単一表を持つEXISTSまたはIN副問合せが含まれる問合せです。次のような、売上が1,000ドルを超えた製品をレポートする問合せを考えてみます。
SELECT DISTINCT p.prod_name
FROM products p
WHERE EXISTS (SELECT p.prod_id, SUM(s.amount_sold) FROM sales s
WHERE p.prod_id=s.prod_id HAVING SUM(s.amount_sold) > 1000)
GROUP BY p.prod_id);
この問合せは、次のように表すこともできます。
SELECT DISTINCT p.prod_name
FROM products p WHERE p.prod_id IN (SELECT s.prod_id FROM sales s
WHERE s.amount_sold > 1000);
この問合せには、products表とsales表の間のセミ結合(s.prod_id = p.prod_id)が含まれています。
この問合せは、外部キー制約がアクティブの場合、join_sales_time_product_mvマテリアライズド・ビューを使用するか、主キーがアクティブの場合はjoin_sales_time_product_oj_mvマテリアライズド・ビューを使用してリライトできます。どちらのマテリアライズド・ビューも、s.prod_id=p.prod_idが含まれます。これは、問合せ内のセミ結合を導出する場合に使用します。この問合せは次のように、join_sales_time_product_mvを使用してリライトされます。
SELECT mv.prod_name
FROM (SELECT DISTINCT mv.prod_name FROM join_sales_time_product_mv mv
WHERE mv.amount_sold > 1000);
マテリアライズド・ビューjoin_sales_time_product_mvがtime_idでパーティション化された場合、salesとproductsの間の元の結合が回避されたため、この問合せは元の問合せより効率的になる傾向があります。この問合せは次のように、join_sales_time_product_oj_mvを使用してリライトされる場合もあります。
SELECT mv.prod_name
FROM (SELECT DISTINCT mv.prod_name FROM join_sales_time_product_oj_mv mv
WHERE mv.amount_sold > 1000 AND mv.prod_id IS NOT NULL);
セミ結合を使用したリライトは、結合のみを含むマテリアライズド・ビューに制限されており、結合および集計を含むマテリアライズド・ビューの場合には使用できません。
問合せデルタ結合は、問合せでのみ使用される結合で、マテリアライズド・ビューでは使用されません。問合せに指定できるデルタ結合の数や種類に制限はなく、問合せがマテリアライズド・ビューを使用してリライトされた場合は、簡単に保持されます。保持された結合が正常に機能するには、マテリアライズド・ビューに結合キーが含まれている必要があります。リライトの際、マテリアライズド・ビューは問合せデルタ内の適切な表に結合されます。たとえば、次の問合せを考えてみます。
SELECT p.prod_name, t.week_ending_day, c.cust_city, SUM(s.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_name, t.week_ending_day, c.cust_city;
マテリアライズド・ビューjoin_sales_time_product_mvを使用した場合、共通結合はs.time_id=t.time_idおよびs.prod_id=p.prod_idになります。問合せのデルタ結合はs.cust_id=c.cust_idです。リライトされたフォームは、次のようにjoin_sales_time_product_mvマテリアライズド・ビューをcustomers表に結合します。
SELECT mv.prod_name, mv.week_ending_day, c.cust_city, SUM(mv.amount_sold) FROM join_sales_time_product_mv mv, customers c WHERE mv.cust_id = c.cust_id GROUP BY mv.prod_name, mv.week_ending_day, c.cust_city;
マテリアライズド・ビューのデルタ結合は、マテリアライズド・ビューのみで使用される結合で、問合せでは使用されません。マテリアライズド・ビュー内のすべてのデルタ結合は、共通結合の結果に関して可逆式である必要があります。可逆式結合は、共通結合の結果が制限されないことを保証します。可逆式結合では、表Aと表Bが結合された場合、表Aの行は表Bの行と常に一致し、どのデータも消失しません。このため、可逆式結合と呼ばれます。たとえば、外部キーを使用する各行は、外部キーにNULLが許可されない場合、主キーを使用した1つの行と一致します。そのため、可逆式結合を保証するには、適切な結合キーに外部キー制約、主キー制約およびNOT NULL制約を指定する必要があります。または、表Aと表Bの結合が外部結合の場合(Aが外部表の場合)、結合は表Aのすべての行を保持するため可逆式となります。
マテリアライズド・ビュー内のすべてのデルタ結合は、共通結合の結果に関して重複していない必要があります。非重複結合は、共通結合の結果が重複されないことを保証します。たとえば、非重複結合では、表Aと表Bが結合された場合、表Aの行は表Bの1つ以下の行と一致し、重複は発生しません。非重複結合を保証するには、主キー制約または一意キー制約を使用して、表Bのキーを一意の値に制約する必要があります。
salesとtimesを結合する次の問合せを考えてみます。
SELECT t.week_ending_day, SUM(s.amount_sold)
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.week_ending_day BETWEEN TO_DATE
('01-AUG-1999', 'DD-MON-YYYY') AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY')
GROUP BY week_ending_day;
マテリアライズド・ビューjoin_sales_time_product_mvは、salesとproductsの間の結合(s.prod_id=p.prod_id)が追加されています。これは、join_sales_time_product_mvのデルタ結合です。この結合が可逆式および非重複である場合は、問合せをリライトできます。s.prod_idがp.prod_idに対する外部キーで、かつ、NULLではない場合がその例です。したがって、問合せは次のようにリライトされます。
SELECT week_ending_day, SUM(amount_sold)
FROM join_sales_time_product_mv
WHERE week_ending_day BETWEEN TO_DATE('01-AUG-1999', 'DD-MON-YYYY')
AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY')
GROUP BY week_ending_day;
問合せは、外部キー制約が必要とされないマテリアライズド・ビューjoin_sales_time_product_mv_ojを使用してリライトすることもできます。このビューには、salesとproductsの間の外部結合(s.prod_id=p.prod_id(+))が含まれています。このため、この結合は可逆式になります。p.prod_idが主キーの場合、非重複条件も満たされ、オプティマイザは問合せを次のようにリライトします。
SELECT week_ending_day, SUM(amount_sold)
FROM join_sales_time_product_oj_mv
WHERE week_ending_day BETWEEN TO_DATE('01-AUG-1999', 'DD-MON-YYYY')
AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY')
GROUP BY week_ending_day;
問合せは、外部キー制約が必要とされないマテリアライズド・ビューjoin_sales_time_product_mv_ojを使用してリライトすることもできます。このビューには、salesとproductsの間の外部結合(s.prod_id=p.prod_id(+))が含まれています。このため、この結合は可逆式になります。p.prod_idが主キーの場合、非重複条件も満たされ、オプティマイザは問合せを次のようにリライトします。
SELECT week_ending_day, SUM(amount_sold)
FROM join_sales_time_product_oj_mv
WHERE week_ending_day BETWEEN TO_DATE('01-AUG-1999', 'DD-MON-YYYY')
AND TO_DATE('10-AUG-1999', 'DD-MON-YYYY')
GROUP BY week_ending_day;
shスキーマでは、salesとproductsの間の主キー/外部キーの関係がすでに可逆式になっているため、join_sales_time_product_mv_ojの定義には外部結合が不要であることに注意してください。ただしこれは、単に具体例を示すためのものです。sales.prod_idがNULL値可能で、したがって結合条件sales.prod_id = products.prod_idに可逆性がなくなる場合、外部結合は必要となります。
現在の制限事項では、外部結合を含むリライトのほとんどは、結合のみを使用したマテリアライズド・ビューに制限されます。外部結合を含むマテリアライズド集計ビューを使用したリライトのサポートは制限されているため、この種のマテリアライズド・ビューは、マテリアライズド・ビューのデルタ結合の可逆性を保証するために、外部キー制約に依存する必要があります。
クエリー・リライトは、等価な結合であるとの認識に基づいて多くの変換を行うことができます。クエリー・リライトでは、次の構成体は結合に対して等価であると認識されます。
WHERE table1.column1 = F(args) /* sub-expression A */ AND table2.column2 = F(args) /* sub-expression B */
F(args)がDETERMINISTICとして宣言されているPL/SQLファンクションで、Fを起動するときの引数がいずれも同じである場合、副次式Aと副次式Bの組合せは、table1.column1とtable2.column2の結合として認識できます。つまり、次の式は、前述の式と等価になります。
WHERE table1.column1 = F(args) /* sub-expression A */ AND table2.column2 = F(args) /* sub-expression B */ AND table1.column1 = table2.column2 /* join-expression J */
結合式Jは副次式Aと副次式Bから推論できるため、推論された結合を使用して、マテリアライズド・ビュー内の対応する結合table1.column1 = table2.column2に一致させることができます。
このチェックでは、オプティマイザは、問合せが要求した列データが、マテリアライズド・ビューから取得可能かどうかを判断します。このために、1つの列と別の列との同等化が使用されます。たとえば、表Aと表Bの間の内部結合が結合述部A.X = B.Xに基づく場合、結合の結果、列A.Xのデータは列B.Xのデータと同等になります。このデータ・プロパティが、問合せ内の列A.Xとマテリアライズド・ビュー内の列B.Xの一致、またはその逆に使用されます。たとえば、次の問合せを考えてみます。
SELECT p.prod_name, s.time_id, t.week_ending_day, SUM(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 p.prod_name, s.time_id, t.week_ending_day;
この問合せは、マテリアライズド・ビューにs.time_idが含まれない場合でも、join_sales_time_product_mvを使用して回答されます。かわりにこの問合せには、結合条件s.time_id=t.time_idを介してs.time_idと同等のt.time_idが含まれます。したがって、オプティマイザで次のリライトが選択される場合があります。
SELECT prod_name, time_id, week_ending_day, SUM(amount_sold) FROM join_sales_time_product_mv GROUP BY prod_name, time_id, week_ending_day;
このチェックは、マテリアライズド・ビューと問合せの両方にGROUP BY句がある場合にのみ必要です。オプティマイザは、まず、問合せが要求したデータのグルーピングが、マテリアライズド・ビューに格納されているデータのグルーピングと同じかどうかを判断します。つまり、グルーピングのレベルは、問合せとマテリアライズド・ビューで同じです。マテリアライズド・ビューで問合せの列および式をすべてグルーピングし、追加の列または式もグルーピングする場合、クエリー・リライトでは、問合せのグルーピング列およびグルーピング式でマテリアライズド・ビューを再集計して、問合せで要求された同じ結果を導出できます。
このチェックは、問合せおよびマテリアライズド・ビューの両方に集計が含まれている場合にのみ必要になります。このチェックでは、オプティマイザは、問合せが要求した集計が、マテリアライズド・ビューに格納された1つ以上の集計から導出または計算可能かどうかを判断します。たとえば、問合せがAVG(X)を要求し、マテリアライズド・ビューがSUM(X)およびCOUNT(X)を含む場合、AVG(X)はSUM(X)/COUNT(X)で計算できます。
グルーピング互換性チェックによって、マテリアライズド・ビューに格納された集計のロールアップが必要であると判断された場合、次に、集計可能性チェックによって、問合せが要求した各集計が、マテリアライズド・ビューの集計を使用してロールアップできるかどうかが判断されます。
この項では、リライト環境でディメンションを使用する際の次の側面について説明します。
ディメンションは、列同士の階層(親子)関係を定義します。これらの列は、同じ表の列である必要はありません。
ディメンションを定義すると、列間の機能依存性を確立できるため、クエリー・リライトの実行性が高まります。また、ディメンションにより、制約では表現できない表内の関係を表現することもできます。ディメンション定義に追加の領域は必要ありません。むしろ、ディメンション定義を行うと、使用しているスキーマ内のディメンション内およびディメンション間の関係を記述するメタデータが構築されます。マテリアライズド・ビューを作成する前に行う最初の手順は、スキーマを調べてディメンションを定義することです。これによって問合せのリライトの機会が大幅に増す場合があるためです。
ディメンションは、任意のスキーマに対し次の手順に従って作成できます。
複数の表に収まるようにディメンションが正規化されている場合、ディメンション表間の結合において、子表の各行が親表の1つの行のみと結合していることが保証されていることを確認します。また非正規化ディメンションの場合、子の列が親(または属性)列を一意に決定できなくてはなりません。これらの規則を順守できなかった場合、問合せから不正確な結果が戻される場合があります。
次に、各結合において、ファクト表の各行がディメンションの1つの行のみと結合することが保証されることを確認します。この条件を宣言し、オプションで規定する必要があります。それには、ファクト表のキー列に外部キー制約およびNOT NULL制約を追加し、親表の結合キーに主キー制約を追加します。これらの関係が、プロシージャ(ロード・プロセスなど)を処理する他のデータによって保証される場合、これらの制約は、NOVALIDATEオプションを使用して有効化できます。このオプションを使用すると、表のすべての行がその制約に準拠することを検証するのに必要な時間を節約できます。妥当性チェックが行われないすべての制約について、クエリー・リライトで使用できるようにするためには、RELY句も必要になります。
時間ディメンションを作成するSQL文の例
CREATE DIMENSION times_dim
LEVEL day IS TIMES.TIME_ID
LEVEL month IS TIMES.CALENDAR_MONTH_DESC
LEVEL quarter IS TIMES.CALENDAR_QUARTER_DESC
LEVEL year IS TIMES.CALENDAR_YEAR
LEVEL fis_week IS TIMES.WEEK_ENDING_DAY
LEVEL fis_month IS TIMES.FISCAL_MONTH_DESC
LEVEL fis_quarter IS TIMES.FISCAL_QUARTER_DESC
LEVEL fis_year IS TIMES.FISCAL_YEAR
HIERARCHY cal_rollup
(day CHILD OF month CHILD OF quarter CHILD OF year)
HIERARCHY fis_rollup
(day CHILD OF fis_week CHILD OF fis_month CHILD OF fis_quarter
CHILD OF fis_year)
ATTRIBUTE day DETERMINES
(day_number_in_week, day_name, day_number_in_month,
calendar_week_number)
ATTRIBUTE month DETERMINES
(calendar_month_desc, calendar_month_number, calendar_month_name,
days_in_cal_month, end_of_cal_month)
ATTRIBUTE quarter DETERMINES
(calendar_quarter_desc, calendar_quarter_number,days_in_cal_quarter,
end_of_cal_quarter)
ATTRIBUTE year DETERMINES
(calendar_year, days_in_cal_year, end_of_cal_year)
ATTRIBUTE fis_week DETERMINES
(week_ending_day, fiscal_week_number);
ディメンションで宣言された関係をクエリー・リライトで利用するには、パラメータQUERY_REWRITE_INTEGRITYをTRUSTEDまたはSTALE_TOLERATEDに設定する必要があります。
多数の行または大規模な表同士の結合に対して計算が必要な集計を持つ問合せは、コストが高くなる可能性があり、結果を戻すまでに長い時間がかかる場合があります。クエリー・リライトは、事前計算済の結果が格納されているマテリアライズド・ビューを使用してそのような問合せを透過的にリライトするので、問合せに対して瞬時に結果を戻すことができます。これらのマテリアライズド・ビューは、大きく2つのグループに分類できます。すなわち、マテリアライズド集計ビューとマテリアライズド結合ビューです。マテリアライズド集計ビューは、元の表の列の事前に計算された集計値を格納する表です。同様に、マテリアライズド結合ビューは、元の表の列間の事前に計算された結合を格納する表です。クエリー・リライトは、入力問合せを変換して、マテリアライズド・ビューの列から結果をフェッチします。これらの列には事前に計算された結果がすでに格納されているので、入力問合せに対して瞬時に結果を戻すことができます。キューブ・マテリアライズド・ビューのクエリー・リライトに関する考慮事柄については、『Oracle OLAPユーザーズ・ガイド』を参照してください。
この項では、問合せのリライトに使用できる次の方法について説明します。
クエリー・リライト・エンジンは常に、問合せをリライトする際に対象となり得るすべてのマテリアライズド・ビューの定義のテキストと、入力問合せのテキストとの比較を最初に試みます。これは、一般的なリライトに必要な複雑な分析を行うコストと比較して、単純なテキスト比較を行うオーバーヘッドが通常わずかであるためです。
クエリー・リライト・エンジンは、テキストの完全一致のリライトおよびテキストの部分一致によるリライトという、2つのテキスト一致方法を使用します。テキストが完全に一致する場合は、問合せのテキスト全体がマテリアライズド・ビュー定義のテキスト全体(SELECT文全体)と比較されます。テキストの比較中は、空白は無視されます。たとえば、次のマテリアライズド・ビューsum_sales_pscat_month_city_mvがあるとします。
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;
次の問合せを考えてみます。
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;
この問合せは、sum_sales_pscat_month_city_mv(空白は除外)と一致し、次のようにリライトされます。
SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city,
mv.sum_amount_sold, mv.count_amount_sold
FROM sum_sales_pscat_month_city_mv;
テキストの完全一致が失敗した場合、オプティマイザはテキストの部分一致を試みます。この方法では、問合せのFROM句から始まるテキストが、マテリアライズド・ビュー定義のFROM句から始まるテキストと比較されます。したがって、次の問合せはリライトできます。
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city,
AVG(s.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;
この問合せは、次のようにリライトされます。
SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city,
mv.sum_amount_sold/mv.count_amount_sold
FROM sum_sales_pscat_month_city_mv mv;
テキストの部分一致によるリライト方法の場合、問合せが要求した売上集計の平均は、マテリアライズド・ビューに格納された売上の合計および売上集計の件数を使用して計算されます。
どちらのテキストの一致も成功しなかった場合、オプティマイザは、一般的なクエリー・リライト方法を使用します。
テキスト一致リライトでは、大文字と小文字の違いに意味があるコンテキストとそうでないコンテキストが区別されます。たとえば、次の2つの文は同等です。
SELECT X, 'aBc' FROM Y Select x, 'aBc' From y
問合せが要求した列データがマテリアライズド・ビューから取得できない場合、オプティマイザは、さらに、機能依存性と呼ばれるデータ関係を基に取得できないかを判断します。列内のデータによって別の列のデータを決定できる場合、そのような関係は、機能依存性または機能決定性と呼ばれます。たとえば、1つの表にprod_idという主キー列、およびprod_nameという別の列があるとします。prod_id値を指定した場合、対応付けられたprod_nameを参照できます。この逆は真ではありません。つまり、prod_name値は一意のprod_idに関連付けられている必要はありません。
問合せが要求した列データが、マテリアライズド・ビューに含まれない場合、要求された列データを機能的に決定するキーがマテリアライズド・ビューに含まれていれば、それらの列データは、要求された列データを含む表をマテリアライズド・ビューに後戻り結合することによって取得できます。たとえば、次の問合せを考えてみます。
SELECT p.prod_category, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND p.prod_category='CD' GROUP BY p.prod_category, t.week_ending_day;
マテリアライズド・ビューsum_sales_prod_week_mvにはp.prod_idが含まれていますが、p.prod_categoryは含まれていません。ただし、prod_idは機能的にprod_categoryを決定するため、sum_sales_prod_week_mvをproductsに再び結合して、prod_categoryを取り出すことができます。オプティマイザは、sum_sales_prod_week_mvを次のように使用して、この問合せをリライトします。
SELECT p.prod_category, mv.week_ending_day, SUM(mv.sum_amount_sold) FROM sum_sales_prod_week_mv mv, products p WHERE mv.prod_id=p.prod_id AND p.prod_category='CD' GROUP BY p.prod_category, mv.week_ending_day;
この場合、products表は、マテリアライズド・ビューに結合されていたものが、リライトされた問合せで再び結合されたため、後戻り結合表と呼ばれます。
機能依存性は、次の2つの方法で宣言できます。
主キー制約を使用する方法(前述の例を参照)
ディメンションのDETERMINES句を使用する方法
別の列を決定する列を主キーにできない場合、ディメンション定義のDETERMINES句のみが、機能依存性を宣言できる唯一の方法になることがあります。たとえば、products表は、prod_id、prod_nameおよびprod_subcategoryの各列を持つ非正規化ディメンション表です。prod_subcategoryはprod_subcat_descおよびprod_categoryを機能的に決定します。また、prod_categoryは、prod_cat_descを機能的に決定します。
最初の機能依存性は、prod_idを主キーとして宣言することで確立されますが、2番目の機能依存性は、prod_subcategory列に重複値が含まれるため、この方法では確立できません。そのような場合は、ディメンションのDETERMINES句を使用すると、2番目の機能依存性を宣言できます。
次のディメンション定義は、機能依存性の宣言方法を示します。
CREATE DIMENSION products_dim
LEVEL product IS (products.prod_id)
LEVEL subcategory IS (products.prod_subcategory)
LEVEL category IS (products.prod_category)
HIERARCHY prod_rollup (
product CHILD OF
subcategory CHILD OF
category
)
ATTRIBUTE product DETERMINES products.prod_name
ATTRIBUTE product DETERMINES products.prod_desc
ATTRIBUTE subcategory DETERMINES products.prod_subcat_desc
ATTRIBUTE category DETERMINES products.prod_cat_desc;
階層prod_rollupは、1:n機能依存性でもある階層関係を宣言します。1:1機能依存性は、prod_subcategoryが機能的にprod_subcat_descを決定するように、DETERMINES句を使用して宣言されます。
次のマテリアライズド・ビューが作成されているものとします。
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_sole
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;
次の問合せを考えてみます。
SELECT p.prod_subcategory_desc, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, times t WHERE s.time_id=t.time_id AND s.prod_id=p.prod_id AND p.prod_subcat_desc LIKE '%Men' GROUP BY p.prod_subcat_desc, t.week_ending_day;
この問合せは、sum_sales_pscat_week_mvをproducts表に結合することによりリライトできます。これにより、prod_subcat_descを使用して選択述語の評価を行うことが可能になります。ただし、結合は、products表の主キーでないprod_subcategory列をベースにするため、重複が許可されます。これは、個別値を選択するインライン・ビューを使用することによって実現され、このビューは、次のリライトされた問合せで示すように、マテリアライズド・ビューと結合されます。
SELECT iv.prod_subcat_desc, mv.week_ending_day, SUM(mv.sum_amount_sold)
FROM sum_sales_pscat_week_mv mv,
(SELECT DISTINCT prod_subcategory, prod_subcat_desc
FROM products) iv
WHERE mv.prod_subcategory=iv.prod_subcategory
AND iv.prod_subcat_desc LIKE '%Men'
GROUP BY iv.prod_subcat_desc, mv.week_ending_day;
このようなリライトが可能なのは、prod_subcategoryが、ディメンションで宣言されたようにprod_subcategory_descを機能的に決定することによります。
問合せで要求された集計がマテリアライズド・ビューに格納された1つ以上の集計から導出または計算可能かどうかをオプティマイザで判断する場合にも、クエリー・リライトは可能です。たとえば、問合せがAVG(X)を要求し、マテリアライズド・ビューがSUM(X)およびCOUNT(X)を含む場合、AVG(X)はSUM(X)/COUNT(X)で計算できます。
また、マテリアライズド・ビューに格納された集計のロールアップが必要と判断され、ロールアップが可能な場合、クエリー・リライトでは、マテリアライズド・ビューの集計を使用して、問合せで要求された各集計もロールアップします。
たとえば、州の値が同じグループのSUM(sales)集計をすべて合計することによって、市レベルのSUM(sales)を州レベルのSUM(sales)にロールアップできます。ただし、COUNT(sales)またはSUM(sales)がマテリアライズド・ビューで使用可能でない場合、細分性が低いレベルにAVG(sales)をロールアップすることはできません。同様に、COUNT(sales)およびSUM(sales)がマテリアライズド・ビューで使用可能でない場合、VARIANCE(sales)またはSTDDEV(sales)はロールアップできません。たとえば、次の問合せを考えてみます。
ALTER TABLE times MODIFY CONSTRAINT time_pk RELY; ALTER TABLE customers MODIFY CONSTRAINT customers_pk RELY; ALTER TABLE sales MODIFY CONSTRAINT sales_time_pk RELY; ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk RELY; SELECT p.prod_subcategory, AVG(s.amount_sold) AS avg_sales FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_subcategory;
salesとtimes、およびsalesとcustomersの結合が可逆式で非重複の場合は、この文では、マテリアライズド・ビューsum_sales_pscat_month_city_mvをリライトに使用できます。さらに、問合せはprod_subcategoryによるグルーピング、マテリアライズド・ビューはprod_subcategory、calendar_month_descおよびcust_cityによるグルーピングであるため、マテリアライズド・ビューに格納された集計がロールアップされる必要があります。オプティマイザは、次のように問合せをリライトします。
SELECT mv.prod_subcategory, SUM(mv.sum_amount_sold)/COUNT(mv.count_amount_sold) AS avg_sales FROM sum_sales_pscat_month_city_mv mv GROUP BY mv.prod_subcategory;
SUMのような集計には、A+Bなどの算術式を引数とすることができます。オプティマイザでは、問合せ内の集計SUM(A+B)と、マテリアライズド・ビューに格納されている集計SUM(A+B)またはSUM(B+A)とを一致させるよう処理します。つまり、問合せ内の集計の引数とマテリアライズド・ビューの同様の集計の引数を一致させる際に、式の同等化を使用します。そのためには、同等である別々の2つの式が同じ標準形になるように、Oracleは集計引数式を標準的な形式に変換します。たとえば、A*(B-C)、A*B-C*A、(B-C)*Aおよび-A*C+A*Bはすべて同一の標準形に変換され、それによってこれらは正しく一致します。
問合せが要求したデータのグルーピングが、マテリアライズド・ビューに格納されているデータのグルーピングより細分性が低いレベルにある場合でも、オプティマイザはマテリアライズド・ビューを使用して問合せをリライトできます。たとえば、マテリアライズド・ビューsum_sales_pscat_week_mvは、prod_subcategoryとweek_ending_dayによるグルーピングです。この問合せは、prod_subcategoryによるグルーピング(より細分性が低いグルーピング)です。
ALTER TABLE times MODIFY CONSTRAINT time_pk RELY; ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk RELY; SELECT p.prod_subcategory, SUM(s.amount_sold) AS sum_amount FROM sales s, products pWHERE s.prod_id=p.prod_id GROUP BY p.prod_subcategory;
したがって、オプティマイザは次のようにこの問合せをリライトします。
SELECT mv.prod_subcategory, SUM(mv.sum_amount_sold) FROM sum_sales_pscat_week_mv mv GROUP BY mv.prod_subcategory;
階層の異なるレベルでレポートが必要な場合、ディメンションが定義済であれば、階層内のレベルごとにマテリアライズド・ビューを作成する必要はありません。これは、クエリー・リライトが、ディメンション内の関係情報を使用して、マテリアライズド・ビューのデータを階層内の必要なレベルにロールアップできるためです。
次の例では、問合せはprod_categoryでグルーピングされたデータを要求し、マテリアライズド・ビューはprod_subcategoryでグルーピングされたデータを格納しています。prod_subcategoryがCHILD OF prod_categoryである場合(前述のディメンション例を参照)、マテリアライズド・ビューに格納されているグルーピングされたデータは、問合せがリライトされた場合にprod_categoryによってさらにグルーピングできます。つまり、マテリアライズド・ビューに格納されたprod_subcategoryレベルの(細分性がより高い)集計は、prod_categoryレベルの(細分性がより低い)集計にロールアップできます。
たとえば、次の問合せを考えてみます。
SELECT p.prod_category, t.week_ending_day, SUM(s.amount_sold) AS sum_amount 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_category, t.week_ending_day;
prod_subcategoryは機能的にprod_categoryを決定するため、sum_sales_pscat_week_mvは、prod_category列データを取り出すためにproducts表の後戻り結合で使用されます。その後、集計は、次に示すようにprod_categoryレベルにロールアップされます。
SELECT pv.prod_category, mv.week_ending_day, SUM(mv.sum_amount_sold)
FROM sum_sales_pscat_week_mv mv,
(SELECT DISTINCT prod_subcategory, prod_category
FROM products) pv
WHERE mv.prod_subcategory= pv.prod_subcategory
GROUP BY pv.prod_category, mv.week_ending_day;
Oracleではクエリー・リライトに、HAVING句またはWHERE句によって1つの表または複数の表からデータを絞り込んでいるマテリアライズド・ビューを使用することをサポートします。たとえば、New Hampshire在住の顧客に限定したものなどです。つまりWHERE句は、WHERE state = 'New Hampshire'となります。
このタイプのクエリー・リライトを実行するには、Oracleは、問合せで要求されたデータがマテリアライズド・ビューに格納されているデータに含まれているか、そのサブセットかどうかを判断する必要があります。以降の項では、Oracleでこの問題が解決され、ディテール表のデータのフィルタ処理済部分を含むマテリアライズド・ビューを使用するように問合せがリライトされる場合の条件について説明します。
フィルタ処理済データでクエリー・リライトを実行できるかどうかを判断するために、問合せとマテリアライズド・ビューの両方に選択述語(非結合)が含まれている場合に選択互換性チェックが実行されます。このチェックは、WHERE句およびHAVING句で行われます。マテリアライズド・ビューに選択が含まれ、問合せに含まれていなければ、マテリアライズド・ビューの方が問合せより限定的であるため、選択互換性チェックは失敗します。問合せに選択述語があり、マテリアライズド・ビューになければ、選択互換性チェックは不要です。
マテリアライズド・ビューのWHEREまたはHAVING句は、結合または絞込み選択(あるいはその両方)を使用でき、問合せのリライトにも使用されます。式を含む述語句や、特定の列の値に基づいて行を選択する述語句は、非結合述語の一例です。
クエリー・リライトでデータの1つのサブセットのみを処理する際に可能な事柄について説明する前に、次の定義について説明します。
join relop
(=、<、<=、>、>=)のいずれかです。
selection relop
(=、<、<=、>、>=、!=、[NOT] BETWEEN | IN| LIKE |NULL)のいずれかです。
join predicate
(column1 join relop column2)形式です。列は、現在の問合せブロックで同じFROM句に記述された異なる表の列です。したがって、外部参照などは使用できません。
selection predicate
左辺式relop右辺式、の形式です。すべての非結合述語は選択述語です。通常、左辺には列、右辺には値が含まれます。たとえば、color='red'の場合、左辺はcolor、右辺は'red'、関係演算子は(=)です。
選択述語は、次のように分類されます。
単純
単純な選択述語は、expression relop constantという形式です。
複雑
複雑な選択述語は、expression relop expressionという形式です。
範囲
範囲選択述語は、WHERE (cust_last_name BETWEEN 'abacrombe' AND 'anakin')などの形式です。
関係演算子(<、<=、>、>=)を含む単純な選択述語も、範囲選択述語とみなされます。
INリスト
WHERE(prod_id) IN (102, 233, ....)など、単一列と複数列のINリストです。
(column1='v1' OR column1='v2' OR column1='v3' OR ....)形式の選択は、グループとして扱われ、INリストに分類されます。
IS [NOT] NULL
[NOT] LIKE
その他
その他の選択述語は、データの境界を判断できない場合です。たとえば、EXISTSなどです。
問合せの選択述語とマテリアライズド・ビューの選択述語の比較では、両者の選択述語の左辺が比較されます。
左辺の選択述語が一致した場合、右辺値でデータの包含がチェックされます。つまり、問合せの選択述語の右辺値が、マテリアライズド・ビューの選択述語の右辺値に含まれている必要があります。
選択述語で式を使用することもできます。この処理は次のようになります。
expression relational operator constant
expressionには、Oracle Databaseで許可されている任意の算術式を使用できます。マテリアライズド・ビューと問合せの式は一致する必要があります。Oracleでは、A+BやB+Aなど、論理的に等価の式は常に同じ式として認識されます。
また、演算子または演算子として定義されるユーザー定義関数の左辺と右辺に式を持つ問合せを使用することもできます。クエリー・リライトは、マテリアライズド・ビューと問合せの複雑な選択述語が論理的に等価のときに発生します。これは、テキストの完全一致とは異なり、式が等価であるかぎり、条件を異なる順序で指定してもリライトできることを意味します。
次に、データがフィルタ処理される際にクエリー・リライトが可能な様々な例を示します。
例19-1 単一値の選択述語
問合せに次の句が含まれているとします。
WHERE prod_id = 102
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE prod_id BETWEEN 0 AND 200
この例では、左辺の選択述語がprod_idで一致し、問合せの右辺値102がマテリアライズド・ビューの範囲内にあるため、クエリー・リライトは可能です。
例19-2 境界付き範囲の選択述語
選択述語には、境界付き範囲(上限値と下限値を持つ範囲)を使用できます。たとえば、問合せに次の句が含まれているとします。
WHERE prod_id > 10 AND prod_id < 50
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE prod_id BETWEEN 0 AND 200
この場合、選択述語はprod_idで一致し、問合せの範囲はマテリアライズド・ビューの範囲内にあります。この例では、問合せの選択が両方とも同じ列に基づいていることがわかります。
例19-3 式を使用した選択述語
問合せに次の句が含まれているとします。
WHERE (sales.amount_sold * .07) BETWEEN 1.00 AND 100.00
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE (sales.amount_sold * .07) BETWEEN 0.0 AND 200.00
この例では、選択述語が(sales.amount_sold *.07)で一致し、問合せの右辺値がマテリアライズド・ビューの範囲内にあるため、クエリー・リライトは可能です。このような複雑な選択述語では、左辺と右辺がマテリアライズド・ビューの範囲内で一致している必要があります。
例19-4 完全一致の選択述語
問合せに次の句が含まれているとします。
WHERE (cost.unit_price * 0.95) > (cost_unit_cost * 1.25)
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE (cost.unit_price * 0.95) > (cost_unit_cost * 1.25)
左辺と右辺がマテリアライズド・ビューに一致し、selection_relopが同一の場合、通常はリライトされた問合せから選択述語を削除できます。それ以外の場合は、選択述語によりマテリアライズド・ビューから余分なデータを除外する必要があります。
クエリー・リライトで、リライトされた問合せから選択述語を削除できる場合は、選択述語のすべての列がマテリアライズド・ビュー内になくてもかまわないため、より多くのリライトを実行できます。これにより、マテリアライズド・ビューのデータが問合せより限定的でないことが保証されます。
例19-5 問合せでの追加選択述語
問合せの選択述語はマテリアライズド・ビューの選択述語と一致する必要はありませんが、一致する場合は、右辺値がマテリアライズド・ビューに含まれている必要があります。たとえば、問合せに次の句が含まれているとします。
WHERE prod_name = 'Shorts' AND prod_category = 'Men'
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE prod_category = 'Men'
この例では、prod_categoryのみが選択述語一致です。問合せには、マテリアライズド・ビューとは一致しない選択述語がありますが、マテリアライズド・ビューで、prod_nameを選択するか、ディテール表に後戻り結合してprod_nameを取得できる列を選択する場合は許容され、クエリー・リライトが可能です。唯一の要件は、選択述語prod_nameをマテリアライズド・ビューに適用する方法がクエリー・リライトに必要とされることです。
例19-6 問合せ内の選択述語が少ないためにリライトが発生しない場合
問合せに次の句が含まれているとします。
WHERE prod_category = 'Men'
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE prod_name = 'Shorts' AND prod_category = 'Men'
この例では、マテリアライズド・ビューの選択述語prod_nameが一致しません。マテリアライズド・ビューのみが製品Shortsを含んでいるので、マテリアライズド・ビューの方が問合せより限定的です。したがって、クエリー・リライトは発生しません。
例19-7 複数列のINリストの選択述語
クエリー・リライトでは、問合せに複数列のINリストがあり、その各列がマテリアライズド・ビューの単一列のINリストからの個々の列と完全に一致している場合も、チェックされます。たとえば、問合せに次の句が含まれているとします。
WHERE (prod_id, cust_id) IN ((1022, 1000), (1033, 2000))
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE prod_id IN (1022,1033) AND cust_id IN (1000, 2000)
この例では、マテリアライズド・ビューのINリストは、問合せの複数列のINリスト内の列と一致しています。さらに、問合せの選択の右辺値は、マテリアライズド・ビューに含まれているため、リライトが発生します。
例19-8 INリストを使用した選択述語
選択互換性では、マテリアライズド・ビューに複数列のINリストがあり、その各列が問合せ内のINリストの1つ以上の列と完全に一致している場合も、チェックされます。たとえば、問合せに次の句が含まれているとします。
WHERE prod_id = 1022 AND cust_id IN (1000, 2000)
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE (prod_id, cust_id) IN ((1022, 1000), (1022, 2000))
この例では、マテリアライズド・ビューのINリストの列は、問合せの選択述語の列と一致しています。さらに、問合せの選択述語の右辺値は、マテリアライズド・ビューに含まれています。したがって、リライトは成功します。
例19-9 複数の選択述語または式
問合せに次の句が含まれているとします。
WHERE (city_population > 15000 AND city_population < 25000 AND state_name = 'New Hampshire')
また、マテリアライズド・ビューに次の句が含まれているとします。
WHERE (city_population < 5000 AND state_name = 'New York') OR (city_population BETWEEN 10000 AND 50000 AND state_name = 'New Hampshire')
この例では、問合せに単一の離接詞(ANDで区切られた選択述語のグループ)があります。また、マテリアライズド・ビューには、ORで区切られた離接詞が2つあります。問合せの単一の離接詞はマテリアライズド・ビューの2番目の離接詞に含まれているため、選択互換性チェックにパスします。マテリアライズド・ビューには、問合せで必要とするより多くのデータが含まれているため、問合せをリライトできることは明らかです。
問合せで指定された範囲がマテリアライズド・ビューで指定された範囲内にあれば、SUM(s.amount_sold) BETWEEN 10000 AND 20000のように、問合せのHAVING句で集計値の範囲が指定されている場合でも、クエリー・リライトが可能です。
CREATE MATERIALIZED VIEW product_sales_mv BUILD IMMEDIATE REFRESH FORCE ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales FROM products p, sales s WHERE p.prod_id = s.prod_id GROUP BY prod_name HAVING SUM(s.amount_sold) BETWEEN 5000 AND 50000;
したがって、次の問合せはリライトできます。
SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales FROM products p, sales s WHERE p.prod_id = s.prod_id GROUP BY prod_name HAVING SUM(s.amount_sold) BETWEEN 10000 AND 20000;
この問合せは、次のようにリライトされます。
SELECT mv.prod_name, mv.dollar_sales FROM product_sales_mv mv WHERE mv.dollar_sales BETWEEN 10000 AND 20000;
マテリアライズド・ビューにINリストが含まれる際、クエリー・リライトを使用できます。たとえば、次のマテリアライズド・ビューの定義があるとします。
CREATE MATERIALIZED VIEW popular_promo_sales_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT p.promo_name, SUM(s.amount_sold) AS sum_amount_sold
FROM promotions p, sales s
WHERE s.promo_id = p.promo_id
AND p.promo_name IN ('coupon', 'premium', 'giveaway')
GROUP BY promo_name;
次の問合せはリライトできます。
SELECT p.promo_name, SUM(s.amount_sold)
FROM promotions p, sales s
WHERE s.promo_id = p.promo_id AND p.promo_name IN ('coupon', 'premium')
GROUP BY p.promo_name;
この問合せは、次のようにリライトされます。
SELECT * FROM popular_promo_sales_mv mv
WHERE mv.promo_name IN ('coupon', 'premium');
PCTリライトでは、オプティマイザは、一部のみが最新のマテリアライズド・ビューを使用して、問合せを最新のデータで正確にリライトできます。そのために、ディテール表内で更新されたパーティションが追跡されます。その次に、ディテール表内の更新されたパーティションに基づくマテリアライズド・ビュー内の行が追跡されます。これにより、オプティマイザは、マテリアライズド・ビューの最新と認識される部分を使用できるようになります。最新かどうかに関する詳細は、DBA_MVIEWSビュー、DBA_DETAIL_RELATIONSビューおよびDBA_MVIEW_DETAIL_PARTITIONビューで確認できます。これらのビューの使用例は「パーティションの最新状態の表示」を参照してください。
オプティマイザでは、QUERY_REWRITE_INTEGRITY = ENFORCEDモードまたはTRUSTEDモードでPCTリライトを使用します。STALE_TOLERATEDモードでは、データが最新かどうかが考慮されないため、PCTリライトは使用されません。また、PCTリライトが実行されるためにはWHERE句が必要です。
PCTリライトは、パーティション化を伴う場合でも使用できますが、ハッシュ・パーティション化はサポートされていません。次の項では、PCTの使用における側面について説明します。
次に示すのは、マテリアライズド・ビューのPCTがパーティション・キーで有効になっており、さらに基になる実表がtimeキーでレンジ・パーティション化されている場合のPCTリライトの例です。
CREATE TABLE part_sales_by_time (time_id, prod_id, amount_sold,
quantity_sold)
PARTITION BY RANGE (time_id)
(
PARTITION old_data
VALUES LESS THAN (TO_DATE('01-01-1999', 'DD-MM-YYYY'))
PCTFREE 0
STORAGE (INITIAL 8M),
PARTITION quarter1
VALUES LESS THAN (TO_DATE('01-04-1999', 'DD-MM-YYYY'))
PCTFREE 0
STORAGE (INITIAL 8M),
PARTITION quarter2
VALUES LESS THAN (TO_DATE('01-07-1999', 'DD-MM-YYYY'))
PCTFREE 0
STORAGE (INITIAL 8M),
PARTITION quarter3
VALUES LESS THAN (TO_DATE('01-10-1999', 'DD-MM-YYYY'))
PCTFREE 0
STORAGE (INITIAL 8M),
PARTITION quarter4
VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY'))
PCTFREE 0
STORAGE (INITIAL 8M),
PARTITION max_partition
VALUES LESS THAN (MAXVALUE)
PCTFREE 0
STORAGE (INITIAL 8M)
)
AS
SELECT s.time_id, s.prod_id, s.amount_sold, s.quantity_sold
FROM sales s;
ここで、1日ごとに販売された製品の総数を含むマテリアライズド・ビューを作成します。
CREATE MATERIALIZED VIEW sales_in_1999_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT s.time_id, s.prod_id, p.prod_name, SUM(quantity_sold)
FROM part_sales_by_time s, products p
WHERE p.prod_id = s.prod_id
AND s.time_id BETWEEN TO_DATE('01-01-1999', 'DD-MM-YYYY')
AND TO_DATE('31-12-1999', 'DD-MM-YYYY')
GROUP BY s.time_id, s.prod_id, p.prod_name;
次に示す問合せが、マテリアライズド・ビューsales_in_1999_mvでリライトされます。
SELECT s.time_id, p.prod_name, SUM(quantity_sold)
FROM part_sales_by_time s, products p
WHERE p.prod_id = s.prod_id
AND s.time_id < TO_DATE(''01-02-1999'', ''DD-MM-YYYY'')
AND s.time_id >= TO_DATE(''01-01-1999'', ''DD-MM-YYYY'')
GROUP BY s.time_id, p.prod_name');
part_sales_by_timeのquarter4に行を追加する場合は、次のようになります。
INSERT INTO part_sales_by_time
VALUES (TO_DATE('26-12-1999', 'DD-MM-YYYY'),38920,2500, 20);
commit;
このとき、マテリアライズド・ビューsales_in_1999_mvは失効状態になります。PCTリライトを使用すると、マテリアライズド・ビューの最新部分のデータのみを要求する問合せをリライトできます。マテリアライズド・ビューsales_in_1999_mvは、SELECT句およびGROUP BY句にtime_idが含まれています。したがってPCTは有効になっており、次の問合せはquarter4のデータを要求しないように正しくリライトされます。
SELECT s.time_id, p.prod_name, SUM(quantity_sold)
FROM part_sales_by_time s, products p
WHERE p.prod_id = s.prod_id
AND s.time_id < TO_DATE(''01-07-1999'', ''DD-MM-YYYY'')
AND s.time_id >= TO_DATE(''01-03-1999'', ''DD-MM-YYYY'')
GROUP BY s.time_id, p.prod_name');
複数のマテリアライズド・ビューによるリライトが無効になっている場合、次の問合せはリライトできません。デフォルトでは、複数のマテリアライズド・ビューによるリライトが有効になっているため、次の問合せはマテリアライズド・ビューと実表を使用してリライトされます。
SELECT s.time_id, p.prod_name, SUM(quantity_sold)
FROM part_sales_by_time s, products p
WHERE p.prod_id = s.prod_id
AND s.time_id < TO_DATE(''31-10-1999'', ''DD-MM-YYYY'') AND
s.time_id > TO_DATE(''01-07-1999'', ''DD-MM-YYYY'')
GROUP BY s.time_id, p.prod_name');
ディテール表がレンジ-リスト・パーティション化されている場合、このディテール表に依存するマテリアライズド・ビューは、パーティション化とサブパーティション化の両方のレベルでPCTをサポートできます。パーティション化キーとサブパーティション化キーの両方がマテリアライズド・ビューにある場合、より細かくPCTを実行できます。これにより、マテリアライズド・ビューのより細かな部分に対してマテリアライズド・ビューをリフレッシュでき、失効したマテリアライズド・ビューでより多くの問合せをリライトできるようになります。また、マテリアライズド・ビューにパーティション化キーしかない場合は、PCTは粗く実行されます。
次のレンジ-リスト・パーティション表を考えてみます。
CREATE TABLE sales_par_range_list
(calendar_year, calendar_month_number, day_number_in_month,
country_name, prod_id, prod_name, quantity_sold, amount_sold)
PARTITION BY RANGE (calendar_month_number)
SUBPARTITION BY LIST (country_name)
(PARTITION q1 VALUES LESS THAN (4)
(SUBPARTITION q1_America VALUES
('United States of America', 'Argentina'),
SUBPARTITION q1_Asia VALUES ('Japan', 'India'),
SUBPARTITION q1_Europe VALUES ('France', 'Spain', 'Ireland')),
PARTITION q2 VALUES LESS THAN (7)
(SUBPARTITION q2_America VALUES
('United States of America', 'Argentina'),
SUBPARTITION q2_Asia VALUES ('Japan', 'India'),
SUBPARTITION q2_Europe VALUES ('France', 'Spain', 'Ireland')),
PARTITION q3 VALUES LESS THAN (10)
(SUBPARTITION q3_America VALUES
('United States of America', 'Argentina'),
SUBPARTITION q3_Asia VALUES ('Japan', 'India'),
SUBPARTITION q3_Europe VALUES ('France', 'Spain', 'Ireland')),
PARTITION q4 VALUES LESS THAN (13)
(SUBPARTITION q4_America VALUES
('United States of America', 'Argentina'),
SUBPARTITION q4_Asia VALUES ('Japan', 'India'),
SUBPARTITION q4_Europe VALUES ('France', 'Spain', 'Ireland')))
AS SELECT t.calendar_year, t.calendar_month_number,
t.day_number_in_month, c1.country_name, s.prod_id,
p.prod_name, s.quantity_sold, s.amount_sold
FROM times t, countries c1, products p, sales s, customers c2
WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND
s.cust_id = c2.cust_id AND c2.country_id = c1.country_id AND
c1.country_name IN ('United States of America', 'Argentina',
'Japan', 'India', 'France', 'Spain', 'Ireland');
製品に関する各年の月ごとの合計売上高を持つ次のマテリアライズド・ビューsum_sales_per_year_month_mvを考えてみます。
CREATE MATERIALIZED VIEW sum_sales_per_year_month_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT s.calendar_year, s.calendar_month_number,
SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt
FROM sales_par_range_list s WHERE s.calendar_year > 1990
GROUP BY s.calendar_year, s.calendar_month_number;
sales_per_country_mvは、レンジ・パーティション化レベルでsales_par_range_listに対するPCTをサポートします。これは、レンジ・パーティション・キーcalendar_month_numberが、SELECTおよびGROUP BYリストにあるためです。
INSERT INTO sales_par_range_list VALUES (2001, 3, 25, 'Spain', 20, 'PROD20', 300, 20.50);
この文では、calendar_month_number = 3およびcountry_name = 'Spain'を含む行を挿入します。この行は、パーティションq1、サブパーティションEuropeに挿入されます。このINSERT文の後、sales_par_range_listのパーティションq1に関して、sum_sales_per_year_month_mvは失効します。したがって、次の文のように、sales_par_range_listのこのパーティションのデータにアクセスする入力問合せは、すべてリライトできません。
次の問合せでは、パーティションq1およびq2のデータにアクセスします。q1が更新され、マテリアライズド・ビューはq1に関して失効しているため、PCTリライトは使用できません。
SELECT s.calendar_year, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_range_list s WHERE s.calendar_year = 2000 AND s.calendar_month_number BETWEEN 2 AND 6 GROUP BY s.calendar_year;
次に、INSERT文の後にリライトを行う文の例を示します。これは最新のデータにアクセスするためです。
SELECT s.calendar_year, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_range_list s WHERE s.calendar_year = 2000 AND s.calendar_month_number BETWEEN 5 AND 9 GROUP BY s.calendar_year;
図19-3に、失効した部分と最新の部分を示します。
マテリアライズド・ビューのSELECTおよびGROUP BYにLISTパーティション化キーがある場合、PCTはマテリアライズド・ビューでサポートされます。サポートされているパーティション化のタイプに関係なく、ディテール表のパーティション・マーカーまたはROWIDがマテリアライズド・ビューにある場合、PCTは該当する特定のディテール表のマテリアライズド・ビューでサポートされます。
CREATE TABLE sales_par_list
(calendar_year, calendar_month_number, day_number_in_month,
country_name, prod_id, quantity_sold, amount_sold)
PARTITION BY LIST (country_name)
(PARTITION America
VALUES ('United States of America', 'Argentina'),
PARTITION Asia
VALUES ('Japan', 'India'),
PARTITION Europe
VALUES ('France', 'Spain', 'Ireland'))
AS SELECT t.calendar_year, t.calendar_month_number,
t.day_number_in_month, c1.country_name, s.prod_id,
s.quantity_sold, s.amount_sold
FROM times t, countries c1, sales s, customers c2
WHERE s.time_id = t.time_id and s.cust_id = c2.cust_id and
c2.country_id = c1.country_id and
c1.country_name IN ('United States of America', 'Argentina',
'Japan', 'India', 'France', 'Spain', 'Ireland');
マテリアライズド・ビューが表sales_par_listで作成され、その中にリスト・パーティション化キーがある場合、PCTリライトでは、このマテリアライズド・ビューをリライト対象として使用します。
この機能を理解するために、毎年の各国における全製品の合計売上高を持つマテリアライズド・ビューの作成例を次に示します。ビューは、ディテール表sales_par_listおよびproductsに依存しています。
CREATE MATERIALIZED VIEW sales_per_country_mv BUILD IMMEDIATE REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS SELECT s.calendar_year AS calendar_year, s.country_name AS country_name, p.prod_name AS prod_name, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt FROM sales_par_list s, products p WHERE s.prod_id = p.prod_id AND s.calendar_year <= 2000 GROUP BY s.calendar_year, s.country_name, prod_name;
sales_per_country_mvは、sales_par_listに対するPCTをサポートします。これは、リスト・パーティション・キーcountry_nameが、SELECTおよびGROUP BYリストにあるためです。表productsはパーティション化されません。したがってsales_per_country_mvはこの表に対するPCTをサポートしていません。
入力問合せがマテリアライズド・ビューの最新の部分にのみアクセスする場合に、sales_per_country_mvが失効していても、問合せは、(ENFORCEDモードまたはTRUSTEDモードで)sales_per_country_mvに関して、リライトできます。FRESHであるマテリアライズド・ビューの部分を判別できるのは、更新された表がマテリアライズド・ビューでPCTを使用できる場合のみです。したがって、PCTを使用できない表が更新された場合、マテリアライズド・ビューのFRESHの部分を判別できないため、特定のマテリアライズド・ビューの最新データによるリライトはできません。
sales_per_country_mvは、sales_par_listのPCTはサポートしますが、表productのPCTはサポートしません。表productsが更新されても、マテリアライズド・ビューのどの部分がFRESHであるかを識別できないため、sales_per_country_mvでのPCTリライトはできません。
次の文は、sales_par_listを更新します。
INSERT INTO sales_par_list VALUES (2000, 10, 22, 'France', 900, 20, 200.99);
この文では、表sales_par_list内のパーティションEuropeに行を挿入しています。この結果、sales_per_country_mvは失効しますが、このマテリアライズド・ビューは表sales_par_listに対するPCTをサポートするため、(ENFORCEDモードおよびTRUSTEDモードでの)PCTリライトが可能となります。マテリアライズド・ビューの最新の部分および失効した部分は、パーティション化されたディテール表sales_par_listに基づいて識別されます。
図19-4に、この例における最新の部分と失効した部分を示します。
次の問合せを考えてみます。
SELECT s.country_name, p.prod_name, SUM(s.amount_sold) AS sum_sales,
COUNT(*) AS cnt
FROM sales_par_list s, products p
WHERE s.prod_id = p.prod_id AND s.calendar_year = 2000
AND s.country_name IN ('United States of America', 'Japan')
GROUP BY s.country_name, p.prod_name;
この問合せでは、sales_par_listのパーティションAmericaおよびAsiaにアクセスします。これらのパーティションは未更新であり、この問合せのアクセス対象はマテリアライズド・ビューのFRESHの部分に限定されるため、失効したマテリアライズド・ビューsales_per_country_mvでリライトできます。
問合せは、次のように、sales_per_country_mvでリライトされます。
SELECT country_name, prod_name, SUM(sum_sales) AS sum_slaes, SUM(cnt) AS cnt
FROM sales_per_country_mv WHERE calendar_year = 2000
AND country_name IN ('United States of America', 'Japan')
GROUP BY country_name, prod_name;
次の問合せを考えてみます。
SELECT s.country_name, p.prod_name,
SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt
FROM sales_par_list s, products p
WHERE s.prod_id = p.prod_id AND s.calendar_year = 1999
AND s.country_name IN ('Japan', 'India', 'Spain')
GROUP BY s.country_name, p.prod_name;
この問合せでは、sales_par_listのパーティションEuropeおよびAsiaにアクセスします。パーティションEuropeは更新されていますが、マテリアライズド・ビューの必要なデータが失効しているため、この問合せはsales_per_country_mvでリライトできません。
入力問合せがマテリアライズド・ビューのFRESHの部分にアクセスする場合、DML、ダイレクト・ロード、パーティション・メンテナンス操作(PMOP)など、sales_par_listに対する任意の種類の更新後に、リライトできるようになります。
パーティション・マーカーを指定して、特定のパーティションのすべての行が同じpmarker値を持つと、クエリー・リライト機能が、ディテール表の全パーティションにアクセスする問合せのリライトに制限されます。つまり、ディテール表のパーティションの一部にアクセスする問合せの場合、マテリアライズド・ビューのFRESHの部分にデータが対応していても、リライトは行われません。この場合、マテリアライズド・ビューのFRESHの部分は、pmarker値によって決定されます。マテリアライズド・ビューの最新の行を決定するには、データの新しさとマーカー値を関連付けます。これにより、特定のpmarker値を持つマテリアライズド・ビューのすべての行が、FRESHまたはSTALEになります。
次に、各月について、sales_par_listの各ディテール表のパーティションにおける、全製品の合計売上高を表すマテリアライズド・ビューを作成する例を示します。次に示すように、このマテリアライズド・ビューはディテール表productsにも依存します。
CREATE MATERIALIZED VIEW sales_per_dt_partition_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT s.calendar_year AS calendar_year, p.prod_name AS prod_name,
DBMS_MVIEW.PMARKER(s.rowid) pmarker,
SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt
FROM sales_par_list s, products p
WHERE s.prod_id = p.prod_id AND s.calendar_year > 2000
GROUP BY s.calendar_year, DBMS_MVIEW.PMARKER(s.rowid), p.prod_name;
マテリアライズド・ビューsales_per_dt_partition_mvでは、ディテール表のパーティションごとの合計売上高を提供します。このマテリアライズド・ビューは、パーティション・マーカーがSELECT句およびGROUP BY句にあるため、表sales_per_listに対するPCTリライトをサポートします。表19-2に、この例でのパーティションの名前およびpmarkerを示します。
次のように、sales_par_listを更新します。
DELETE FROM sales_par_list WHERE country_name = 'India';
これで、表sales_par_listのパーティションAsiaから行が削除されました。この結果、sales_per_dt_partition_mvは失効しますが、このマテリアライズド・ビューは表sales_par_listに対するPCT(pmarkerベース)をサポートするため、(ENFORCEDモードおよびTRUSTEDモードでの)PCTリライトが可能となります。
次の問合せを考えてみます。
SELECT p.prod_name, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt
FROM sales_par_list s, products p
WHERE s.prod_id = p.prod_id AND s.calendar_year = 2001 AND
s.country_name IN ('United States of America', 'Argentina')
GROUP BY p.prod_name;
ディテール表のパーティションに対応するすべてのデータがアクセスされ、このデータに関してマテリアライズド・ビューはFRESHであるため、この問合せはsales_per_dt_partition_mvでリライトできます。この問合せは、未更新のパーティションAmericaのすべてのデータにアクセスします。
問合せは、次のように、sales_per_dt_partition_mvでリライトされます。
SELECT prod_name, SUM(sum_sales) AS sum_sales, SUM(cnt) AS cnt FROM sales_per_dt_partition_mv WHERE calendar_year = 2001 AND pmarker = 1000 GROUP BY prod_name;
マテリアライズド・ビューは、GROUP BY句があり、SELECTおよびGROUP BY句にパーティション・キーまたはパーティション・マーカーが指定されている場合、PCTリライトをサポートします。pmarkerまたはパーティション・キーのかわりに、パーティション表のROWIDを使用できます。この場合、内部的にROWIDがpmarkerに変換されます。次の表を考えてみます。
CREATE TABLE product_par_list
(prod_id, prod_name, prod_category,
prod_subcategory, prod_list_price)
PARTITION BY LIST (prod_category)
(PARTITION prod_cat1
VALUES ('Boys', 'Men'),
PARTITION prod_cat2
VALUES ('Girls', 'Women'))
AS
SELECT prod_id, prod_name, prod_category,
prod_subcategory, prod_list_price
FROM products;
表sales_par_listおよびproduct_par_listのマテリアライズド・ビューを作成する例を次に示します。
CREATE MATERIALIZED VIEW sum_sales_per_category_mv
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT p.rowid prid, p.prod_category,
SUM (s.amount_sold) sum_sales, COUNT(*) cnt
FROM sales_par_list s, product_par_list p
WHERE s.prod_id = p.prod_id and s.calendar_year <= 2000
GROUP BY p.rowid, p.prod_category;
pmarkerでのリライトに適用されるすべての制限が、ここでも適用されます。入力問合せは、リライト対象の問合せの全パーティションにアクセスする必要があります。この場合に使用されるpmarker表を次に示します。
product_par_list pmarker value ---------------- ------------- prod_cat1 1000 prod_cat2 1001 prod_cat3 1002
次のように、product_par_listを更新します。
DELETE FROM product_par_list WHERE prod_name = 'MEN';
この結果、product_par_listのパーティションprod_list1に関して、sum_sales_per_category_mvは失効します。
次の問合せを考えてみます。
SELECT p.prod_category, SUM(s.amount_sold) AS sum_sales, COUNT(*) AS cnt
FROM sales_par_list s, product_par_list p
WHERE s.prod_id = p.prod_id AND p.prod_category IN
('Girls', 'Women') AND s.calendar_year <= 2000
GROUP BY p.prod_category;
ディテール表のパーティションに対応するすべてのデータがアクセスされ、このデータに関してマテリアライズド・ビューはFRESHであるため、この問合せはsum_sales_per_category_mvでリライトできます。この問合せは、未更新のパーティションprod_cat2のすべてのデータにアクセスします。次に、sum_sales_per_category_mvでリライトされた問合せを示します。
SELECT prod_category, sum_sales, cnt FROM sum_sales_per_category_mv WHERE DBMS_MVIEW.PMARKER(srid) IN (1000) GROUP BY prod_category;
クエリー・リライトは、複数のマテリアライズド・ビューを使用した問合せのリライトができるように拡張されました。クエリー・リライトにおいて、すべてのデータを戻すマテリアライズド・ビューのセットがないと判断された場合、残りのデータは実表から取得されます。
複数のマテリアライズド・ビューを使用したクエリー・リライトでは、PCTやINリストを使用したリライトなど、様々なタイプのリライトを活用できる他、それらを組み合せて使用することもできます。以下では、現在クエリー・リライトが可能な問合せについて、具体例をいくつか取り上げます。
2つのマテリアライズド・ビュー、cust_avg_credit_mv1とcust_avg_credit_mv2について考えてみます。cust_avg_credit_mv1では、各郵便番号について、1940年から1950年に生まれたすべての顧客の平均与信限度額を問い合せます。cust_avg_credit_mv2では、各郵便番号について、1951年から1970年に生まれた顧客の平均与信限度額を問い合せます。
この例で定義されるマテリアライズド・ビューを次に示します。
CREATE MATERIALIZED VIEW cust_avg_credit_mv1
ENABLE QUERY REWRITE
AS SELECT cust_postal_code, cust_year_of_birth,
SUM(cust_credit_limit) AS sum_credit,
COUNT(cust_credit_limit) AS count_credit
FROM customers
WHERE cust_year_of_birth BETWEEN 1940 AND 1950
GROUP BY cust_postal_code, cust_year_of_birth;
CREATE MATERIALIZED VIEW cust_avg_credit_mv2
ENABLE QUERY REWRITE
AS SELECT cust_postal_code, cust_year_of_birth,
SUM(cust_credit_limit) AS sum_credit,
COUNT(cust_credit_limit) AS count_credit
FROM customers
WHERE cust_year_of_birth > 1950 AND cust_year_of_birth <= 1970
GROUP BY cust_postal_code, cust_year_of_birth;
問合せ1: マテリアライズド・ビューと問合せにおける1つの範囲の一致
各郵便番号について、1940年から1970年に生まれたすべての顧客の平均与信限度額を問い合せる問合せを考えてみます。この問合せは、cust_year_of_birthに対してBETWEENで指定した範囲と一致します。
SELECT cust_postal_code, AVG(cust_credit_limit) AS avg_credit FROM customers c WHERE cust_year_of_birth BETWEEN 1940 AND 1970 GROUP BY cust_postal_code;
前述の問合せは、次のように、すべてのデータを取得するために、2つのマテリアライズド・ビューでリライトできます。
SELECT v1.cust_postal_code, SUM(v1.sum_credit)/SUM(v1.count_credit) AS avg_credit FROM (SELECT cust_postal_code, sum_credit, count_credit FROM cust_avg_credit_mv1 GROUP BY cust_postal_code UNION ALL SELECT cust_postal_code, sum_credit, count_credit FROM cust_avg_credit_mv2 GROUP BY cust_postal_code) v1 GROUP BY v1.cust_postal_code;
インライン・ビューにUNION ALLの問合せが使用されているのは、再集計の必要があるためです。また、クエリー・リライトがどのように集計をカウントしてこのロールアップを実行したかを確認してください。
問合せ2: マテリアライズド・ビューに含まれるデータ外の問合せ
マテリアライズド・ビューで指定している範囲が、問合せで問い合せる範囲を超える場合、リライトされる問合せにはフィルタ(選択述語)が追加され、マテリアライズド・ビューによって戻される不要な行が削除されます。これは、次のような問合せの場合です。
SELECT cust_postal_code, SUM(cust_credit_limit) AS sum_credit FROM customers c WHERE cust_year_of_birth BETWEEN 1945 AND 1955 GROUP BY cust_postal_code;
問合せ2は、次のようにリライトされます。
SELECT v1.cust_postal_code, SUM(v1.sum_credit) FROM (SELECT cust_postal_code, SUM(sum_credit) AS sum_credit FROM cust_avg_credit_mv1 WHERE cust_year_of_birth BETWEEN 1945 AND 1950 GROUP BY cust_postal_code UNION ALL SELECT cust_postal_code, SUM(sum_credit) AS sum_credit FROM cust_birth_mv2 WHERE cust_year_of_birth > 1950 AND cust_year_of_birth <= 1955 GROUP BY cust_postal_code) v1 GROUP BY v1.cust_postal_code;
問合せ3: マテリアライズド・ビューよりも多くのデータの問合せ
問合せで、前述の2つのマテリアライズド・ビューに含まれているデータよりも多くのデータを問い合せる場合を考えます。その場合でも、両方のマテリアライズド・ビューおよび実表のデータを使用してリライトされます。次の例では、集計がないマテリアライズド・ビューの新しいセットを定義し、両方のマテリアライズド・ビューおよび実表のデータを使用してリライトを行います。
CREATE MATERIALIZED VIEW cust_birth_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_year_of_birth FROM customers WHERE cust_year_of_birth BETWEEN 1940 AND 1950; CREATE MATERIALIZED VIEW cust_avg_credit_mv2 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_year_of_birth FROM customers WHERE cust_year_of_birth > 1950 AND cust_year_of_birth <= 1970;
この問合せでは、1940年から1990年に生まれたすべての顧客を必要としています。
SELECT cust_last_name, cust_first_name FROM customers c WHERE cust_year_of_birth BETWEEN 1940 AND 1990;
クエリー・リライトは、1971年から1990年に生まれた顧客にアクセスするため、実表にアクセスする必要があります。したがって、問合せ3は次のようにリライトされます。
SELECT cust_last_name, cust_first_name FROM cust_birth_mv1 UNION ALL SELECT cust_last_name, cust_first_name FROM cust_birth_mv2 UNION ALL SELECT cust_last_name, cust_first_name FROM customers c WHERE cust_year_of_birth > 1970 AND cust_year_of_birth <= 1990;
問合せ4: 複数の選択列のデータの問合せ
次に、1945年から1960年に生まれ、与信限度額が1,000から10,000のすべての顧客を問い合せる問合せを考えてみます。これは、複数の選択列のデータを問い合せるため、複数選択の問合せです。
SELECT cust_last_name, cust_first_name FROM customers WHERE cust_year_of_birth BETWEEN 1945 AND 1960 AND cust_credit_limit BETWEEN 1000 AND 10000;
図19-5は、2つの選択問合せを示します。これらは、次の項で説明する2つの選択マテリアライズド・ビューでリライトできます。
図19-5のグラフは、この問合せを満たすために使用できるマテリアライズド・ビューを表したものです。credit_mv1は、1945から1950年生まれで、与信限度額が1,000から5,000の顧客を問い合せます。credit_mv2は、1945から1960年生まれで、与信限度額が5,000より多く、10,000以下の顧客を問い合せます。credit_mv3は、1951から1955年生まれで、与信限度額が1,000から5,000の顧客を問い合せます。
この場合のマテリアライズド・ビューの定義を次に示します。
CREATE MATERIALIZED VIEW credit_mv1
ENABLE QUERY REWRITE
AS SELECT cust_last_name, cust_first_name,
cust_credit_limit, cust_year_of_birth
FROM customers
WHERE cust_credit_limit BETWEEN 1000 AND 5000
AND cust_year_of_birth BETWEEN 1945 AND 1950;
CREATE MATERIALIZED VIEW credit_mv2
ENABLE QUERY REWRITE
AS SELECT cust_last_name, cust_first_name,
cust_credit_limit, cust_year_of_birth
FROM customers
WHERE cust_credit_limit > 5000
AND cust_credit_limit <= 10000 AND cust_year_of_birth
BETWEEN 1945 AND 1960;
CREATE MATERIALIZED VIEW credit_mv3
ENABLE QUERY REWRITE AS
SELECT cust_last_name, cust_first_name,
cust_credit_limit, cust_year_of_birth
FROM customers
WHERE cust_credit_limit BETWEEN 1000 AND 5000
AND cust_year_of_birth > 1950 AND cust_year_of_birth <= 1955;
問合せ4は、ほとんどのデータにアクセスするこれら3つのマテリアライズド・ビューを使用してリライトできます。ただし、一部のデータはこれら3つのマテリアライズド・ビューからは取得できないため、クエリー・リライトは実表にもアクセスして、1955年から1960年生まれで、与信限度額が1,000から5,000である顧客のデータを取得します。次のようにリライトされます。
SELECT cust_last_name, cust_first_name FROM credit_mv1 UNION ALL SELECT cust_last_name, cust_first_name FROM credit_mv2 UNION ALL SELECT cust_last_name, cust_first_name FROM credit_mv3 UNION ALL SELECT cust_last_name, cust_first_name FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 5000 AND cust_year_of_birth > 1955 AND cust_year_of_birth <= 1960;
この例では、複数のマテリアライズド・ビューを使用して複数選択の問合せをリライトする方法を示しています。この例は、3つのマテリアライズド・ビュー間でデータが重複しないように単純化されています。これに対し、クエリー・リライトでは同様のリライトを実行できます。
問合せ5: 範囲および制約範囲
この例では、単一選択マテリアライズド・ビューを使用して複数選択の問合せをリライトする方法を示します。この例では、問合せに2つの範囲指定があり、マテリアライズド・ビューには1つの制約範囲指定があります。問合せでは、1945年から1960年生まれで、与信限度額が1,000から10,000の顧客を問い合せます。一方、credit_mv1では与信限度額が1,000から5,000の顧客のみを問い合せるとします。credit_mv1はcust_year_of_birthの選択述語で制約されていないので、問合せの出生年値の全範囲をカバーしています。
図19-6では、下部の領域がcredit_mv1のデータを表しています。
新しいcredit_mv1は、次のように定義されます。
CREATE MATERIALIZED VIEW credit_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 5000;
問合せは次のとおりです。
SELECT cust_last_name, cust_first_name FROM customers WHERE cust_year_of_birth BETWEEN 1945 AND 1960 AND cust_credit_limit BETWEEN 1000 AND 10000;
最終的にリライトされた問合せは、次のようになります。
SELECT cust_last_name, cust_first_name FROM credit_mv1 WHERE cust_year_of_birth BETWEEN 1945 AND 1960 UNION ALL SELECT cust_last_name, cust_first_name FROM customers WHERE cust_year_of_brith BETWEEN 1945 AND 1960 AND cust_credit_limit > 5000 AND cust_credit_limit <= 10000;
問合せ6: 単一列のINリストが指定された問合せと単一列の範囲が指定されたマテリアライズド・ビュー
複数のマテリアライズド・ビューのクエリー・リライトでは、入力問合せのINリストを処理し、同じ選択列の範囲指定を持つマテリアライズド・ビューで問合せをリライトできます。前述の範囲指定のみの例に対する自然な流れとして、INリストでは範囲内の離散値を表すものとします。
次の例では、1つの列のINリストの選択述語を持つ問合せと、1つの列の範囲選択述語を持つマテリアライズド・ビューを示します。1945、1950、1955、1960、1965、1970または1975年に生まれた、各国の顧客数を問い合せる問合せを考えてみます。この問合せは、cust_year_of_birthのINリストで制約されます。
SELECT c2.country_name, count(c1.country_id) FROM customers c1, countries c2 WHERE c1.country_id = c2.country_id AND c1.cust_year_of_birth IN (1945, 1950, 1955, 1960, 1965, 1970, 1975) GROUP BY c2.country_name;
次の2つのマテリアライズド・ビューを考えてみます。cust_country_birth_mv1では、1940年から1950年に生まれた、各国の顧客数を問い合せます。cust_country_birth_mv2では、1951年から1970年に生まれた、各国の顧客数を問い合せます。前述の問合せは、1945、1950、1955、1960、1965および1970年生まれの各国の顧客総数を取得するために、これら2つのマテリアライズド・ビューでリライトできます。1975年生まれの顧客数を取得するためには、実表にアクセスする必要があります。
この例で定義されるマテリアライズド・ビューを次に示します。
CREATE MATERIALIZED VIEW cust_country_birth_mv1
ENABLE QUERY REWRITE
AS SELECT c2.country_name, c1.cust_year_of_birth,
COUNT(c1.country_id) AS count_customers
FROM customers c1, countries c2
WHERE c1.country_id = c2.country_id AND
cust_year_of_birth BETWEEN 1940 AND 1950
GROUP BY c2.country_name, c1.cust_year_of_birth;
CREATE MATERIALIZED VIEW cust_country_birth_mv2
ENABLE QUERY REWRITE
AS SELECT c2.country_name, c1.cust_year_of_birth,
COUNT(c1.country_id) AS count_customers
FROM customers c1, countries c2
WHERE c1.country_id = c2.country_id AND cust_year_of_birth > 1950
AND cust_year_of_birth <= 1970
GROUP BY c2.country_name, c1.cust_year_of_birth;
したがって、問合せ6は次のようにリライトされます。
SELECT v1.country_name, SUM(v1.count_customers) FROM (SELECT country_name, SUM(count_customers) AS count_customers FROM cust_country_birth_mv1 WHERE cust_year_of_birth IN (1945, 1950) GROUP BY country_name UNION ALL SELECT country_name, SUM(count_customers) AS count_customers FROM cust_country_birth_mv2 WHERE cust_year_of_birth IN (1955, 1960, 1965, 1970) GROUP BY country_name UNION ALL SELECT c2.country_name, COUNT(c1.country_id) AS count_customers FROM customers c1, countries c2 WHERE c1.country_id = c2.country_id AND cust_year_of_birth IN (1975) GROUP BY c2.country_name) v1 GROUP BY v1.country_name;
問合せ7: 複数のマテリアライズド・ビューを使用したPCTリライト
複数のマテリアライズド・ビューを使用したリライトでも、PCTリライトを利用できます。PCTリライトは、マテリアライズド・ビューの失効時にマテリアライズド・ビューの最新部分のみを使用して問合せをリライトする機能です。この機能は、ENFORCEDまたはTRUSTEDの整合性モード、および、複数のマテリアライズド・ビューによるリライトで使用され、最新データはマテリアライズド・ビューの最新部分から取得し、失効データは実表から取得できます。したがって、リライトされる問合せでは、1つ以上のマテリアライズド・ビューの最新部分のみに対してUNION ALLを行い、残りのデータを実表から取得して、結果を戻します。そのため、ここではPCTのすべての規則と条件も適用されます。マテリアライズド・ビューではPCTを有効化し、実表に対する変更は、マテリアライズド・ビューの最新部分と失効部分を明確に識別できるように実行する必要があります。
この例では、1945年から1964年生まれで、与信限度額が1,000から10,000の顧客を問い合せる問合せを考えます。また、顧客(customers)表はcust_date_of_birthでパーティション化されており、PCTを有効化したマテリアライズド・ビューcredit_mv1でも、1945年から1964年生まれで、与信限度額が1,000から10,000の顧客を問い合せるものとします。
SELECT cust_last_name, cust_first_name FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 10000;
図19-7では、実表のパーティションp1からp6に関して、マテリアライズド・ビューの最新部分および失効部分をそれぞれグレーと白で示しています。
ここでは、ENFORCEDモードで、顧客表のp1、p2、p3、p5およびp6が最新部分、パーティションp4が失効部分だとします。これは、問合せに回答するのにcredit_mv1のすべてのパーティションが使用できないことを意味します。リライトされる問合せでは、顧客パーティションp4の結果を他の特定のマテリアライズド・ビューから取得するか、この例で示すように、実表から取得する必要があります。次に、表がどのようにパーティション化されているのかがわかるcustomers表の表定義の一部を示します。
CREATE TABLE customers (PARTITION BY RANGE (cust_year_of_birth) PARTITION p1 VALUES LESS THAN (1945), PARTITION p2 VALUES LESS THAN (1950), PARTITION p3 VALUES LESS THAN (1955), PARTITION p4 VALUES LESS THAN (1960), PARTITION p5 VALUES LESS THAN (1965), PARTITION p6 VALUES LESS THAN (1970);
次に、前述の例のマテリアライズド・ビューの定義を示します。
CREATE MATERIALIZED VIEW credit_mv1 ENABLE QUERY REWRITE AS SELECT cust_last_name, cust_first_name, cust_credit_limit, cust_year_of_birth FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 10000 AND cust_year_of_birth BETWEEN 1945 AND 1964;
このマテリアライズド・ビューでは、表customersに関してPCTが有効化されています。
リライトされた問合せは、次のようになります。
SELECT cust_last_name, cust_first_name FROM credit_mv1
WHERE cust_credit_limit BETWEEN 1000 AND 10000 AND
(cust_year_of_birth >= 1945 AND cust_year_of_birth < 1955 OR
cust_year_of_birth BETWEEN 1945 AND 1964)
UNION ALL
SELECT cust_last_name, cust_first_name
FROM customers WHERE cust_credit_limit BETWEEN 1000 AND 10000
AND cust_year_of_birth < 1960 AND cust_year_of_birth >= 1955;
ここでは、クエリー・リライトが可能なその他のケースについて説明します。
クエリー・リライトでは、ネステッド・マテリアライズド・ビューが繰り返し利用されます。Oracle Databaseでは、まず、集計および結合を持つマテリアライズド・ビューを使用して、クエリー・リライトを試みます。次に、結合のみを含むマテリアライズド・ビューを使用して試みます。いずれかのリライトが成功した場合、Oracleはリライトがなくなるまで、そのプロセスを繰り返します。たとえば、次のようにマテリアライズド・ビューjoin_sales_time_product_mvとsum_sales_time_product_mvを作成したとします。
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 sum_sales_time_product_mv
ENABLE QUERY REWRITE AS
SELECT mv.prod_name, mv.week_ending_day, COUNT(*) cnt_all,
SUM(mv.amount_sold) sum_amount_sold,
COUNT(mv.amount_sold) cnt_amount_sold
FROM join_sales_time_product_mv mv
GROUP BY mv.prod_name, mv.week_ending_day;
次の問合せを考えてみます。
SELECT p.prod_name, t.week_ending_day, SUM(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 p.prod_name, t.week_ending_day;
Oracleは、join_sales_time_product_mvがリライトに適していると判断します。リライトされた問合せの形式は、次のとおりです。
SELECT mv.prod_name, mv.week_ending_day, SUM(mv.amount_sold) FROM join_sales_time_product_mv mv GROUP BY mv.prod_name, mv.week_ending_day;
リライトが発生したため、Oracleはこのプロセスを再度試みます。ここでは、前述の問合せは、単一表集計マテリアライズド・ビューsum_sales_store_timeを使用して、次の形式にリライトされます。
SELECT mv.prod_name, mv.week_ending_day, mv.sum_amount_sold FROM sum_sales_time_product_mv mv;
Oracle Databaseでは、次の2つの場合にインライン・ビューによるクエリー・リライトをサポートしています。
マテリアライズド・ビューに含まれるインライン・ビューのテキストが問合せのテキストと完全に一致している場合
問合せの中に含まれるインライン・ビューが、マテリアライズド・ビューに含まれるインライン・ビューと同等である場合
2つのインライン・ビューが同等であるとみなされるのは、それぞれのSELECT構文のリストおよびGROUP BYリストが同等であり、FROM句に同一または同等のオブジェクトが含まれ、WHERE句の中のすべての選択述語を含む結合グラフが同等であり、さらにHAVING句が同等である場合です。
次の例は、インライン・ビューを持つ問合せに対し、テキスト一致リライトおよび一般的なインライン・ビュー・リライトを使用したマテリアライズド・ビューによるリライトがどのように行われるかを示します。インライン・ビューを含む次のマテリアライズド・ビューを考えてみます。
CREATE MATERIALIZED VIEW SUM_SALES_MV ENABLE QUERY REWRITE AS SELECT mv_iv.prod_id, mv_iv.cust_id, sum(mv_iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE sales.prod_id = products.prod_id) MV_IV GROUP BY mv_iv.prod_id, mv_iv.cust_id;
次の問合せに含まれるインライン・ビューのテキストは、マテリアライズド・ビューのインライン・ビューのテキストと完全に一致しています。そのためこの問合せでは、リライトが行えるように、インライン・ビューが内部的にマテリアライズド・ビューのインライン・ビューと置き換えられます。
SELECT iv.prod_id, iv.cust_id, SUM(iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE sales.prod_id = products.prod_id) IV GROUP BY iv.prod_id, iv.cust_id;
次の問合せに含まれるインライン・ビューのテキストは、前述のマテリアライズド・ビューに含まれるインライン・ビューのテキストと完全には一致していません。問合せのインライン・ビューの中の結合述語が置き換えられていることに注意してください。この問合せのテキストは、マテリアライズド・ビューに含まれるインライン・ビューのテキストに一致していませんが、クエリー・リライトでは、この問合せのインライン・ビューが、マテリアライズド・ビューに含まれるインライン・ビューと同等であると判断されます。ここでも同様に、この問合せに対してリライトが行えるよう、問合せのインライン・ビューが内部的にマテリアライズド・ビューのインライン・ビューと置き換えられます。
SELECT iv.prod_id, iv.cust_id, SUM(iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE products.prod_id = sales.prod_id) IV GROUP BY iv.prod_id, iv.cust_id;
これらの問合せはいずれも、次のようにSUM_SALES_MVでリライトされます。
SELECT prod_id, cust_id, sum_amount_sold FROM SUM_SALES_MV;
集合演算子、GROUPING SET句、ネストした副問合せ、ネストしたインライン・ビュー、およびリモート表を含む問合せでは、一般的なインライン・ビュー・リライトはサポートされていません。
Oracle Databaseは、単一のリモート・データベース・サイトの表を参照するマテリアライズド・ビューでのクエリー・リライトをサポートしています。ただしマテリアライズド・ビューは、問合せの発行が行われているサイトに存在する必要があります。リモート表の更新内容は、即座にはローカル・サイトに伝播されないため、クエリー・リライトはstale_toleratedモードでのみ実行されます。マテリアライズド・ビューにはない列が含まれている問合せでは、後戻り結合という手法を使用してクエリー・リライトが行われます。ただし、後戻り結合表がローカル・サイトにない場合は、クエリー・リライトは実行されません。また、リモート表の制約情報はリモート・サイトでは使用できないため、クエリー・リライトでは制約情報は使用されません。
次の問合せには、単一のリモート・サイトにある表が含まれています。
SELECT p.prod_id, t.week_ending_day, s.cust_id,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales@remotedbl s, products@remotedbl p, times@remotedbl 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_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@remotedbl s, products@remotedbl p, times@remotedbl 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;
この問合せはリモート表を参照しますが、そのリライトは次のように前述のマテリアライズド・ビューを使用して行われます。
SELECT prod_id, week_ending_day, cust_id, sum_amount_sold FROM sum_sales_prod_week_mv;
同じ表に対する複数の参照または自己結合を含む問合せのクエリー・リライトを行うには、2つの異なる方法があります。第1の方法では、問合せとマテリアライズド・ビューの定義で、表に対する複数の参照の別名が同じであるようにする必要があります。別名が異なる場合は、Oracleは第2の方法を試みます。この方法では、問合せの複数の参照がマテリアライズド・ビューの複数の参照に一致するように、問合せの結合とマテリアライズド・ビューの結合が比較されます。
マテリアライズド・ビューと問合せの例を次に示します。この例では、問合せには表の列の参照がないため、テキストの完全一致は機能しません。ただし、表の参照の別名は一致するため、一般的なクエリー・リライトは可能です。
shサンプル・スキーマで自己結合のリライトの可能性を示すために、ファクト表にある実際の出荷日と支払日を含むように、次のような追加が行われ、同じディメンション表timesを参照しているとします。これはあくまでも例であり、結果は戻されません。
ALTER TABLE sales ADD (time_id_ship DATE); ALTER TABLE sales ADD (CONSTRAINT time_id_book_fk FOREIGN key (time_id_ship) REFERENCES times(time_id) ENABLE NOVALIDATE); ALTER TABLE sales MODIFY CONSTRAINT time_id_book_fk RELY; ALTER TABLE sales ADD (time_id_paid DATE); ALTER TABLE sales ADD (CONSTRAINT time_id_paid_fk FOREIGN KEY (time_id_paid) REFERENCES times(time_id) ENABLE NOVALIDATE); ALTER TABLE sales MODIFY CONSTRAINT time_id_paid_fk RELY;
これで、マテリアライズド・ビューを次のように定義できます。
CREATE MATERIALIZED VIEW sales_shipping_lag_mv
ENABLE QUERY REWRITE AS
SELECT t1.fiscal_week_number, s.prod_id,
t2.fiscal_week_number - t1.fiscal_week_number AS lag
FROM times t1, sales s, times t2
WHERE t1.time_id = s.time_id AND t2.time_id = s.time_id_ship;
次の問合せはテキストの完全一致テストにはパスしませんが、表の別名が一致するためリライトされます。
SELECT s.prod_id, t2.fiscal_week_number - t1.fiscal_week_number AS lag FROM times t1, sales s, times t2 WHERE t1.time_id = s.time_id AND t2.time_id = s.time_id_ship;
Oracle Databaseでは、問合せ内で複数インスタンス化された表のインスタンスが、マテリアライズド・ビュー内の対応する表のインスタンスと正確に一致することを保証するために、他のチェックを実行することに注意してください。たとえば、次の例では、表timesの複数インスタンスで使用される別名は、マテリアライズド・ビューの表timesの複数インスタンスと一致しないと判断されます。
次の問合せでは、複数インスタンス化された表timeの別名は一致しますが、t2で別名化されたtimeのインスタンス間の結合条件とは互換性がないため、sales_shipping_lag_mvを使用してリライトできません。
SELECT s.prod_id, t2.fiscal_week_number - t1.fiscal_week_number AS lag FROM times t1, sales s, times t2 WHERE t1.time_id = s.time_id AND t2.time_id = s.time_id_paid;
この問合せは、t2で別名化されたtime表のインスタンスをs.time_id_paid列で結合しますが、マテリアライズド・ビューは、t2で別名化されたtime表のインスタンスをs.time_id_ship列で結合します。結合条件が異なるため、Oracleではリライトできないことが適切に判断されます。
次の問合せでは、表timesのマテリアライズド・ビューsales_shipping_lag_mvにおいて一致する別名がありません。ただし、クエリー・リライトによって問合せとマテリアライズド・ビューの結合が比較され、timesの複数インスタンスと正しく一致します。
SELECT s.prod_id, x2.fiscal_week_number - x1.fiscal_week_number AS lag FROM times x1, sales s, times x2 WHERE x1.time_id = s.time_id AND x2.time_id = s.time_id_ship;
デート・フォールディングによるリライトは、式の一致によるリライトの特別な形式です。このリライトでは、問合せの日付範囲が、より大きい単位の日付で表される同等の日付範囲にフォールドされます。フォールドされた日付範囲にある大きい単位の日付で表された式は、マテリアライズド・ビューの等価の式と一致します。より大きい単位(月、四半期、年など)の日付への日付範囲のフォールドは、列の基礎となるデータ型がOracle DATEの場合に実行されます。式の一致は、式の標準形の使用をベースに実行されます。
DATEは組込みデータ型で、秒、日、月などの順序付けられた時間単位を表し、時間階層(秒->分->時間->日->月->四半期->年)が組み込まれています。DATEについてのハードコード化されたこの情報が、小さい単位の日付から大きい単位の日付に日付範囲をフォールドするときに使用されます。つまり、日付値は、月、四半期、年の初め、または月、四半期、年の終わりにフォールドできます。たとえば、日付値1-jan-1999は、年1999、四半期1999-1または月1999-01のいずれかの最初にフォールドできます。また、日付値30-sep-1999は、四半期1999-03または月1999-09のいずれかの最後にフォールドできます。
|
注意: デート・フォールディングのしくみにより、BETWEENおよび日付列を使用する際には注意が必要です。BETWEENおよび日付列を使用するには、後の日付を1日増やすのが最善の方法です。つまり、date_col BETWEEN '1-jan-1999' AND '30-jun-1999'を使用するかわりに、date_col BETWEEN '1-jan-1999' AND '1-jul-1999'を使用してください。また、TRUNC(date_col) BETWEEN '1-jan-1999' AND '30-jun-1999'のように、TRUNC関数を使用しても同等の結果を取得できます。ただし、TRUNCでは、時間値が取り除かれます。 |
日付値は順序付けされているため、日付範囲の方がより大きい単位の整数を表している場合、日付列に対して指定されている範囲述語は小さい単位から大きい単位にフォールドできます。たとえば、範囲述語date_col >= '1-jan-1999' AND date_col < '30-jun-1999'は、日付値から特定の日付コンポーネントを抽出するTO_CHAR関数を使用して、月範囲または四半期範囲にフォールドできます。
日付値のフォールドによってデータを集計するメリットは、データを圧縮できることです。デート・フォールディングが実行されない場合、データは最小単位で集計されます。その結果、データの格納に必要なディスク領域が多くなり、マテリアライズド・ビューをスキャンするためのI/Oも増加します。
1998年の製品別の売上合計を問い合せる問合せを考えてみます。
SELECT p.prod_category, SUM(s.amount_sold)
FROM sales s, products p
WHERE s.prod_id=p.prod_id AND s.time_id >= TO_DATE('01-jan-1998', 'dd-mon-yyyy')
AND s.time_id < TO_DATE('01-jan-1999', 'dd-mon-yyyy')
GROUP BY p.prod_category;
CREATE MATERIALIZED VIEW sum_sales_pcat_monthly_mv
ENABLE QUERY REWRITE AS
SELECT p.prod_category, TO_CHAR(s.time_id,'YYYY-MM') AS month,
SUM(s.amount_sold) AS sum_amount
FROM sales s, products p
WHERE s.prod_id=p.prod_id
GROUP BY p.prod_category, TO_CHAR(s.time_id, 'YYYY-MM');
SELECT p.prod_category, SUM(s.amount_sold)
FROM sales s, products p
WHERE s.prod_id=p.prod_id
AND TO_CHAR(s.time_id, 'YYYY-MM') >= '01-jan-1998'
AND TO_CHAR(s.time_id, 'YYYY-MM') < '01-jan-1999'
GROUP BY p.prod_category;
SELECT mv.prod_category, mv.sum_amount
FROM sum_sales_pcat_monthly_mv mv
WHERE month >= '01-jan-1998' AND month < '01-jan-1999';
問合せに指定されている範囲は、年、四半期または月の整数を表します。prod_typeごとに事前集計された売上を含むマテリアライズド・ビューmv3が次のように定義されているとします。
CREATE MATERIALIZED VIEW mv3 ENABLE QUERY REWRITE AS SELECT prod_name, TO_CHAR(sales.time_id,'yyyy-mm') AS month, SUM(amount_sold) AS sum_sales FROM sales, products WHERE sales.prod_id = products.prod_id GROUP BY prod_name, TO_CHAR(sales_time_id, 'yyyy-mm');
問合せは、まず日付範囲を月単位にフォールドし、次に月を表す式をmv3の月式と一致させることでリライトされます。このリライトを、次の2つのステップ(日付範囲のフォールドおよび実際のリライト)で示します。
SELECT prod_name, SUM(amount_sold) AS sum_sales
FROM sales, products
WHERE sales.prod_id = products.prod_id AND TO_CHAR(sales.time_id, 'yyyy-mm') >=
TO_CHAR('01-jan-1998', 'yyyy-mm') AND TO_CHAR(sales.time_id, '01-jan-1999',
'yyyy-mm') < TO_CHAR(TO_DATE(''01-jan-1999'', ''dd-mon-yyyy''), ''yyyy-mm'')
GROUP BY prod_name;
SELECT prod_name, sum_sales
FROM mv3 WHERE month >=
TO_CHAR(TO_DATE('01-jan-1998', 'dd-mon-yyyy'), 'yyyy-mm')
AND month < TO_CHAR(TO_DATE('01-jan-1999', 'dd-mon-yyyy'), 'yyyy-mm');
mv3の事前集計済の売上がprod_nameと月ではなくprod_nameと年に基づいたものであれば、問合せは日付範囲を年単位にフォールドして年の式と一致させることでリライトされます。
データ・ウェアハウス・アプリケーションでは、リレーショナル・スキーマの整合性制約を識別することにより、データベースの多次元キューブが認識されます。整合性制約は、ファクト表とディメンション表間の主キーと外部キーの関係を表します。アプリケーションは、データ・ディクショナリを問い合せれば整合性制約とデータベース内のキューブを認識できます。ただし、スキーマの複雑さとセキュリティ上の理由から、データベース管理者がファクト表とディメンション表のビューを定義する環境では、これは機能しません。このような環境では、アプリケーションはキューブを正しく識別できません。ビュー間で制約の定義を可能にすると、実表の制約をビューにも適用し、制限付きの環境でもアプリケーションにキューブを認識させることができます。
ビューの制約の定義は宣言の性質を持っていますが、ビューの操作には実表に定義された整合性制約が適用され、ビューに対する制約は実表に対する制約を通じて実施できます。データの正確性と正当性のみでなく、元のベース・オブジェクトを使用したマテリアライズド・ビューのクエリー・リライトのためにも、実表の制約を定義する必要があります。
マテリアライズド・ビューのリライトでは、クエリー・リライトのために制約が広範囲に使用されます。これは、可逆式結合の判断と、マテリアライズド・ビューの結合に問合せの結合との互換性があるかどうかの判断、リライトが可能かどうかの判断に使用されます。
ビューに対する制約の有効な状態は、DISABLE NOVALIDATEのみです。ただし、ビューの制約の状態としてRELYまたはNORELYを選択すると、より洗練されたクエリー・リライトが使用可能になります。たとえば、RELY状態のビューの制約では、問合せの整合性レベルがTRUSTEDに設定されている場合に、問合せをリライトできます。表19-3に、ビューの制約が可逆式結合の判断に使用される場合を示します。
ビューの制約は、問合せの整合性レベルがENFORCEDの場合には使用できないため注意してください。このレベルでは、最大レベルのENABLE VALIDATEが施行されます。
例19-10 ビューの制約
ビューのリライト機能を理解するには、shサンプル・スキーマを次のように拡張する必要があります。
CREATE VIEW time_view AS SELECT time_id, TO_NUMBER(TO_CHAR(time_id, 'ddd')) AS day_in_year FROM times;
これで、ビューとファクト表間の外部キーと主キーの関係を(RELYモードで)設定できます。次の制約を追加すると、リライトは表19-3のようになります。たとえば、リライトはTRUSTEDモードで機能します。
ALTER VIEW time_view ADD (CONSTRAINT time_view_pk PRIMARY KEY (time_id) DISABLE NOVALIDATE); ALTER VIEW time_view MODIFY CONSTRAINT time_view_pk RELY; ALTER TABLE sales ADD (CONSTRAINT time_view_fk FOREIGN KEY (time_id) REFERENCES time_view(time_id) DISABLE NOVALIDATE); ALTER TABLE sales MODIFY CONSTRAINT time_view_fk RELY;
次のマテリアライズド・ビューの定義を考えてみます。
CREATE MATERIALIZED VIEW sales_pcat_cal_day_mv ENABLE QUERY REWRITE AS SELECT p.prod_category, t.day_in_year, SUM(s.amount_sold) AS sum_amount_sold FROM time_view t, sales s, products p WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY p.prod_category, t.day_in_year;
次の問合せも、ディメンション表productsが省略されていますが、主キーと外部キーの関係なしでリライトされます。これは、salesとproducts間の結合が可逆式結合であるためです。
SELECT t.day_in_year, SUM(s.amount_sold) AS sum_amount_sold FROM time_view t, sales s WHERE t.time_id = s.time_id GROUP BY t.day_in_year;
ただし、マテリアライズド・ビューsales_pcat_cal_day_mvがビューtime_viewでのみ定義されている場合、次の問合せはsalesとtime_viewの間の結合が明記されていないためリライトできません。これは、マテリアライズド・ビューのデルタ結合の可逆性を示せないことによります。前述のように制約が追加されていれば、この問合せもリライト可能です。
SELECT p.prod_category, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p WHERE p.prod_id = s.prod_id GROUP BY p.prod_category;
shスキーマに加えた変更を元に戻すには、次の文を発行します。
ALTER TABLE sales DROP CONSTRAINT time_view_fk; DROP VIEW time_view;
参照整合性制約定義にビューが関係する場合、つまり、ビューに外部キーまたは参照されるキーがある場合、制約に指定できるモードはDISABLE NOVALIDATEのみです。
ビューのRELY制約が許可されるのは、DISABLE NOVALIDATEモードで参照される一意または主キー制約もRELY制約である場合のみです。
参照整合性制約に対応付けるON DELETEアクションは指定できません(DELETE CASCADEなど)。ただし、ビューの制約はDISABLE NOVALIDATEモードであるため、ビューおよびその実表に対するDELETE、UPDATEおよびINSERT操作は許可されます。
集合演算子を含むマテリアライズド・ビューでクエリー・リライトを使用できます。この場合、リライトを行うために、問合せとマテリアライズド・ビューが文字どおりに一致している必要はありません。たとえば、次のマテリアライズド・ビューを考えてみます。このマテリアライズド・ビューでは、San FranciscoまたはLos Angelesの男性顧客の郵便番号を使用します。
CREATE MATERIALIZED VIEW cust_male_postal_mv ENABLE QUERY REWRITE AS SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'M' AND c.cust_city = 'San Francisco' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'M' AND c.cust_city = 'Los Angeles';
次の問合せを行うと、San FranciscoまたはLos Angelesの男性顧客の郵便番号が表示されます。
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'Los Angeles' AND c.cust_gender = 'M' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'San Francisco' AND c.cust_gender = 'M';
リライトされた問合せは次のようになります。
SELECT mv.cust_city, mv.cust_postal_code FROM cust_male_postal_mv mv;
リライトされた問合せでは、UNION ALLを削除し、マテリアライズド・ビューに置き換えています。通常、クエリー・リライトでは、既存の一般的な適性ルールを使用して、UNION ALLの下位にあるSELECT副次選択が、問合せとマテリアライズド・ビューで同等かどうかを判断する必要があります。
たとえば、San Francisco、PalmdaleまたはLos Angelesの男性顧客の郵便番号を取得する問合せの場合、前述の例と同じリライトが可能ですが、クエリー・リライトでは、次のように実表でUNION ALLを保持する必要があります。
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Palmdale' AND c.cust_gender ='M' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'Los Angeles' AND c.cust_gender = 'M' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'San Francisco' AND c.cust_gender = 'M';
リライトされた問合せは、次のようになります。
SELECT mv.cust_city, mv.cust_postal_code FROM cust_male_postal_mv mv UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'Palmdale' AND c.cust_gender = 'M';
これにより、マテリアライズド・ビューcust_male_postal_mvを使用して、UNION ALLのサブセットをリライトできるケースがクエリー・リライトで検出されます。
UNION、UNION ALLおよびINTERSECTは可換であるため、問合せまたはマテリアライズド・ビューで副次選択が出現する順序に関係なく、クエリー・リライトでリライトできます。ただし、MINUSは可換ではありません。たとえば、A MINUS Bは、B MINUS Aと同等ではありません。したがって、問合せおよびマテリアライズド・ビューにおいてMINUS演算子の副次選択の出現順序は、リライトする順序と同じである必要があります。たとえば、customer_oldという古いバージョンの顧客表があり、London在住の男性顧客に限定した現行の顧客表との相違を探すとします。つまり、古い顧客表にはなく、現行の顧客表にあるそうした顧客を探します。MINUS演算子を使用して、これを行う方法の例を次に示します。
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Los Angeles' AND c.cust_gender = 'M' MINUS SELECT c.cust_city, c.cust_postal_code FROM customers_old c WHERE c.cust_city = 'Los Angeles' AND c.cust_gender = 'M';
副次選択の順序を変更すると、異なる回答になります。これは、MINUSが可換でないことを示します。
マテリアライズド・ビューにUNION ALL演算子が1つ以上ある場合、UNION ALLマーカーを含めることができます。UNION ALLマーカーは、マテリアライズド・ビューの各行が影響を受けているUNION ALL副次選択を識別するために使用します。クエリー・リライトでは、このマーカーを使用して、マテリアライズド・ビューのどの行が特定のUNION ALL副次選択に属しているかを識別できます。これは、問合せがマテリアライズド・ビューのデータのサブセットのみを必要とする場合、または問合せの副次選択が文字どおりマテリアライズド・ビューの副次選択と一致しない場合に有効です。例として、San Franciscoの男性顧客およびLos Angelesの女性顧客の郵便番号を取得する問合せを次に示します。
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'M' and c.cust_city = 'San Francisco' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'F' and c.cust_city = 'Los Angeles';
問合せの回答は、次のマテリアライズド・ビューを使用して取得できます。
CREATE MATERIALIZED VIEW cust_postal_mv ENABLE QUERY REWRITE AS SELECT 1 AS marker, c.cust_gender, c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'Los Angeles' UNION ALL SELECT 2 AS marker, c.cust_gender, c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city = 'San Francisco';
リライトされた問合せは、次のようになります。
SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 2 AND mv.cust_gender = 'M' UNION ALL SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 1 AND mv.cust_gender = 'F';
最初の副次選択のWHERE句には、mv.marker = 2およびmv.cust_gender = 'M'があります。これらは、UNION ALLの2番目の副次選択で男性顧客を表す行のみを選択します。2番目の副次選択のWHERE句には、mv.marker = 1およびmv.cust_gender = 'F'があります。これらは、UNION ALLの最初の副次選択で女性顧客を表す行のみを選択します。クエリー・リライトでは、重複行または個別行を削除する集合演算子は使用できないことに注意してください。たとえば、UNIONでは重複が削除されるため、クエリー・リライトでは削除された行を識別できません。次に例を示します。
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Palmdale' AND c.cust_gender ='M' SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'M' and c.cust_city = 'San Francisco' UNION ALL SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_gender = 'F' and c.cust_city = 'Los Angeles';
UNION ALLマーカーを使用してリライトされた問合せは、次のようになります。
SELECT c.cust_city, c.cust_postal_code FROM customers c WHERE c.cust_city= 'Palmdale' AND c.cust_gender ='M' UNION ALL SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 2 AND mv.cust_gender = 'M' UNION ALL SELECT mv.cust_city, mv.cust_postal_code FROM cust_postal_mv mv WHERE mv.marker = 1 AND mv.cust_gender = 'F';
マーカーを使用する場合に準拠する必要があるルールを次に示します。
定数または文字列を指定し、すべてのUNION ALL副次選択と同じデータ型にする必要があります。
定数を指定し、UNION ALL副次選択ごとに別個の値にします。複数の副次選択で同じ値は再使用できません。
すべての副次選択に対して同じ順序の場所に指定する必要があります。
この項では、グルーピング・セットがある場合のクエリー・リライトの使用について、考慮すべき事柄をいくつか説明します。
GROUP BY句の拡張機能としてGROUPING SETS、CUBE、ROLLUPおよびこれらの連結が使用できます。これらの拡張機能により、必要なグルーピングを問合せのGROUP BY句で選択的に指定できます。たとえば、次の例は、グルーピング・セットを使用した一般的な問合せです。
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, customers c, products p, times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (c.cust_city, p.prod_subcategory));
GROUP BY拡張機能を持つ問合せに対するベース・グルーピングという語は、GROUP BY句に存在するすべての一意式を意味します。前述の問合せでは、グルーピング(p.prod_subcategory, t.calendar_month_desc, c.cust_city)がベース・グルーピングです。
拡張機能は、ユーザーの問合せやマテリアライズド・ビューを定義する問合せに使用できます。いずれの場合も、マテリアライズド・ビューのリライトが適用され、リライトの機能を次の使用例に分けて識別できます。
問合せに拡張GROUP BY句が含まれている場合、ベース・グルーピングが「Oracleによるクエリー・リライト条件」で説明したリライト・ルールにリストされているマテリアライズド・ビューを使用してリライトできる場合は、マテリアライズド・ビューでリライトできます。たとえば、次の問合せを考えてみます。
SELECT p.prod_subcategory, t.calendar_month_desc, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, customers c, products p, times t WHERE s.time_id=t.time_id AND s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (c.cust_city, p.prod_subcategory));
ベース・グルーピングは(p.prod_subcategory, t.calendar_month_desc, c.cust_city, p.prod_subcategory))であり、これによってOracleでは、次のようにsum_sales_pscat_month_city_mvを使用して問合せをリライトできます。
SELECT mv.prod_subcategory, mv.calendar_month_desc, mv.cust_city, SUM(mv.sum_amount_sold) AS sum_amount_sold FROM sum_sales_pscat_month_city_mv mv GROUP BY GROUPING SETS ((mv.prod_subcategory, mv.calendar_month_desc), (mv.cust_city, mv.prod_subcategory));
問合せでEXPAND_GSET_TO_UNIONヒントを使用している場合は、特殊な状況が発生します。EXPAND_GSET_TO_UNIONの使用例は、「拡張GROUP BYを持つ問合せのヒント」を参照してください。
拡張GROUP BYを持つマテリアライズド・ビューをリライトに使用するためには、さらに2つの条件を満たしている必要があります。
グルーピング識別名が含まれていること。これは、すべてのGROUP BY式のGROUPING_ID関数です。たとえば、マテリアライズド・ビューのGROUP BY句がGROUP BY CUBE(a, b)の場合は、SELECT構文のリストにGROUPING_ID(a, b)を含める必要があります。
マテリアライズド・ビューのGROUP BYの結果、重複するグルーピングが発生しないこと。たとえば、GROUP BY GROUPING SETS ((a, b), (a, b))があると、マテリアライズド・ビューは一般的なリライトの対象外になります。
拡張GROUP BYを持つマテリアライズド・ビューには、複数のグルーピングが含まれています。Oracleは、問合せを計算できる最低コストのグルーピングを検索し、それをリライトに使用します。たとえば、次のマテリアライズド・ビューを考えてみます。
CREATE MATERIALIZED VIEW sum_grouping_set_mv
ENABLE QUERY REWRITE AS
SELECT p.prod_category, p.prod_subcategory, c.cust_state_province, c.cust_city,
GROUPING_ID(p.prod_category,p.prod_subcategory,
c.cust_state_province,c.cust_city) AS gid,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY GROUPING SETS
((p.prod_category, p.prod_subcategory, c.cust_city),
(p.prod_category, p.prod_subcategory, c.cust_state_province, c.cust_city),
(p.prod_category, p.prod_subcategory));
この場合、次の問合せはリライトされます。
SELECT p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY p.prod_subcategory, c.cust_city;
この問合せは、マテリアライズド・ビューに最も近似したグルーピングでリライトされます。つまり、次のように、(prod_category, prod_subcategory, cust_city)グルーピングが使用されます。
SELECT prod_subcategory, cust_city, SUM(sum_amount_sold) AS sum_amount_sold
FROM sum_grouping_set_mv
WHERE gid = grouping identifier of (prod_category,prod_subcategory, cust_city)
GROUP BY prod_subcategory, cust_city;
マテリアライズド・ビューと問合せの両方にGROUP BY拡張機能が含まれている場合、Oracleではリライトに対してグルーピングの一致とUNION ALLリライトという2つの方法が使用されます。最初にグルーピングの一致が試されます。問合せのグルーピングがマテリアライズド・ビューのグルーピングと一致し、しかもロールアップなしで一致する場合、マテリアライズド・ビューからグルーピングが選択されます。たとえば、次の問合せを考えてみます。
SELECT p.prod_category, p.prod_subcategory, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY GROUPING SETS
((p.prod_category, p.prod_subcategory, c.cust_city),
(p.prod_category, p.prod_subcategory));
この問合せは、sum_grouping_set_mvの2つのグルーピングと一致するため、Oracleは問合せを次のようにリライトします。
SELECT prod_subcategory, cust_city, sum_amount_sold FROM sum_grouping_set_mv WHERE gid = grouping identifier of (prod_category,prod_subcategory, cust_city) OR gid = grouping identifier of (prod_category,prod_subcategory)
グルーピングの一致が失敗した場合、UNION ALLリライトと呼ばれる一般的なリライト方法が試みられます。Oracleは、最初に、拡張GROUP BY句を持つ問合せを、同等なUNION ALL問合せで表します。元の問合せのグルーピングはすべて別個のUNION ALLブランチで置き換えられます。ブランチは、単純GROUP BY句を持ちます。たとえば、次の問合せを考えてみます。
SELECT p.prod_category, p.prod_subcategory, c.cust_state_province, t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold FROM sales s, products p, customers c, times t WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id GROUP BY GROUPING SETS ((p.prod_subcategory, t.calendar_month_desc), (t.calendar_month_desc), (p.prod_category, p.prod_subcategory, c.cust_state_province), (p.prod_category, p.prod_subcategory));
これは、最初に、次の4つのブランチを持つUNION ALLで表されます。
SELECT null, p.prod_subcategory, null,
t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY p.prod_subcategory, t.calendar_month_desc
UNION ALL
SELECT null, null, null,
t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc
UNION ALL
SELECT p.prod_category, p.prod_subcategory, c.cust_state_province,
null, SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY p.prod_category, p.prod_subcategory, c.cust_state_province
UNION ALL
SELECT p.prod_category, p.prod_subcategory, null,
null, SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY p.prod_category, p.prod_subcategory;
次に、個々のブランチは、「Oracleによるクエリー・リライト条件」のルールを使用して個別にリライトされます。マテリアライズド・ビューsum_grouping_set_mvを使用して、ブランチ3(マテリアライズド・ビューのロールアップが必要)とブランチ4(マテリアライズド・ビューと正確に一致)のみがリライトできます。リライトされないブランチは、元の拡張GROUP BY形式に変換されます。この結果、問合せは次のようにリライトされます。
SELECT null, p.prod_subcategory, null,
t.calendar_month_desc, SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.prod_id = p.prod_id AND s.cust_id = c.cust_id
GROUP BY GROUPING SETS
((p.prod_subcategory, t.calendar_month_desc),
(t.calendar_month_desc),)
UNION ALL
SELECT prod_category, prod_subcategory, cust_state_province,
null, SUM(sum_amount_sold) AS sum_amount_sold
FROM sum_grouping_set_mv
WHERE gid = <grouping id of (prod_category,prod_subcategory, cust_city)>
GROUP BY p.prod_category, p.prod_subcategory, c.cust_state_province
UNION ALL
SELECT prod_category, prod_subcategory, null,
null, sum_amount_sold
FROM sum_grouping_set_mv
WHERE gid = <grouping id of (prod_category,prod_subcategory)>
拡張GROUP BYを持つ問合せは等価なUNION ALLで表され、リライトの最適化のために再帰的に実行されることに注意してください。リライトできないグルーピングはUNION ALLの最後のブランチに残り、かわりに実データがアクセスされます。
ウィンドウ関数は、累積集計、移動集計および集中集計の計算に使用されます。これらの関数は、SUM、AVG、MIN、MAX、COUNT、VARIANCE、STDDEV、FIRST_VALUEおよびLAST_VALUEの各集計とともに使用されます。ウィンドウ関数を持つ問合せは、テキストの完全一致のリライトを使用してリライトできます。これには、マテリアライズド・ビューの定義も問合せに完全に一致する必要があります。マテリアライズド・ビューにウィンドウ関数がなく、問合せ内の集計がマテリアライズド・ビューにあり、結合互換性チェックなどの他のすべての適格性チェックにパスした場合に、ウィンドウ関数を持つ問合せはリライトできます。問合せのウィンドウ関数は、マテリアライズド・ビューのウィンドウ関数と比較されます。比較には標準的な形式のフォーマットが使用されます。これによって、複雑なウィンドウ関数でもリライトを実行できます。
クエリー・リライトの際に、ウィンドウ関数を持つ問合せでロールアップが必要となる場合、その問合せは、可能な場合、集計を持つ内部問合せとウィンドウ関数を持つ外部問合せに分割されます。これにより、クエリー・リライトでは、ウィンドウ関数が適用される前に内部問合せの集計をリライトできます。唯一の例外は、問合せにウィンドウ関数とグルーピング・セットの両方がある場合です。この場合、グルーピング・セットがあるため、クエリー・リライトでは問合せを分割できず、問合せはリライトされません。
マテリアライズド・ビューの列が、問合せの式と一致する事前に計算された式を表している場合、問合せに使用される式は、マテリアライズド・ビューの単純列に置き換えることができます。マテリアライズド・ビューを使用するように問合せをリライトできれば、その方が高速になります。これは、マテリアライズド・ビューには事前計算済の計算が含まれており、式の計算を実行する必要がないためです。
式の一致は、まず式を標準的な形式に変換し、次にそれらが同等かどうかを比較することによって行われます。したがって、2つの異なる式は、互いに同等であれば通常は一致します。さらに、問合せの式全体がマテリアライズド・ビュー内の式と一致しなかった場合は、副式内の一致が検索されます。式を最大限に一致させるために、副式はトップダウンで検索されます。
年齢の範囲(1-10、11-20、21-30など)ごとの売上の合計を問い合せる問合せについて考えてみます。
CREATE MATERIALIZED VIEW sales_by_age_bracket_mv
ENABLE QUERY REWRITE AS
SELECT TO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999) AS age_bracket,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, customers c WHERE s.cust_id=c.cust_id
GROUP BY TO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999);
次の問合せは式の一致を使用してリライトされます。
SELECT TO_CHAR(((2000-c.cust_year_of_birth)/10)-0.5,999), SUM(s.amount_sold) FROM sales s, customers c WHERE s.cust_id=c.cust_id GROUP BY TO_CHAR((2000-c.cust_year_of_birth)/10-0.5,999);
この問合せは、次のような年齢の範囲の式(つまり、2000 - c.cust_year_of_birth)/10-0.5)の標準的な形式の一致に基づいてsales_by_age_bracket_mvでリライトされます。
SELECT age_bracket, sum_amount_sold FROM sales_by_age_bracket_mv;
ディテール表のあるパーティションが更新されると、マテリアライズド・ビューの特定セクションのみが失効としてマークされます。マテリアライズド・ビューには、その特定の行またはグループに対応する表のパーティションを識別できる情報が必要です。最も単純な使用例は、マテリアライズド・ビューのSELECT構文のリスト内で表のパーティション化キーを使用する場合です。これが、行を失効パーティションにマップするには最も簡単な方法であるためです。部分的に失効したマテリアライズド・ビューを使用する場合のポイントは、次のとおりです。
クエリー・リライトでENFORCEDまたはTRUSTEDモードのマテリアライズド・ビューを使用できるのは、そのマテリアライズド・ビューのうち問合せに対する回答に使用される行がFRESHであると認識される場合です。
マテリアライズド・ビュー内の最新行は、そのビューのWHERE句に選択述語を追加することで識別されます。回答がこの(制限付き)マテリアライズド・ビュー内に含まれる場合は、このマテリアライズド・ビューで問合せをリライトできます。
ファクト表salesは、次のようにtime_idの範囲に基づいてパーティション化されます。
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998
VALUES LESS THAN (TO_DATE('01-APR-1998', 'DD-MON-YYYY')),
PARTITION SALES_Q2_1998
VALUES LESS THAN (TO_DATE('01-JUL-1998', 'DD-MON-YYYY')),
PARTITION SALES_Q3_1998
VALUES LESS THAN (TO_DATE('01-OCT-1998', 'DD-MON-YYYY')),
...
次のように、time_idでグルーピングされているマテリアライズド・ビューがあるとします。
CREATE MATERIALIZED VIEW sum_sales_per_city_mv
ENABLE QUERY REWRITE AS
SELECT s.time_id, p.prod_subcategory, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
GROUP BY time_id, prod_subcategory, cust_city;
また、パーティションsales_q4_2000に割り当てられる2000年12月に関する新規データが挿入されるとします。テストの目的で、salesに対して任意のDML操作を適用し、このマテリアライズド・ビューが最新の場合に次の問合せがsales_q1_2000のデータを要求すると、これ以外のパーティションを変更できます。たとえば、次のようにします。
INSERT INTO SALES VALUES(17, 10, '01-DEC-2000', 4, 380, 123.45, 54321);
リフレッシュが完了するまで、マテリアライズド・ビューは一般に失効状態で、enforcedモードでの無制限のリライトには使用できません。ただし、表salesはパーティション化されており、すべてのパーティションが変更されたわけではないため、変更されていないパーティションはすべてOracleによって識別できます。オプティマイザは、次のように問合せを定義するマテリアライズド・ビューに選択述語を暗黙的に追加することにより、マテリアライズド・ビューの最新行(最後のリフレッシュ操作以降に行われた更新の影響を受けないデータ)を識別できます。
SELECT s.time_id, p.prod_subcategory, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
AND s.time_id < TO_DATE('01-OCT-2000','DD-MON-YYYY')
OR s.time_id >= TO_DATE('01-OCT-2001','DD-MON-YYYY'))
GROUP BY time_id, prod_subcategory, cust_city;
部分的に失効したマテリアライズド・ビューが最新であるかどうかは、論理ベースではなくパーティションごとに追跡されることに注意してください。salesファクト表のパーティション化方法は四半期ベースであるため、2000年12月に変更があると、パーティションsales_q4_2000全体が失効します。
2000年の第1四半期と第2四半期の売上を要求する次の問合せがあるとします。
SELECT s.time_id, p.prod_subcategory, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
AND s.time_id BETWEEN TO_DATE('01-JAN-2000', 'DD-MON-YYYY')
AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY')
GROUP BY time_id, prod_subcategory, cust_city;
Oracle Databaseでは、マテリアライズド・ビュー内の該当する行範囲が最新であるとわかっているため、前述の問合せをマテリアライズド・ビューでリライトできます。リライトされた問合せは、次のようになります。
SELECT time_id, prod_subcategory, cust_city, sum_amount_sold
FROM sum_sales_per_city_mv
WHERE time_id BETWEEN TO_DATE('01-JAN-2000', 'DD-MON-YYYY')
AND TO_DATE('01-JUL-2000', 'DD-MON-YYYY');
マテリアライズド・ビューのSELECT(およびGROUP BYリスト)には、パーティン化キーのかわりにパーティション・マーカー(ROWIDを持つパーティションを識別するファンクション)を使用できます。マテリアライズド・ビューを使用すると、パーティション全体を含むパーティション化キーの範囲を指定する選択述語がある問合せなど、特定のパーティション(パーティション・マーカーで識別可能)のデータのみを必要とする問合せをリライトできます。パーティション・マーカー・ファンクションDBMS_MVIEW.PMARKERの詳細は、第10章「高度なマテリアライズド・ビュー」を参照してください。
次の例は、パーティション・キー列を直接使用するのではなく、マテリアライズド・ビューでパーティション・マーカーを使用する方法を示しています。
CREATE MATERIALIZED VIEW sum_sales_per_city_2_mv
ENABLE QUERY REWRITE AS
SELECT DBMS_MVIEW.PMARKER(s.rowid) AS pmarker,
t.fiscal_quarter_desc, p.prod_subcategory, c.cust_city,
SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id
AND s.time_id = t.time_id
GROUP BY DBMS_MVIEW.PMARKER(s.rowid),
p.prod_subcategory, c.cust_city, t.fiscal_quarter_desc;
パーティションsales_q1_2000が最新であり、sales表の他のパーティションに対してDML変更が行われたことがわかっているとします。テストの目的で、salesに対して任意のDML操作を適用し、マテリアライズド・ビューが最新のときにsales_q1_2000以外のパーティションを変更できます。次に例を示します。
INSERT INTO SALES VALUES(17, 10, '01-DEC-2000', 4, 380, 123.45, 54321);
マテリアライズド・ビューsum_sales_per_city_2_mvは、これで一般に失効しているとみなされますが、Oracle Databaseでは、このマテリアライズド・ビューを使用して次の問合せをリライトできます。この問合せは、次に示すように、データをパーティションsales_q1_2000に制限し、cust_cityの特定値のみを選択します。
SELECT p.prod_subcategory, c.cust_city, SUM(s.amount_sold) AS sum_amount_sold
FROM sales s, products p, customers c, times t
WHERE s.cust_id = c.cust_id AND s.prod_id = p.prod_id AND s.time_id = t.time_id
AND c.cust_city= 'Nuernberg'
AND s.time_id >=TO_DATE('01-JAN-2000','dd-mon-yyyy')
AND s.time_id < TO_DATE('01-APR-2000','dd-mon-yyyy')
GROUP BY prod_subcategory, cust_city;
PMARKERファンクションを含む部分的に失効したマテリアライズド・ビューでのリライトは、前述の例で示すように、1つ以上のパーティションの完全なデータ内容がアクセスされ、述語条件がパーティション化されたファクト表自体にある場合にのみ実行できることに注意してください。
DBMS_MVIEW.PMARKERファンクションでは、各パーティションごとにまったく別の値が与えられます。これにより、パーティション化キー自体と比べて、潜在的なマテリアライズド・ビュー内の行数は大幅に減りますが、このキーに関する詳細な情報もすべて放棄することになります。わかっている情報は、パーティション番号、したがって、境界値の上限と下限のみです。これは、レンジ・パーティション列のカーディナリティ、つまり行数を減らすこととのトレードオフとなります。
パーティションsales_q1_2000のp_marker値を31070とすると、前述の問合せはマテリアライズド・ビューに対して次のようにリライトできます。
SELECT mv.prod_subcategory, mv.cust_city, SUM(mv.sum_amount_sold) FROM sum_sales_per_city_2_mv mv WHERE mv.pmarker = 31070 AND mv.cust_city= 'Nuernberg' GROUP BY prod_subcategory, cust_city;
したがって、問合せは失効データにアクセスせずにマテリアライズド・ビューに対してリライトできます。
クエリー・リライトがサポートされるのは、問合せにユーザー・バインド変数が含まれ、実際のバインド値がクエリー・リライトで不要な場合です。バインド変数の実際の値がクエリー・リライトで必要な場合、このクエリー・リライトはバインド値に依存しています。クエリー・リライト時にユーザー・バインド変数は使用できないため、クエリー・リライトがバインド値に依存している場合、問合せのリライトはできません。例として、次のマテリアライズド・ビューcustomer_mvを考えてみます。このマテリアライズド・ビューには、WHERE句に述語(customer_id >= 1000)があります。
CREATE MATERIALIZED VIEW customer_mv ENABLE QUERY REWRITE AS SELECT cust_id, prod_id, SUM(amount_sold) AS total_amount FROM sales WHERE cust_id >= 1000 GROUP BY cust_id, prod_id;
次の問合せを考えてみます。この問合せには、WHERE句にユーザー・バインド変数(:user_id)があります。
SELECT cust_id, prod_id, SUM(amount_sold) AS sum_amount FROM sales WHERE cust_id > :user_id GROUP BY cust_id, prod_id;
マテリアライズド・ビューcustomer_mvには、WHERE句に選択述語があるため、クエリー・リライトは、ユーザー・バインド変数user_idの実際の値に依存して包含を計算します。user_idはクエリー・リライト時に使用できず、クエリー・リライトはuser_idのバインド値に依存しているため、この問合せはリライトできません。
前述の例ではWHERE句にユーザー・バインド変数がありますが、ユーザー・バインド変数が問合せのどの部分にあっても、結果は同じです。つまり、ユーザー・バインド変数が問合せのどの箇所にあるかに関係なく、クエリー・リライトがその値に依存している場合、その問合せはリライトできません。
次の問合せを考えてみます。この問合せでは、SELECT構文のリストにユーザー・バインド変数(:user_id)があります。
SELECT cust_id + :user_id, prod_id, SUM(amount_sold) AS total_amount FROM sales WHERE cust_id >= 2000 GROUP BY cust_id, prod_id;
この場合、ユーザー・バインド変数user_idの値はクエリー・リライト時に必要ないため、前述の問合せはリライトされます。
SELECT cust_id + :user_id, prod_id, total_amount FROM customer_mv;
式がTO_DATE('12-SEP-1999','DD-Mon-YYYY')のように定数に評価される場合は、一部の式でのリライトもサポートされます。たとえば、既存のマテリアライズド・ビューが次のように定義されているとします。
CREATE MATERIALIZED VIEW sales_on_valentines_day_99_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT s.prod_id, s.cust_id, s.amount_sold
FROM times t, sales s WHERE s.time_id = t.time_id
AND t.time_id = TO_DATE('14-FEB-1999', 'DD-MON-YYYY');
したがって、次の問合せはリライトできます。
SELECT s.prod_id, s.cust_id, s.amount_sold
FROM sales s, times t WHERE s.time_id = t.time_id
AND t.time_id = TO_DATE('14-FEB-1999', 'DD-MON-YYYY');
この問合せは、次のようにリライトされます。
SELECT * FROM sales_on_valentines_day_99_mv;
TO_DATEが使用される際は常に、与えられた日付マスクがNLS_DATE_FORMATで指定された日付マスクと同じである場合にのみクエリー・リライトが発生します。
2つのSQL文が機能的に同等であることを宣言できる場合に使用可能な特殊なタイプのクエリー・リライトがあります。この機能を使用すると、内部アプリケーション情報をデータベースに配置でき、データベースがこの情報を活用することにより、問合せのパフォーマンスが向上します。これを実行するには、機能的に同等な(同じ行と列を戻す)SELECT文を2つ宣言して、一方のSELECT文がパフォーマンスに関して有利なように指定します。
この高度なリライトの機能は通常、様々な問合せパフォーマンスの問題や事項に適用できます。この機能はどのようなアプリケーションでも使用でき、複雑なユーザー問合せに対するリライトに作用して、一般的に内部アプリケーション情報を持つユーザーが特別に作成した、より単純でパフォーマンスの高い問合せで回答を得ることができます。
SQL文の変換および飛躍的なパフォーマンス向上のためのチューニングを実現する、内部アプリケーション情報を持つことができるシナリオには様々なものがあります。実行する最適化のタイプには、非常に単純なものや、問合せを大幅に再構成するような高度なものもあります。ただし、入力SQL問合せはアプリケーションで生成される場合が多く、このような問合せの形式および構造は制御できません。
この機能へのアクセス権を取得するには、SYSDBAとして接続し、リライトの同等化を宣言するデータベース管理者に実行アクセス権を明示的に付与する必要があります。詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
この項では、このようなタイプの高度なリライト機能を理解するために、一部の例で多次元データを使用しています。リソース使用率を最適化するため、アプリケーションでは、複雑なSQL、カスタムCコードまたはテーブル・ファンクションを使用して、データベースからデータを取得する場合があります。ただし、エンド・ユーザーに関するかぎり、こうした複雑な操作を行うことはほとんどありません。ユーザーにとっては、SELECT ... GROUP BYなどの一般的な問合せで回答を取得するのが理想的です。
次の例では、指定の代替問合せを使用して特定のユーザー問合せを実行する必要があることを、Oracleに対して宣言します。Oracleはこの関係を認識し、ユーザーから問合せが要求されるたびに、代替問合せを使用して透過的にリライトします。したがって、ユーザーは複雑な集計計算のSQLを学習したり記述したりする必要はありません。
例19-11 同等化を使用したリライト
ここにsales_factおよびgeog_dimという2つの実表があります。次の文を発行すると、ロールアップを使用して市、州および地域ごとの合計売上を計算できます。
SELECT g.region, g.state, g.city, GROUPING_ID(g.city, g.state, g.region), SUM(sales) FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY ROLLUP(g.region, g.state, g.city);
アプリケーションでは、迅速に結果を戻せるように、この問合せがマテリアライズされることもあります。ただし、生成されるマテリアライズド・ビューによって、非常に多くのディスク領域が占有されてしまいます。ただし、市から州、州から地域にロールアップするディメンションがある場合、次のようにDECODE文を使用して3つのグルーピング列を1つの列に簡単に圧縮できます。これは、埋込み合計と呼ばれます。
DECODE (gid, 0, city, 1, state, 3, region, 7, "grand_total")
この場合、最低レベルの階層を使用して、情報全体を表します。たとえば、BostonがBoston, MA, New England Regionを表し、CAがCA, Western Regionを表す場合などです。アプリケーションでは、この埋込み合計の結果をembedded_total_salesなどの表に格納します。
ただし、結果を取得する際に、すべてのデータ列(city、state、region)が必要な場合もあります。アプリケーションでは、結果を効率的かつ迅速に戻すため、次のようにカスタム・テーブル・ファンクション(et_function)を使用して、表embedded_total_salesから拡張形式でデータを取り出す場合があります。
SELECT * FROM TABLE (et_function);
つまりこの機能によって、アプリケーションでは、前述のユーザー問合せと代替問合せの同等化を次のように宣言できるようになります。
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
'EMBEDDED_TOTAL',
'SELECT g.region, g.state, g.city,
GROUPING_ID(g.city, g.state, g.region), SUM(sales)
FROM sales_fact f, geog_dim g
WHERE f.geog_key = g.geog_key
GROUP BY ROLLUP(g.region, g.state, g.city)',
'SELECT * FROM TABLE(et_function)');
このDECLARE_REWRITE_EQUIVALENCEの呼出しにより、EMBEDDED_TOTALという同等化の宣言が作成されます。この宣言では、指定されたSOURCE_STMTおよびDESTINATION_STMTが機能的に同等であり、パフォーマンスに関してはDESTINATION_STMTが優先されることを示しています。いったん、このような宣言をDBAが作成すれば、ユーザーは内部で実行されている領域の最適化に関する知識を持つ必要がありません。
またこの機能により、アプリケーションでは、SQL問合せの特殊な部分的なマテリアライズを実行できます。たとえば、例19-12で示す3つのリレーションのUNION ALLを使用して、ロールアップを実行することもできます。
例19-12 同等化を使用したリライト(UNION ALL)
CREATE MATERIALIZED VIEW T1 AS SELECT g.region, g.state, g.city, 0 AS gid, SUM(sales) AS sales FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY g.region, g.state, g.city; CREATE MATERIALIZED VIEW T2 AS SELECT t.region, t.state, SUM(t.sales) AS sales FROM T1 GROUP BY t.region, t.state; CREATE VIEW T3 AS SELECT t.region, SUM(t.sales) AS sales FROM T2 GROUP BY t.region;
ROLLUP(region, state, city)問合せは、次のものと同等になります。
SELECT * FROM T1 UNION ALL SELECT region, state, NULL, 1 AS gid, sales FROM T2 UNION ALL SELECT region, NULL, NULL, 3 AS gid, sales FROM T3 UNION ALL SELECT NULL, NULL, NULL, 7 AS gid, SUM(sales) FROM T3;
この同等化を指定することにより、Oracle Databaseは問合せの効率的な2番目の形式を使用して、ユーザーが問い合せたROLLUP問合せを計算できます。
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'CUSTOM_ROLLUP', 'SELECT g.region, g.state, g.city, GROUPING_ID(g.city, g.state, g.region), SUM(sales) FROM sales_fact f, geog_dim g WHERE f.geog_key = g.geog_key GROUP BY ROLLUP(g.region, g.state, g.city ', ' SELECT * FROM T1 UNION ALL SELECT region, state, NULL, 1 as gid, sales FROM T2 UNION ALL SELECT region, NULL, NULL, 3 as gid, sales FROM T3 UNION ALL SELECT NULL, NULL, NULL, 7 as gid, SUM(sales) FROM T3');
また、この機能を使用すると、概念的には単純でもSQLで表現すると非常に複雑になる特殊な集計計算が可能になります。この場合アプリケーションは、指定のカスタム集計関数を使用し、内部的に複雑なSQLで計算を行うよう、ユーザーへ通知します。
例19-13 同等化を使用したリライト(カスタム集計を使用)
アプリケーション・ユーザーが市、州、地域ごとの売上および特定の季節の売上情報を調べるとします。たとえば、New Englandのユーザーが、冬季の各月におけるNew Englandの各市の売上情報を必要としているとします。アプリケーションでは、前述の集計を計算する特殊な集計Seasonal_Aggが提供されます。この場合、従来のサマリー問合せを行いますが、SUM(sales)ではなくSeasonal_Agg(sales, region)を使用します。
SELECT g.region, t.calendar_month_name, Seasonal_Agg(f.sales, g.region) AS sales FROM sales_fact f, geog_dim g, times t WHERE f.geog_key = g.geog_key AND f.time_id = t.time_id GROUP BY g.region, t.calendar_month_name;
アプリケーションでは、ユーザーが追加の計算を行うSQLを記述するよう指示されることはなく、この機能により自動的に計算が行われます。この例の場合、Seasonal_Aggはスプレッドシート機能を使用して計算されます(第23章「モデリングのSQL」を参照)。Seasonal_Aggはユーザー定義の集計ですが、問合せの回答に行を追加することが必要となります。このような処理は、単純なPL/SQLファンクションでは容易には行えません。
DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
'CUSTOM_SEASONAL_AGG',
SELECT g.region, t.calendar_month_name, Seasonal_Agg(sales, region) AS sales
FROM sales_fact f, geog_dim g, times t
WHERE f.geog_key = g.geog_key AND f.time_id = t.time_id
GROUP BY g.region, t.calendar_month_name',
'SELECT g,region, t.calendar_month_name, SUM(sales) AS sales
FROM sales_fact f, geog_dim g
WHERE f.geog_key = g.geog_key AND t.time_id = f.time_id
GROUP BY g.region, g.state, g.city, t.calendar_month_name
DIMENSION BY g.region, t.calendar_month_name
(sales ['New England', 'Winter'] = AVG(sales) OVER calendar_month_name IN
('Dec', 'Jan', 'Feb', 'Mar'),
sales ['Western', 'Summer' ] = AVG(sales) OVER calendar_month_name IN
('May', 'Jun', 'July', 'Aug'), .);
結果キャッシュ・マテリアライズド・ビュー(RCMV)という特殊なマテリアライズド・ビューを使用すると、クエリー・リライトの実行時に結果キャッシュを使用できます。結果キャッシュ・マテリアライズド・ビューでは、より少ない領域ですばやくアクセスできるという結果キャッシュの主なメリットを得られる上に、クエリー・リライトを実行できないという通常のデメリットから解放されます。
このタイプのマテリアライズド・ビューの使用例を次に示します。
例19-14 結果キャッシュ・マテリアライズド・ビュー
まず、必要な権限を付与します。
CONNECT / AS SYSDBA GRANT CREATE MATERIALIZED VIEW TO sh; GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO sh;
次に、結果キャッシュ・マテリアライズド・ビューを作成します。
CONNECT sh/sh
begin
sys.DBMS_ADVANCED_REWRITE.Declare_Rewrite_Equivalence
(
Name => 'RCMV_SALES',
Source_Stmt =>
'select channel_id, prod_id, sum(amount_sold), count(amount_sold)
from sales
group by prod_id, channel_id',
Destination_Stmt =>
'select * from
(select /*+ RESULT_CACHE(name=RCMV_SALES) */
channel_id, prod_id, sum(amount_sold), count(amount_sold)
from sales
group by prod_id, channel_id)',
Validate => FALSE,
Rewrite_Mode => 'GENERAL'
);
end;
/
ALTER SESSION SET query_rewrite_integrity = stale_tolerated;
EXPLAIN PLANを見ながら、異なるすべての問合せがRCMV_SALESにリライトされることを確認します。
EXPLAIN PLAN FOR SELECT channel_id, SUM(amount_sold) FROM sales GROUP BY channel_id; @?/rdbms/admin/utlxpls PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3903632134 -------------------------------------------------------------------------------- |Id | Operation | Name |Rows|Bytes|Cost(%CPU)| Time |Pstart|Pstop| --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 64| 1340 (68)|00:00:17| | | | 1 | HASH GROUP BY | | 4 | 64| 1340 (68)|00:00:17| | | | 2 | VIEW | | 204| 3264| 1340 (68)|00:00:17| | | | 3 | RESULT CACHE |3gps5zr86gyb53y36js9zuay2s| | | | | | | | 4 | HASH GROUP BY | | 204| 2448| 1340 (68)|00:00:17| | | | 5 | PARTITION RANGE ALL| |918K| 10M| 655 (33)|00:00:08| 1 | 28 | | 6 | TABLE ACCESS FULL | SALES |918K| 10M| 655 (33)|00:00:08| 1 | 28 | --------------------------------------------------------------------------------- Result Cache Information (identified by operation id): ------------------------------------------------------ 3 - column-count=4; dependencies=(SH.SALES); name="RCMV_SALES" 18 rows selected.
キャッシュされた結果を作成する問合せを実行します。
SELECT channel_id, SUM(amount_sold)
FROM sales
GROUP BY channel_id;
CHANNEL_ID SUM(AMOUNT_SOLD)
---------- ----------------
2 26346342.3
4 13706802
3 57875260.6
9 277426.26
結果キャッシュ内でマテリアライズド・ビューがマテリアライズされていることを確認します。
CONNECT / AS SYSDBA SELECT name, scan_count hits, block_count blocks, depend_count dependencies FROM V$RESULT_CACHE_OBJECTS WHERE name = 'RCMV_SALES'; NAME HITS BLOCKS DEPENDENCIES ---------- ---- ------ ------------ RCMV_SALES 0 5 1
最後に、RCMV問合せの同等化を削除します。
begin
sys.DBMS_ADVANCED_REWRITE.Drop_Rewrite_equivalence('RCMV_SALES');
end;
/
結果キャッシュの詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
クエリー・リライトは透過的に行われるので、問合せがリライトされたかどうかを確認するには、特別なステップを実行する必要があります。問合せが高速に実行された場合、リライトが発生したと考えられますが、これは確認にはなりません。そのため、EXPLAIN PLAN文またはDBMS_MVIEW.EXPLAIN_REWRITEプロシージャを使用して、クエリー・リライトが発生したことを確認します。
EXPLAIN PLAN機能の使用方法については、『Oracle Database SQL言語リファレンス』を参照してください。クエリー・リライトの場合、チェックする必要があるのは、操作(OPERATION)にMAT_VIEW REWRITE ACCESSが示されていることのみです。示されている場合、クエリー・リライトが発生したということになります。次の例では、マテリアライズド・ビュー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;
次のSQL文でEXPLAIN PLANが使用された場合、結果はデフォルトのPLAN_TABLE表に格納されます。ただし、PLAN_TABLEはutlxplan.sqlスクリプトを使用して最初に作成する必要があります。EXPLAIN PLANでは問合せが実際には実行されないことに注意してください。
EXPLAIN PLAN FOR 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;
PLAN_TABLEから得られる情報のうちクエリー・リライトに関して必要なものは、この問合せの実行方法を特定するための操作OBJECT_NAMEのみです。したがって次のように、出力には操作MAT_VIEW REWRITE ACCESSが表示されます。
SELECT OPERATION, OBJECT_NAME FROM PLAN_TABLE; OPERATION OBJECT_NAME -------------------- ----------------------- SELECT STATEMENT MAT_VIEW REWRITE ACCESS CALENDAR_MONTH_SALES_MV
問合せがリライトされない場合、その原因を容易には特定できないことがあります。クエリー・リライトの対象になるかどうかを制御するルールはきわめて複雑で、制約、ディメンション、クエリー・リライトの整合性モード、マテリアライズド・ビューが最新かどうか、問合せ自体のタイプなど、様々な要因が関係します。また、クエリー・リライトで特定のマテリアライズド・ビューが選択された理由も知る必要があります。そのため、問合せをリライトできる場合にそれを知らせ、リライトできない場合はその理由を知らせるDBMS_MVIEW.EXPLAIN_REWRITEプロシージャが用意されています。DBMS_MVIEW.EXPLAIN_REWRITEの結果を使用すると、問合せをできるかぎりリライトさせるために必要となる適切な措置を講じることができます。
EXPLAIN_REWRITE文で指定した問合せは、実際には実行されないことに注意してください。
DBMS_MVIEW.EXPLAIN_REWRITEからの出力を取得するには、2つの方法があります。1つは表を使用する方法、もう1つはVARRAYを作成する方法です。出力表を使用するための基本構文は、次のとおりです。
DBMS_MVIEW.EXPLAIN_REWRITE (
query VARCHAR2,
mv VARCHAR2(30),
statement_id VARCHAR2(30));
スクリプトutlxrw.sqlを実行すると、出力表REWRITE_TABLEを作成できます。
queryパラメータは、SQL問合せを表すテキスト文字列です。mvパラメータには、schema.mvという形式の完全修飾されたマテリアライズド・ビュー名を指定します。これは、オプション・パラメータです。このパラメータを指定しない場合、EXPLAIN_REWRITEは、指定されたクエリー・リライトに使用できるすべてのマテリアライズド・ビューについて関連メッセージを戻します。schemaを省略してmvのみを指定すると、EXPLAIN_REWRITEでは現在のスキーマにあるマテリアライズド・ビューが検索されます。
EXPLAIN_REWRITEの出力を表ではなくVARRAYに送る場合は、このプロシージャを次のようにコールする必要があります。
DBMS_MVIEW.EXPLAIN_REWRITE (
query [VARCHAR2 | CLOB],
mv VARCHAR2(30),
output_array SYS.RewriteArrayType);
問合せの長さが256文字未満であれば、EXPLAIN_REWRITEは、SQL*PlusのEXECUTEコマンドを使用して簡単に呼び出すことができます。256文字以上の場合は、PL/SQLのBEGIN... ENDブロックを/rdbms/demo/smxrw*の例のように使用する方法をお薦めします。
EXPLAIN_REWRITEの出力は、表REWRITE_TABLEに送ることができます。この出力表を作成するには、スクリプトutlxrw.sqlを実行します。このスクリプトはadminディレクトリにあります。REWRITE_TABLEの形式は、次のとおりです。
CREATE TABLE REWRITE_TABLE( statement_id VARCHAR2(30), -- id for the query mv_owner VARCHAR2(30), -- owner of the MV mv_name VARCHAR2(30), -- name of the MV sequence INTEGER, -- sequence no of the msg query VARCHAR2(2000), -- user query query_block_no INTEGER, -- block no of the current subquery rewritten_txt VARCHAR2(2000), -- rewritten query message VARCHAR2(512), -- EXPLAIN_REWRITE msg pass VARCHAR2(3), -- rewrite pass no mv_in_msg VARCHAR2(30), -- MV in current message measure_in_msg VARCHAR2(30), -- Measure in current message join_back_tbl VARCHAR2(30), -- Join back table in message join_back_col VARCHAR2(30), -- Join back column in message original_cost INTEGER, -- Cost of original query rewritten_cost INTEGER, -- Cost of rewritten query flags INTEGER, -- associated flags reserved1 INTEGER, -- currently not used reerved2 VARCHAR2(10)) -- currently not used;
例19-15 REWRITE_TABLEを使用したEXPLAIN_REWRITE
PL/SQLコールの例を次に示します。
EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE -
('SELECT p.prod_name, SUM(amount_sold) ' || -
'FROM sales s, products p ' || -
'WHERE s.prod_id = p.prod_id ' || -
' AND prod_name > ''B%'' ' || -
' AND prod_name < ''C%'' ' || -
'GROUP BY prod_name', -
'TestXRW.PRODUCT_SALES_MV', -
'SH');
SELECT message FROM rewrite_table ORDER BY sequence;
MESSAGE
--------------------------------------------------------------------------------
QSM-01033: query rewritten with materialized view, PRODUCT_SALES_MV
1 row selected.
デモ・ファイルxrwutl.sqlには、EXPLAIN_REWRITEのより詳細な出力を表示するためのプロシージャが用意されています。詳細は、「EXPLAIN_REWRITEの出力」を参照してください。
次に、いくつかのマテリアライズド・ビューがある中で、結果的にsales_mvが最適なマテリアライズド・ビューとして選択された理由を、1つの具体例を基にして詳しく説明します。
DECLARE
qrytext VARCHAR2(500) :='SELECT cust_first_name, cust_last_name,
SUM(amount_sold) AS dollar_sales FROM sales s, customers c WHERE s.cust_id=
c.cust_id GROUP BY cust_first_name, cust_last_name';
idno VARCHAR2(30) :='ID1';
BEGIN
DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, '', idno);
END;
/
SELECT message FROM rewrite_table ORDER BY sequence;
SQL> MESSAGE -------------------------------------------------------------------------------- QSM-01082: Joining materialized view, CAL_MONTH_SALES_MV, with table, SALES, not possible QSM-01022: a more optimal materialized view than PRODUCT_SALES_MV was used to rewrite QSM-01022: a more optimal materialized view than FWEEK_PSCAT_SALES_MV was used to rewrite QSM-01033: query rewritten with materialized view, SALES_MV
EXPLAIN_REWRITEの出力はPL/SQLのVARRAYに保存できます。この配列の要素はRewriteMessage型で、SYSスキーマで次のように事前定義されています。
TYPE RewriteMessage IS OBJECT( mv_owner VARCHAR2(30), -- MV's schema mv_name VARCHAR2(30), -- Name of the MV sequence NUMBER(3), -- sequence no of the msg query_text VARCHAR2(2000), -- User query query_block_no NUMBER(3), -- block no of the current subquery rewritten_text VARCHAR2(2000), -- rewritten query text message VARCHAR2(512), -- EXPLAIN_REWRITE error msg pass VARCHAR2(3), -- Query rewrite pass no mv_in_msg VARCHAR2(30), -- MV in current message measure_in_msg VARCHAR2(30), -- Measure in current message join_back_tbl VARCHAR2(30), -- Join back table in current msg join_back_col VARCHAR2(30), -- Join back column in current msg original_cost NUMBER(10), -- Cost of original query rewritten_cost NUMBER(10), -- Cost rewritten query flags NUMBER, -- Associated flags reserved1 NUMBER, -- For future use reserved2 VARCHAR2(10) -- For future use );
配列型RewriteArrayTypeは、RewriteMessageオブジェクトのVARRAYで、SYSスキーマに次のように事前定義されています。
TYPE RewriteArrayType AS VARRAY(256) OF RewriteMessage;
この配列型を使用すると、配列変数を宣言して、それをEXPLAIN_REWRITE文の中に指定できます。
各RewriteMessageレコードには、リライト処理に関するメッセージがあります。
パラメータはREWRITE_TABLEと同じですが、出力としてVARRAYを使用する場合、statement_idは使用しません。
mv_ownerフィールドでは、メッセージに関連するマテリアライズド・ビューの所有者が定義されます。
mv_nameフィールドでは、メッセージに関連するマテリアライズド・ビューの名前が定義されます。
sequenceフィールドでは、メッセージに必要な順序が定義されます。
query_textフィールドには、分析対象となる問合せテキストの最初の2000文字が含まれます。
messageフィールドには、queryのリライト処理に関連するメッセージのテキストが含まれます。
flags、reserved1およびreserved2フィールドは、将来のために予約されています。
例19-16 VARRAYを使用したEXPLAIN_REWRITE
次のマテリアライズド・ビューを考えてみます。
CREATE MATERIALIZED VIEW avg_sales_city_state_mv ENABLE QUERY REWRITE AS SELECT c.cust_city, c.cust_state_province, AVG(s.amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_city, c.cust_state_province;
このマテリアライズド・ビューを次の問合せで使用してみます。
SELECT c.cust_state_province, AVG(s.amount_sold) FROM sales s, customers c WHERE s.cust_id = c.cust_id GROUP BY c.cust_state_province;
ただし、このマテリアライズド・ビューでは、問合せはリライトされません。これでは、リライトに必要な情報がすべてマテリアライズド・ビューにあるように思われるため、不慣れなユーザーは混乱しがちです。特定のマテリアライズド・ビューからAVGを計算できないことは、DBMS_MVIEW.EXPLAIN_REWRITEから判断できます。問題は、ここではROLLUPが必要であり、AVGではCOUNTまたはSUMのROLLUPが必要であることです。
前述の問合せのPL/SQLブロックの例は、出力としてVARRAYを使用すると次のようになります。
SET SERVEROUTPUT ON
DECLARE
Rewrite_Array SYS.RewriteArrayType := SYS.RewriteArrayType();
querytxt VARCHAR2(1500) := 'SELECT c.cust_state_province,
AVG(s.amount_sold)
FROM sales s, customers c WHERE s.cust_id = c.cust_id
GROUP BY c.cust_state_province';
i NUMBER;
BEGIN
DBMS_MVIEW.EXPLAIN_REWRITE(querytxt, 'AVG_SALES_CITY_STATE_MV',
Rewrite_Array);
FOR i IN 1..Rewrite_Array.count
LOOP
DBMS_OUTPUT.PUT_LINE(Rewrite_Array(i).message);
END LOOP;
END;
/
このEXPLAIN_REWRITE文の出力を次に示します。
QSM-01065: materialized view, AVG_SALES_CITY_STATE_MV, cannot compute measure, AVG, in the query QSM-01101: rollup(s) took place on mv, AVG_SALES_CITY_STATE_MV QSM-01053: NORELY referential integrity constraint on table, CUSTOMERS, in TRUSTED/STALE TOLERATED integrity mode PL/SQL procedure successfully completed.
EXPLAIN_REWRITEの出力には、original_costおよびrewritten_costという2つの列があり、クエリー・コストの見積りに役立ちます。original_cost列は、クエリー・リライトを使用禁止にした場合のクエリー・コストに関するオプティマイザの見積りを提供し、rewritten_cost列は、マテリアライズド・ビューを使用して問合せをリライトした場合の見積りを提供します。これらのコスト値は、特定の問合せがリライトから受けるメリットを調べる場合に使用できます。
このリリースでは、サイズの大きな問合せでも処理できるよう、EXPLAIN_REWRITEプロシージャが強化されました。入力問合せテキストをVARCHARデータ型のかわりにCLOBデータ型で定義できるようになりました。これにより、EXPLAIN_REWRITEで最大4GBの問合せが許容されます。
次に、CLOBを使用して表に出力する場合のEXPLAIN_REWRITEの構文を示します。
DBMS_MVIEW.EXPLAIN_REWRITE( query IN CLOB, mv IN VARCHAR2, statement_id IN VARCHAR2);
2番目の引数mvおよび3番目の引数statement_idには、NULLを指定できます。同様に、CLOBを使用してVARRAYに出力する場合のEXPLAIN_REWRITEの構文を示します。
DBMS_MVIEW.EXPLAIN_REWRITE( query IN CLOB, mv IN VARCHAR2, msg_array IN OUT SYS.RewriteArrayType);
表に出力する場合と同様、2番目の引数mvにはNULLを指定できます。CLOBでの長い問合せテキストは、DBMS_LOBパッケージのプロシージャを使用して生成できます。
複数のマテリアライズド・ビューを使用したEXPLAIN_REWRITEの構文は、単一のマテリアライズド・ビューを使用した場合の構文と同じです。ただし、複数のマテリアライズド・ビューはカンマで区切った文字列として指定します。たとえば、マテリアライズド・ビューの特定のセットmv1、mv2およびmv3を問合せquery_txtのリライトに使用できるかどうかを判断し、使用できない場合にその原因を調べるには、次のようにEXPLAIN_REWRITEを使用します。
DBMS_MVIEW.EXPLAIN_REWRITE(query_txt, 'mv1, mv2, mv3')
マテリアライズド・ビューの特定のセットを使用して問合せquery_txtがリライトされた場合、次のメッセージが表示されます。
QSM-01127: query rewritten with materialized view(s), mv1, mv2, and mv3.
1つ以上のマテリアライズド・ビューの特定のセットを使用したクエリー・リライトが失敗した場合、リライトに使用されなかった各マテリアライズド・ビューについてEXPLAIN_REWRITEによって失敗の原因が出力されます。
/rdbms/demo/smxrw.sqlには、EXPLAIN_REWRITEの使用方法を示した具体例がいくつか用意されています。またデモのxrw領域には、SYS.XRWというユーティリティがあり、EXPLAIN_REWRITEプロシージャの出力内容の選択に使用できます。EXPLAIN_REWRITEにより問合せの評価が行われると、その出力には、リライトされた問合せテキスト、問合せのブロック番号、リライトされた問合せのコストなどの情報が反映されます。ユーティリティSYS.XRWでは、ユーザーにより指定されたフィールドが一定の書式に基づいて出力されるため、内容の判読が容易です。構文は次のとおりです。
SYS.XRW(list_of_mvs, list_of_commands, query_text),
ただし、list_of_mvsは、クエリー・リライトで使用されると予想されるマテリアライズド・ビューを表します。マテリアライズド・ビューが複数存在する場合は、それぞれをカンマで区切る必要があります。list_of_commandsは、次のいずれかのフィールドを表します。
QUERY_TXT: User query text
REWRITTEN_TXT: Rewritten query text
QUERY_BLOCK_NO: Query block number to identify each query blocks in
case the query has subqueries or inline views
PASS: Pass indicates whether a given message was generated
before or after the view merging process of query rewrite.
COSTS: Costs indicates the estimated execution cost of the
original query and the rewritten query
次の例は、このユーティリティの使用方法を示したものです。
DROP MATERIALIZED VIEW month_sales_mv;
CREATE MATERIALIZED VIEW month_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.calendar_month_number, SUM(s.amount_sold) AS sum_dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_number;
SET SERVEROUTPUT ON
DECLARE
querytxt VARCHAR2(1500) := 'SELECT t.calendar_month_number,
SUM(s.amount_sold) AS sum_dollars FROM sales s, times t
WHERE s.time_id = t.time_id GROUP BY t.calendar_month_number';
BEGIN
SYS.XRW('MONTH_SALES_MV', 'COSTS, PASS, REWRITTEN_TXT, QUERY_BLOCK_NO', querytxt);
END;
/
SYS.XRWの出力は次のようになります。SYS.XRWの出力には、リライト前およびリライト後の問合せのコスト、リライトされた問合せテキスト、問合せのブロック番号、およびビューのマージ処理の前後でメッセージが生成されたかどうかが反映されていることがわかります。
============================================================================ >> MESSAGE : QSM-01151: query was rewritten >> RW QUERY : SELECT MONTH_SALES_MV.CALENDAR_MONTH_NUMBER CALENDAR_MONTH_NUMBER, MONTH_SALES_MV.SUM_DOLLARS SUM_DOLLARS FROM SH.MONTH_SALES_MV MONTH_SALES_MV >> ORIG COST: 19.952763130792 RW COST: 1.80687108 ============================================================================ >> ------------------------- ANALYSIS OF QUERY REWRITE ------------------------- >> >> QRY BLK #: 0 >> MESSAGE : QSM-01209: query rewritten with materialized view, MONTH_SALES_MV, using text match algorithm >> RW QUERY : SELECT MONTH_SALES_MV.CALENDAR_MONTH_NUMBER CALENDAR_MONTH_NUMBER, MONTH_SALES_MV.SUM_DOLLARS SUM_DOLLARS FROM SH.MONTH_SALES_MV MONTH_SALES_MV >> ORIG COST: 19.952763130792 RW COST: 1.80687108 >> MESSAGE OUTPUT BEFORE VIEW MERGING... ============================ END OF MESSAGES =============================== PL/SQL procedure successfully completed.
この項では、クエリー・リライトのメリットを最大限引き出す上で役立つ、設計上の考慮事項について説明します。これらは、クエリー・リライトを使用するために必須ではありません。また、これらのガイドラインに従っても、リライトが保証されるわけではありません。考慮する必要がある一般的なルールは次のとおりです。
マテリアライズド・ビューで参照されるすべての内部結合に、外部キー列に対する追加のNOT NULL制約を使用した参照整合性(外部キー/主キー制約)があることを確認します。制約は通常、大量のオーバーヘッドを伴うため、それらをNO VALIDATEおよびRELYにして、パラメータQUERY_REWRITE_INTEGRITYをSTALE_TOLERATEDまたはTRUSTEDに設定することも可能です。ただし、QUERY_REWRITE_INTEGRITYをENFORCEDに設定した場合、リライト機能の効果を最大限に高めるには、すべての制約をENABLED状態、ENFORCED状態およびVALIDATED状態に設定する必要があります。
予期しない結果となる可能性があるため、ON DELETE句は使用しないようにする必要があります。
正規化されたディメンション表または非正規化ディメンション表の階層関係および機能依存性は、ディメンションのHIERARCHY句およびDETERMINES句を使用して表現できます。ディメンションは、制約では表現できない表内関係を表現できます。ディメンションで宣言された関係をクエリー・リライトで利用するには、パラメータQUERY_REWRITE_INTEGRITYをTRUSTEDまたはSTALE_TOLERATEDに設定する必要があります。
制約を回避するもう1つの方法は、マテリアライズド・ビューに外部結合を使用する方法です。クエリー・リライトは、BのROWIDまたは列B.bがマテリアライズド・ビュー内で使用可能な場合、(A.a=B.b)などの問合せ内の内部結合を、マテリアライズド・ビュー(A.a = B.b(+))内の外部結合から導出できます。外部結合を使用したリライトのほとんどは、結合のみを使用したマテリアライズド・ビューによってサポートされます。それを活用するためには、外部結合を使用したマテリアライズド・ビューは、外部結合の内部表のROWIDまたは主キーを格納する必要があります。たとえば、マテリアライズド・ビューjoin_sales_time_product_mv_ojには、外部結合の内部表の主キーprod_idおよびtime_idが格納されます。
極端に複雑で実行に時間がかかる問合せについて、その処理時間を短縮する必要がある場合は、問合せと同じテキストを使用したマテリアライズド・ビューを作成します。マテリアライズド・ビューには問合せの結果が格納されるため、複合結合の実行に要する時間や全データの中から必要なデータを検索するのに要する時間を節約できます。
クエリー・リライトで最大の効果を得ることができるよう、問合せの集計を計算するために必要なすべての集計がマテリアライズド・ビューに存在していることを確認します。集計の条件は、増分リフレッシュの条件とよく似ています。たとえば、AVG(x)が問合せ内にある場合、COUNT(x)およびAVG(x)、またはSUM(x)およびCOUNT(x)をマテリアライズド・ビューに格納する必要があります。高速リフレッシュの要件については、「高速リフレッシュにおける一般的な制限」を参照してください。
階層のより低いレベルでデータを集計すると、より高いレベルでデータを集計するより効率的です。より低いレベルは、より多くのクエリー・リライトに使用されるためです。ただし、それによって、より多くの領域が必要となることに注意してください。たとえば、州でグルーピングするのではなく、市でグルーピングした場合などです(領域の制約で禁止されていない場合)。
オーバーラップした、または階層的に関係付けられたGROUP BY列を使用した複数のマテリアライズド・ビューを作成するかわりに、それらすべてのGROUP BY列を使用した単一のマテリアライズド・ビューを作成します。たとえば、市でグルーピングされたマテリアライズド・ビュー、および月でグルーピングされた別のマテリアライズド・ビューを使用するかわりに、市および月でグルーピングされた1つのマテリアライズド・ビューを使用します。
ディメンション内のレベルに対応する列にGROUP BYを使用し、機能的に依存している列には使用しません。これは、クエリー・リライトは、ディメンション内のDETERMINES句をベースにして、機能依存性を自動的に使用できるためです。たとえば、prod_nameでグルーピングするかわりに、prod_idでグルーピングします(属性prod_idによりprod_nameが決定されることを示すディメンションがあれば、prod_nameに関するクエリー・リライトを使用可能にできます)。
複数の問合せに共通の副次選択がある場合、SELECT列の1つとして共通の副次選択を使用したマテリアライズド・ビューを作成すると効果的です。その場合、共通の副次選択の事前計算によって、複数の問合せにおけるパフォーマンスが向上します。
月、四半期、年などのフォールドされた日付単位ごとにデータを集計するマテリアライズド・ビューを作成する場合、年コンポーネントは、常に接頭辞として使用し、接尾辞としては使用しません。たとえば、TO_CHAR(date_col, 'yyyy-q')は、日付を四半期にフォールドし、年の順序にそろえますが、TO_CHAR(date_col, 'q-yyyy')は、日付を四半期にフォールドし、四半期の順序にそろえます。前者は順序を維持しますが、後者は順序を維持しません。このため、年の接頭辞なしで作成されたすべてのマテリアライズド・ビューは、デート・フォールディングのリライトには使用できません。
マテリアライズド・ビューを使用した最適化は、コストベースで実行されます。オプティマイザがコストベースの選択をするには、マテリアライズド・ビューと問合せの表の両方の統計情報が必要です。そのため、マテリアライズド・ビューにはDBMS_STATSパッケージを使用して収集された情報が必要です。
この項の内容は、次のとおりです。
SQL文のSELECTブロックにヒントを含めると、クエリー・リライトの発生を制御できます。問合せにNOREWRITEヒントを使用すると、オプティマイザが問合せをリライトすることを回避できます。
問合せに引数を持たないREWRITEヒントを使用すると、オプティマイザでは、コストにかかわらずマテリアライズド・ビュー(ある場合)によるリライトが強制的に実行されます。引数を持つREWRITE(mv1,mv2,...)ヒントを使用すると、指定した名前のリストから最適なマテリアライズド・ビューを選択してリライトを強制できます。
リライトを防止するには、次の文を使用できます。
SELECT /*+ NOREWRITE */ p.prod_subcategory, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_subcategory;
sum_sales_pscat_week_mvを使用してリライトを強制するには(リライトが可能な場合)、次の文を使用します。
SELECT /*+ REWRITE (sum_sales_pscat_week_mv) */
p.prod_subcategory, SUM(s.amount_sold)
FROM sales s, products p WHERE s.prod_id=p.prod_id
GROUP BY p.prod_subcategory;
リライト・ヒントの有効範囲は問合せブロックです。SQL文が複数の問合せブロック(SELECT句)からなる場合、文全体のリライトを制御するには、各問合せブロックにリライト・ヒントを指定する必要があります。
問合せでREWRITE_OR_ERRORヒントを使用すると、問合せがリライトに失敗した場合、次のエラーが発生します。
ORA-30393: a query block in the statement did not rewrite
たとえば、次の問合せでは、使用するクエリー・リライトに適切なマテリアライズド・ビューがない場合、ORA-30393エラーを発行します。
SELECT /*+ REWRITE_OR_ERROR */ p.prod_subcategory, SUM(s.amount_sold) FROM sales s, products p WHERE s.prod_id = p.prod_id GROUP BY p.prod_subcategory;
複数のマテリアライズド・ビューを使用する際にリライトを制御するヒントが2つあります。NO_MULTIMV_REWRITEヒントを使用すると、2つ以上のマテリアライズド・ビューによる問合せのリライトが行われなくなり、NO_BASETABLE_MULTIMV_REWRITEヒントを使用すると、マテリアライズド・ビューと実表の組合せによる問合せのリライトが行われなくなります。
EXPAND_GSET_TO_UNIONヒントを使用すると、GROUP BY拡張機能を持つ問合せを、強制的に等価なUNION ALL問合せに拡張できます。詳細は、「拡張GROUP BYを持つ問合せのヒント」を参照してください。
