適切な応答セットを判別するための手法 - Advanced SQL Engine - Teradata Database

単一テーブルの基数の見積もり

この問合わせは、サンプルの外部結合の問合わせで使用する条件によって制限される顧客テーブルの要素数を表わす単一テーブルのSELECTです。

この問合わせによって返されるレポートから、外部結合によって返されるはずの行数が分かります。

     SELECT  c.cust_num
     FROM sampdb.customer c
     WHERE c.district = 'K'
     AND  (c.service_type = 'ABC'
     OR    c.service_type = 'XYZ')
     ORDER BY 1;

この問合わせは18,034行を返します。

内部結合の基数の見積もり

この問合わせは、サンプルの外部結合の問合わせやその結果を説明する上で役立つ内部結合です。問合わせは、単一テーブルの基数の見積もりと同じ顧客行で始まりますが、1997年7月の収入行と突き合わせます。

     SELECT c.custnum, b.monthly_revenue
     FROM sampdb.customer AS c, sampdb2.revenue AS b
     WHERE c.custnum = b.custnum
     AND   c.district = 'K'
     AND   b.data_year_month = 199707
     AND  (c.service_type = 'ABC'
     OR    c.service_type = 'XYZ')
     ORDER BY 1;

この問合わせは13,010行を返します。

すべてのcustomerテーブルがmonthly_revenueテーブルの行と突き合わされることに注意してください。

例1:本当は内部結合である外部結合

この例1は、明示的な外部結合リクエストを行ないますが、その結果には驚かれるでしょう。

     SELECT c.custnum, b.monthly_revenue
     FROM sampdb.customer AS c
     LEFT OUTER JOIN sampdb2.revenue AS b ON c.custnum = b.custnum
     WHERE c.district ='K'
     AND   b.data_date = 199707
     AND  (c.service_type = 'ABC'
     OR    c.service_type = 'XYZ')
     ORDER BY 1;

この問合わせは、内部結合の基数の見積もりの単純な内部結合と同じ13,010行を返します。

結果の分析

このリクエストはLEFT OUTER JOINを指定していますが、最適化ルーチンはこれを単なる内部結合として扱います。これは、選択基準がすべてWHERE句にあるため、外部結合処理が完了した後に論理的に適用されるためです。例1と例3は論理的に同じであることを意味するため、同じ結果が生成されます。

EXPLAINは、最適化ルーチンがこの問合わせを内部結合と識別して実行します(“..。 joined using a merge join...”ではなく“...joined using a merge join...”)。そのため、これは内部結合の速さで実行されます。

最適化ルーチンは、この内部結合、マージ結合にとって最適な結合アルゴリズムを選択し、WHERE句からその条件を適用します。マージ結合の詳細については、<Teradata Vantage™ - SQLリクエストおよびトランザクション処理、B035-1142>を参照してください。

論理的には、WHERE句の用語は、LEFT OUTER JOINのON句の用語が適用された後に適用されます。この結果、18,034行の中間ステップが作成されます。しかしながら、WHERE句にはB.DATA_YEAR_MONTH=199707が含まれ、B.DATA_YEAR_MONTHの値がnullであるすべての行が削除され、13,010行という不正確な結果が返されてしまいます。

最適化ルーチンは、WHERE句がnullをTRUEと評価しないゆえに内部結合と同じである内部テーブルを参照していることを識別します。結果として、単なる内部結合の計画が生成され、左外部結合リクエストは無視されます。

EXPLAINには“...SAMPDB.CUSTOMER and SAMPDB2.REVENUE are joined using a merge join...”と記述されています。計画で外部結合を使っていれば、EXPLAINテキストには、“...SAMPDB.CUSTOMER and SAMPDB2.REVENUE are left outer joined using a merge join...”と示されていたはずです。

問合わせの結果(13,010行)は、ビジネスの命題にとって正しい応答ではありません。

コーディング エラー

問合わせ(customer)における外部テーブルをON句ではなくWHERE句に置くことによりSELECT文にエラーが発生し、結果として問合わせは左外部結合としてではなく単なる内部結合として処理されています。

