17.00 - 17.05 - 結合インデックス統計を使用した単一テーブルの式のカーディナリティの見積もり - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

このトピックでは、最適化ルーチンが単一テーブルの結合インデックスの選択リストで指定されている複合式で収集された統計を使用して、問合わせの述部内の基本テーブルの列で指定された複合式のカーディナリティをより正確に見積もる方法について説明します。このトピックで結合インデックスという用語の使用は、単一テーブル結合インデックスまたは同等のハッシュ インデックスのみを表わします。

このトピックでは、ハッシュ インデックスを非スパース単一テーブル結合インデックスと同等であると見なす必要があります。非スパース単一テーブル結合インデックスに関して記述されるものはすべて、同等のハッシュ インデックスに等しく適用され、選択リスト選択リストという用語が使用されます。

複雑な基本テーブルの式で統計を収集することはできませんが、選択リストで同じ式を指定する単一テーブルの結合インデックスを作成すると、式が単純な結合インデックス列に変換され、その列で統計を収集できるようになります。最適化ルーチンは、これらの統計を使用して、式が基本テーブルで問合わせの述部で指定されているときに、その式の評価の単一テーブルのカーディナリティを見積もることができます。

この機能は、問合わせの最適化における結合インデックスの適用を単純なクエリー リライトを越えた機能へと拡張し、基本テーブルの列を参照する述部式でコーディングされている複合式で統計が収集されていない、または収集できない場合に、単一テーブルの式のカーディナリティの見積もりに結合インデックスの統計の使用を含めます。

単一テーブルの式のカーディナリティの見積もり機能を拡張するには、次の条件が満たされる必要があります。
  • 統計が収集される結合インデックスは、選択リストで関連する複合式を指定する必要があります。
  • 関連する複合式を指定する結合インデックスの列セットで統計を収集する必要があります。

単純式および複合式の定義

選択リストで指定されている複合式から作成される単純な結合インデックス列で統計を収集できます。最適化ルーチンは、結合インデックス列で収集された統計を使用して、一致する基本テーブルの式を指定する問合わせの述部で指定された複合式の選択性をより正確に見積もることができます。

単純式は、述部の左側に単純な列参照のみを含む式で、複合式は、左側で単純な式以外を指定する式です。

例えば、以下の述部は単純な列参照を指定します。


単純な列参照を指定する述部

以下の述部は、左側の条件が単純な列参照ではないため、複合式を指定します。


複合式を指定する述部

すべての複合式がこの最適化の恩恵を受けられるわけではありません。最適化ルーチンは、複合式を単純な結合インデックス式に完全にマップできる場合にのみ、複合式から派生する結合インデックス列で収集された統計を使用できます。例えば、以下の述部は、単一テーブル結合インデックス式に完全にマップできないため、この最適化を活用できません。


最適化されていない述部の例

この式を、統計を収集可能な単一テーブル結合インデックス式にマップできません。

述部の照合と式のマッピング

結合インデックス統計が複雑な日付式を使用して記述された基本テーブルの述部で使用できるよりも正確なカーディナリティ見積もりを提供できる状況は複数あります。
  • クエリーの述部で指定されるEXTRACT式を結合インデックスの述部と照合できる場合。
  • クエリーの述部条件で指定されるEXTRACT…DATE式を結合インデックスの選択リストに指定される式にマップできる場合。

    最適化ルーチンは、同一の問合わせ式または一致しない述部内の一致する問合わせ式の部分集合を検出する際に式のマッピングを使用します。これが発生すると、最適化ルーチンは、述部を結合インデックスの同一列にマップし、それにより、結合インデックス列で収集された統計を式結果を使用して式結果のカーディナリティを見積もることができます。

述部の照合による複合式の単一テーブルのカーディナリティ見積もり

このトピックでは、問合わせの述部条件で指定されるEXTRACT/DATE式を結合インデックスの選択リストで指定される式に照合できる状況について説明します。一致する結合インデックス列で統計を収集したことがある場合、最適化ルーチンは、それらの統計を使用して述部結果のカーディナリティを見積もることができます。

例えば、基本テーブルt100_aで以下の結合インデックスが作成されたとします。

CREATE JOIN INDEX ji_a3 AS
  SELECT i1, c1
  FROM t100_a
  WHERE EXTRACT(YEAR FROM d2)>=1969;

次に、ji_a3ji_a3.i1のデフォルトの非固有プライマリ インデックスの統計を収集します。

COLLECT STATISTICS ON ji_a3 INDEX (i1);

結合インデックスji_a3は、基本テーブルt100_aで以下のクエリーをサポートするように設計されています。

クエリー1

SELECT *
FROM t100_a
WHERE EXTRACT(YEAR FROM d2)>=1969;

クエリー2

SELECT *
FROM t100_a
WHERE EXTRACT(YEAR FROM d2)>=1969
AND   i1>2;

