この章では、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を持つ問合せのヒント」を参照してください。