2つの異なった観点からこのことを検討すると、期待した結果が返されなかった理由を説明することができます。

論理的説明

WHERE句は、問合わせではON句の後に表示されます。このため、WHERE句の述部は、ON句の述部の後に評価されます。

ON句の結果テーブルには18,034行あります。WHERE句の述部(b.data_year_month=199707)が適用されると、b.data_year_monthがnullである結果行がこの条件によってすべて削除され、選択した述部にnullがある結果が定義どおりに外部結合によって生成されるため、必然的に結果テーブルの行数は少なくなってしまいます。

物理的な説明

この問合わせのEXPLAINテキストは、論理的な説明に従っていません。

最適化ルーチンの基本的なタスクの1つは、すばやく分析できる代数形式に問合わせを再編成することです。最適化ルーチンは、その述部式で参照される列にnullが含まれていると、外部結合の内部テーブルを参照する述部がTRUEと評価されないことを認識します。

最適化ルーチンは、結合が外部結合ではなく内部結合であると識別します。結果として、内部結合を実行する計画が生成されます。EXPLAINには“...are joined using a merge join...”が含まれています。問合わせが外部結合として正しく行なわれていれば、この部分は“...are joined using an 外部 merge join...”となるはずです。

以上がこのSELECT文の記述の正しい解釈になりますが、いずれにしても、この問合わせは、ビジネスの命題を正しく記述したものではありません。

例2:正しい応答を返さない外部結合

例2は、外部結合としては正しく作成されていますが、提起された問題に取り組んでいないため、正しい応答が返されません。

     SELECT c.custnum, b.monthly_revenue
     FROM sampdb.customer AS c
     LEFT OUTER JOIN sampdb2.revenue AS b ON c.custnum = b.custnum
     AND  c.district ='K'
     AND  b.data_year_month = 199707
     AND (c.service_type ='ABC'
     OR   c.service_type ='XYZ')
     ORDER BY 1;

この問合わせは17713502行を返します。

結果の分析

この問合わせは、WHERE句で結果テーブルを修飾せずに左外部結合を指定しています。最適化ルーチンは、問合わせによって指定されたとおりに左外部結合の計画を構築します。結果として、1997年7月のK地区のABCまたはXYZのどちらかのサービス タイプを持つ内部テーブルの修飾された行(13,010行)と外部テーブルのすべての行(17,713,502行)が結合されます。

この結果の基数(17,713,502行)は、ビジネスの命題にとって正しい応答よりも絶対値で3桁大きくなります。

結果を修飾するためにWHERE句を使用しない場合、外部結合の結果には外部テーブルのすべての行から常に最低1行が入ります。

ON句に置かれる述部を修飾しても、結果から行が消去されることはありません。むしろ、それらは内部テーブルと外部テーブルの両方を参照する結合用語がTRUEと評価されるかどうかに関係なく、内部テーブルで一致しないものとして扱われます。言い換えれば、ON句の選択基準で定義されるのは、nullが一致しない行に付加される行だけです。この定義によって、指定した条件と一致する行に対する結果が制限されることはありません。

最適化ルーチンは述部b.data_year_month = 199707がTRUEと評価される行だけを返すことにより、ある程度は結果を限定します。

最適化ルーチンは、この左外部結合(マージ結合)にとって最適な結合アルゴリズムを選択し、ON句からその条件を適用します。

問合わせの結果(17,713,502行)はビジネスの命題にとって正しい応答ではありません。

コーディング エラー

問合わせのすべての選択基準をON句に置くと、SELECT文でエラーが発生します。外部結合の中の内部テーブルの選択基準(外部結合の一致しないNULL受入れ可能行を定義)だけを、ON句に置く必要があります。

外部結合の中の外部テーブル(この場合はcustomerテーブル)の選択基準は、WHERE句に置く必要があります。

例3:本当は内部結合である外部結合