ji_a3は問合わせ1と2の両方で指定されている述部と同一の述部(EXTRACT(YEAR FROM d2)>=1969)を指定し、問合わせ2はji_a3で統計を収集した列に対して述部条件(i1>2)を指定するため、両方の問合わせはji.i1で収集される統計を使用して最適化ルーチンの機能を拡張し、述部式のカーディナリティを見積もることができます。

この例の実際のカーディナリティは100行です。
EXPLAIN SELECT *
FROM t100a
WHERE EXTRACT(YEAR FROM d2)>=1970;
EXPLAIN出力の一部を次に示します。
...
3) We do an all-AMPs RETRIEVE step from df2.t100_a by way of 
   an all-rows scan with a condition of ("(EXTRACT(YEAR FROM
   (df2.t100_a.D2 )))= 1970") into Spool 1 (all_amps), which
   is built locally on the AMPs.  The size of Spool 1 is estimated 
    with high confidence to be 100 rows (49,800 bytes). The estimated
   time for this step is 0.03 seconds.

最適化ルーチンは、t_100aから派生されるSpool 1 (太字で強調表示されています)のカーディナリティを100行と見積ります。これは実際のカーディナリティと正確に一致します。

式のマッピングによる複合式の単一テーブルのカーディナリティ見積もり

このトピックでは、問合わせの述部条件で指定されるEXTRACT/DATE式を結合インデックスの選択リストで指定される式にマップできる状況について説明します。マッピングにより、EXTRACT式はDATE列に対して記述される式に変換されます。

最適化ルーチンが基本テーブルから結合インデックスへのマッピングによってカーディナリティ見積もりを行なう際、目的の年の値を含む基本テーブルのDATE列と結合インデックス定義内のEXTRACT関数の間で正規化が適用され、結合インデックス統計は、基本テーブルで指定される複合式を使用して記述された単一テーブルの述部に対して、その他の場合よりも正確なカーディナリティ見積もりを提供できます。

以下の例は、問合わせがDATE列d2,で述部を指定し、結合インデックスji_a4が単純な結合インデックス列と同じDATE列でEXTRACT関数を指定する状況を示しています。

この場合、最適化ルーチンは、結合インデックス列にマップする前に、述部のDATE条件を同等のEXTRACT形式に変換しようとします。最適化ルーチンは、最初に、問合わせの述部条件d2>=1969-01-01’を同等の式EXTRACT(YEAR FROM d2)>=‘1969’に変換し、さらにその式をji.yr>=1969に変換します。ji.yr>=1969は結合インデックス列での単純式のため、最適化ルーチンは、列ji.yrで収集された統計を使用して式結果のカーディナリティを見積もることができます。

次の結合インデックスを考えてみましょう。

CREATE JOIN INDEX ji_a4 AS
  SELECT i1, EXTRACT(YEAR FROM d2) AS yr, c1
  FROM t100_a
  WHERE i1<30;
インデックスで以下の統計を収集できます。
  • i1は、ji_a4のデフォルトの非固有プライマリ インデックスです。
    COLLECT STATISTICS ON ji_a4 INDEX(i1);
  • 式の別名yrは、関数EXTRACT(YEAR FROM d2)の値を表わします。
    COLLECT STATISTICS ON ji_a4 COLUMN(yr);

この例の実際のカーディナリティは30行です。

以下の問合わせは、結合インデックスji_a4の選択リストで指定されるEXTRACT関数にマップできるDATE式を述部で指定します。d2>=’1969-01-01’は一致しない述部内でのji_a4の選択リスト式EXTRACT(YEAR FROM d2) AS yrの一致するサブセットだからです。
EXPLAIN SELECT i1
 FROM t100a
 WHERE i1<30
 AND   d2>='1969-01-01';