この例は、明示的外部結合が指定されていますが、その結合が論理的には内部結合であるため、問合わせを実行する前に最適化ルーチンが単純な内部結合に変換してしまう別の問合わせです。

     SELECT c.custnum, b.monthly_revenue
     FROM sampdb.customer c
     LEFT OUTER JOIN sampdb2.revenue b ON c.custnum = b.custnum
     AND  c.district ='K'
     AND (c.service_type = 'ABC'
     OR   c.service_type = 'XYZ')
     WHERE b.data_year_month = 199707
     ORDER BY 1;

この問合わせは13,010行を返します。

結果の分析

この例は、例1に似ています。リクエストが外部結合を明示的に指定していても、最適化ルーチンはこの問合わせをここでも内部結合として扱います。内部テーブルに対するWHERE句は、外部結合からのこの問合わせを内部結合に論理的に変更します。

以前の例と同じように、外部結合の処理が終了して、Null値を入れられた行と左側および右側のテーブルの間にある一致しない行がすべて削除された後に、WHERE句が論理的に適用されます。最適化ルーチンは、問合わせのパフォーマンスを改善するために、これを内部結合として実行する必要があると判断します。

EXPLAINテキストは、例1のEXPLAINテキストと一致します。想定されるとおり、その応答セットも一致します。

最適化ルーチンは、この内部結合(マージ結合)にとって最適な結合アルゴリズムを選択し、WHERE句からその条件を適用します。

論理的には、WHERE句の用語は、LEFT OUTER JOINのON句の用語が適用された後に適用されます。その結果、18,034行の中間ステップが生成されます。しかしながら、WHERE句からのb.data_year_month=199707という用語が適用され、b.data_year_monthの値がnullであるすべての行が削除され、最終的に13,010行という不正確な結果が返されてしまいます。

最適化ルーチンは、WHERE句がnullをTRUEと評価しないゆえに内部結合と同じである内部テーブルを参照していることを識別します。結果として、単なる内部結合の計画が生成され、左外部結合リクエストは無視されます。

EXPLAINテキストには“...SAMPDB.CUSTOMER and SAMPDB2.REVENUE are joined using a merge join...”と記述されています。計画で外部結合を使っていれば、EXPLAINテキストには、“...SAMPDB.CUSTOMER and SAMPDB2.REVENUE are left outer joined using a merge join...”と示されていたはずです。

問合わせの結果(13,010行)はビジネスの命題にとって正しい応答ではありません。

コーディング エラー

以下を置くと、SELECT文でエラーが発生します。

    b.data_date = 199707

ON句ではなくWHERE句に置かれているため、結果として問合わせは左外部結合としてではなく単なる内部結合として処理されています。

例4: 外部結合、正しい回答

最後に、正しい回答を得ます。この例は、最初のビジネス命題に対して希望する応答を提供する外部結合です。

     SELECT c.custnum, b.monthly_revenue
     FROM sampdb.customer AS c
     LEFT OUTER JOIN sampdb2.revenue AS b ON c.custnum = b.custnum
     AND   b.data_year_month = 199707
     WHERE c.district ='K'
     AND  (c.service_type = 'ABC'
     OR    c.service_type = 'XYZ')
     ORDER BY 1;

この問合わせは18,034行を返します。

結果の分析

この問合わせ結果の基数(18,034行)は、返される行の予定数を調整します。単一テーブルの基数の見積もりを参照してください。

monthly_revenueの13,010行が(非NULL)値を持ちます。

EXPLAINからは、システムで左外部結合が実行されることがわかります。

最適化ルーチンは、この外部結合(マージ結合)にとって最適な結合アルゴリズムを選択し、WHERE句とON句から別々にその条件を適用します。

左(外部)テーブルは、WHERE句にある検索条件や、右(内部)テーブルが一致しないNULL受入れ可能行を定義するON句にある検索条件によって制限されます。

EXPLAINテキストは、これがtrueの外部結合であることを確認します(「...SAMPDB.c and SAMPDB2.b are left outer joined...」)。

問合わせの結果（18,034行）はビジネスの命題にとって正しい応答です。