EXPLAIN出力の一部を次に示します。
...
3) We do an all-AMPs RETRIEVE step from df2.JI_A4 by way of
   an all-rows scan with a condition of ("df2.JI_A4.yr >=
   1969") into Spool 1 (all_amps), which is built locally on the AMPs.
   The size of Spool 1 is estimated with high confidence to be 30 
    rows (960 bytes). The estimated time for this step is 0.03
   seconds.

最適化ルーチンは、t_100aから派生されるSpool 1 (太字で強調表示されています)のカーディナリティを30行と見積ります。これは実際のカーディナリティと正確に一致します。

結合インデックスji_a4は、複合式EXTRACT(YEAR FROM DATE) as yr=2010の別名であると定義されます。この定義により、最適化ルーチンは、複雑な述部式EXTRACT(YEAR FROM DATE)=2010を単純式yr=2010にマップでき、これによってyrで収集された統計を基本テーブルの列参照を使用して記述された述部式の単一テーブルのカーディナリティの見積もりに使用できます。

しかし、EXTRACT(YEAR FROM DATE)/2 = 1005などのクエリー述部式をコーディングする場合はどうでしょうか。最適化ルーチンは、この式をいくらか単純な式ji_a4.yr/2 = 1005にマップできますが、このマッピングでは結合インデックス統計を式の単一テーブルのカーディナリティを見積もるために使用できるようにはなりません。

このマッピングがより正確なカーディナリティの見積もりを促進できない理由は、マップされた式の外観はあまり複雑ではないものの、複合式のままであることです。式の複雑さから、最適化ルーチンは、ji_a4.yr/2=1005にマップできるかどうかに関係なく、yrで収集された統計を使用して述部式EXTRACT(YEAR FROM DATE)/2=1005の単一テーブルのカーディナリティを見積もることができません。

次の例は、式のマッピングのわずかに複雑な例です。この場合、結合インデックスの選択リストで指定される一致する複合式からの統計を、問合わせの述部で指定される式に使用します。この例は、スパースと非スパースの両方の結合インデックスに適用されますが、この例で使用される結合インデックスはスパースです。

次のSELECTリクエストについて考えてみます。

SELECT *
FROM perfcurrnew
WHERE BEGIN(vt) <= CURRENT_DATE
AND   END(vt)   >  CURRENT_DATE
AND   END(tt)   IS UNTIL_CHANGED;

Vantageは、結果セットのカーディナリティを見積もるために使用できる、この問合わせの述部で指定される複合式でのperfcurrnewからの統計の収集をサポートしません。

しかし、perfcurrnewに以下の結合インデックスを作成するとします。この結合インデックスの選択リストは、例のSELECTリクエスト内の基本テーブルperfcurrnewに対して記述された問合わせ述部のコンポーネントである式を指定します。

CREATE JOIN INDEX ji, NO FALLBACK, CHECKSUM = DEFAULT AS
  SELECT i, j, BEGIN(vt)(AS bvt), END(vt)(AS evt), END(tt)(AS ett)
  FROM perfcurrnew
  WHERE (evt>DATE)
  AND   END(tt) IS UNTIL_CHANGED
PRIMARY INDEX (i);
結合インデックスjiを作成した後、以下の統計を収集してperfcurrnewでSELECTリクエストをサポートすることができます。
  • iは、jiの非固有プライマリ インデックスです。
    COLLECT STATISTICS ON ji INDEX(i);
  • vtは、基本テーブルperfcurrnewでPERIOD タイプで定義されています。bvtおよびevtという別名の式は、それぞれperfcurrnew.vtのPERIOD列のBEGINおよびEND範囲関数をテーブルわし、ji.bvtおよびji.evtとして結合インデックスで定義されるときに、それらに対する統計を収集できます。
    COLLECT STATISTICS ON ji COLUMN(bvt);
    COLLECT STATISTICS ON ji COLUMN(evt);
  • ttperfcurrnewでPeriodタイプで定義されており、ettという別名が指定されているPERIOD列ji.ttのEND範囲関数で統計を収集できます。
    COLLECT STATISTICS ON ji COLUMN(ett);

基本テーブルperfcurrnewの実際のカーディナリティは691行です。

以下の例のSELECTリクエストは、CURRENT_DATE関数とIS UNTIL_CHANGED述部変数を使用する述部を指定します。IS UNTIL_CHANGEDは、PERIOD列perfcurrnew.ttに対して指定されたEND期間範囲関数の「forever」または「until changed」日付値を表わします。

結合インデックスjiは、SELECTリクエストで指定される述部の部分集合を使用して定義されるため、その述部のカーディナリティの見積もりは、END(vt)およびEND(tt)で指定される述部によって選択される行の数を表わします。

最適化ルーチンは、述部BEGIN(vt)<=CURRENT_DATE-2000を式ji.bvt<=CURRENT_DATE-2000にマップし、ji.bvtで収集される統計を使用します。

これらの統計から作成されたカーディナリティの見積もりを組み合わせたものが、元のSELECTリクエストで指定される3つの述部すべてによって返される結果のカーディナリティの評価を表わします。

この場合、クエリー内の述部はjiの選択リストで指定される述部と同じで、jiのカーディナリティはクエリーの結果セットのカーディナリティと同じであるため、jiで収集される統計はカーディナリティの見積もりの作成のために必要ではありません。
EXPLAIN SELECT *
FROM perfcurrnew
WHERE BEGIN(vt) <= CURRENT_DATE - 2000
AND   END(vt)   >  CURRENT_DATE
AND   END(tt)   IS UNTIL_CHANGED;
EXPLAIN出力の一部を次に示します。
...
3) We do an all-AMPs RETRIEVE step from a single partition of
   df2.perfcurrnew with a condition of (
   "((BEGIN(df2.perfcurrnew.vt ))<= DATE '2004-11-24') AND
   (((END(df2.perfcurrnew.tt ))= TIMESTAMP '9999-12-31
   23:59:59.999999+00:00') AND ((END(df2.perfcurrnew.vt ))> DATE
   '2010-05-17'))") into Spool 1 (all_amps), which is built locally
   on the AMPs.  The size of Spool 1 is estimated with high 
    confidence to be 723 rows (161,952 bytes). The estimated time for
   this step is 0.07 seconds.

perfcurrnewから派生されるSpool 1の見積もりカーディナリティは723行で(太字で強調表示されています)、誤差はほんの32行です。