例: 結合インデックスのカバー - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
2021年1月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/ncd1596241368722.ditamap
dita:ditavalPath
ja-JP/ncd1596241368722.ditaval
dita:id
B035-1144
Product Category
Software
Teradata Vantage

基本テーブル、結合インデックス、問合わせ、およびEXPLAINレポートの以下のセットは、結合インデックス定義にある基礎となる基本テーブルの参照整合性関係が、結合インデックスの参照するテーブルよりも少ない数の基本テーブルを参照する問合わせに対して最適化ルーチンがこのインデックスを選択するかどうかにどのような影響を与えるかを示しています。

    CREATE SET TABLE t1, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      x1 INTEGER NOT NULL,
      a1 INTEGER NOT NULL,
      b1 INTEGER NOT NULL,
      c1 INTEGER NOT NULL,
      d1 INTEGER NOT NULL,
      e1 INTEGER NOT NULL,
      f1 INTEGER NOT NULL,
      g1 INTEGER NOT NULL,
      h1 INTEGER NOT NULL,
      i1 INTEGER NOT NULL,
      j1 INTEGER NOT NULL,
      k1 INTEGER NOT NULL,
    CONSTRAINT ri1 FOREIGN KEY (a1, b1, c1) REFERENCES t2 (a2, b2, c2),
    CONSTRAINT ri2 FOREIGN KEY (d1) REFERENCES t3(d3),
    CONSTRAINT ri3 FOREIGN KEY (e1, f1) REFERENCES t4 (e4, f4),
    CONSTRAINT ri4 FOREIGN KEY (g1, h1, i1, j1) 
      REFERENCES t5(g5, h5, i5, j5),
    CONSTRAINT ri5 FOREIGN KEY (k1) REFERENCES t6(k6));
    
    CREATE SET TABLE t2, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      a2 INTEGER NOT NULL,
      b2 INTEGER NOT NULL,
      c2 INTEGER NOT NULL,
      x2 INTEGER)
    UNIQUE PRIMARY INDEX(a2, b2, c2);
    
    CREATE SET TABLE t3, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      d3 INTEGER NOT NULL,
      x3 INTEGER)
    UNIQUE PRIMARY INDEX(d3);
    
    CREATE SET TABLE t4, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      e4 INTEGER NOT NULL,
      f4 INTEGER NOT NULL,
      x4 INTEGER)
    UNIQUE PRIMARY INDEX(e4, f4);
    
    CREATE SET TABLE t5, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      g5 INTEGER NOT NULL,
      h5 INTEGER NOT NULL,
      i5 INTEGER NOT NULL,
      j5 INTEGER NOT NULL,
      x5 INTEGER)
    UNIQUE PRIMARY INDEX(g5, h5, i5, j5);
    
    CREATE SET TABLE t6, NO FALLBACK, NO BEFORE JOURNAL, 
                         NO AFTER JOURNAL (
      k6 INTEGER NOT NULL,
      x6 INTEGER)
    UNIQUE PRIMARY INDEX(k6);

以下の結合インデックス定義は、外部結合t1からt3をd1=d3に残してから、t6になる外部結合をk1=k6に残します。

    CREATE JOIN INDEX ji_out AS
     SELECT d1, d3, k1, k6, x1
     FROM t1 LEFT OUTER JOIN t3 ON d1=d3 
     LEFT OUTER JOIN t6 ON k1=k6;

次の例は、結合インデックス内の外部結合が固有の列で問合わせテーブルに内部結合されるため、最適化ルーチンで結合インデックスのji_outを使用できる問合わせの例です。列のd1とk1は、t1内で外部キーとして宣言されています。t3のプライマリ キーの列d3、およびt6のプライマリ キーの列k6は、それらのテーブルの固有プライマリ インデックスとして宣言されています。SELECT文のEXPLAINには、「SUM step to aggregate from ji_out」(ji_outから集約するためのSUMステップ)が含まれます。

    SELECT d1, SUM(x1)
    FROM t1, t3
    WHERE d1=d3
    GROUP by 1;

以下の単純結合インデックス定義は、テーブルt1、t3、t6に内部結合を指定します。

    CREATE JOIN INDEX ji_in AS
    SELECT d1, d3, k1, k6, x1
    FROM t1, t3, t6
    WHERE d1=d3 
    AND k1=k6;

次の問合わせでは、最適化ルーチンで結合インデックスのji_inを使用できます。SELECT文のEXPLAINには、「SUM step to aggregate from ji_in」(ji_inから集約するためのSUMステップ)が含まれます。

    SELECT d1, SUM(x1)
    FROM t1, t3
    WHERE d1=d3
    GROUP BY 1;

この集約結合インデックス定義では、テーブルのt1、t2、およびt4に内部結合を指定しています。

    CREATE JOIN INDEX ji_in_aggr AS
    SELECT a1, e1, SUM(x1) AS total
    FROM t1, t2, t4
    WHERE a1=a2 
    AND   e1=e4 
    AND   a1>1
    GROUP BY 1, 2;

最適化ルーチンでは、次の問合わせの計画で結合インデックスのji_in_aggrを使用できます。これは、この結合インデックスに問合わせと同じ結合条件があるためです。SELECT文のEXPLAINには、("(ji_in_aggr.a1 > 2)      AND (ji_in_aggr.a1 >= 3)")という条件を使用したji_in_aggrからの取得ステップ)が含まれます。

    SELECT a1, e1, SUM(x1) AS total
    FROM t1, t2, t4
    WHERE a1=a2 
    AND   e1=e4 
    AND   a1>2
    GROUP BY 1, 2;

最適化ルーチンでは、次のクエリーについて結合インデックスのji_in_aggrを使用しません。これは、条件のb1=b2f1=f4がこの結合インデックスでカバーされていないためです。最適化ルーチンでは、指定された行を取得するためにフル テーブル スキャンを指定します。("t1.a1 > 2")という条件を使用した全行スキャンによるt1からの取得ステップ)、("b1 = t2.b2")という結合条件でのプロダクト ジョインを使用したt2からの結合ステップ)、および「JOIN step from t4」(t4からの結合ステップ)が含まれます。

    SELECT a1, e1, SUM(x1) AS total
    FROM t1, t2, t4
    WHERE b1=b2 
    AND   f1=f4 
    AND   a1>2
    GROUP BY 1, 2;

次の集約結合インデックスのji_in_aggrでは、テーブルのt1、t3、およびt6間の外部キーとプライマリ キーのリレーションシップを利用した条件により、テーブルのt1、t3、およびt6それぞれの内部結合を指定します。

    CREATE JOIN INDEX ji_in_aggr AS
    SELECT d1, k1, SUM(x1) AS total
    FROM t1, t3, t6
    WHERE d1=d3 
    AND   k1=k6
    GROUP BY 1, 2;

最適化ルーチンでは、ji_in_aggrが、問合わせで参照しない内部結合テーブルのt6とともに定義されていても、そのアクセス計画に下の問合わせ用の結合インデックスのji_in_aggrを含めます。最適化ルーチンではこの定義を承認可能です。これは、列のk1とk6に基づく、t1と追加のt6との間の外部キーとプライマリ キーのリレーションシップがあるためです。これらの列には外部キーとプライマリ キーのリレーションシップがあり、それらのそれぞれのテーブルの外部キーおよび固有プライマリ インデックスとして明示的に定義されています。SELECT文のEXPLAINには、「SUM step to aggregate from ji_in_aggr」(ji_in_aggrから集約するためのSUMステップ)が含まれます。

    SELECT d1, SUM(x1)
    FROM t1, t3
    WHERE d1=d3
    GROUP BY 1;

以下の結合インデックス定義は、外部結合テーブルt1、およびそれに続けてテーブルt2、t3、t4、t5、t6を、基礎となる基本テーブルにある外部キーとプライマリ キーの関係に対して作成された一連の等価条件に残しました。

    CREATE JOIN INDEX ji_out AS
    SELECT a1, b1, c1, c2, d1, d3, e1, e4, f1, g1, h1, i1, j1, j5, k1,
           k6, x1 
    FROM t1 
    LEFT OUTER JOIN t2 ON  a1=a2 
                       AND b1=b2 
                       AND c1=c2
    LEFT OUTER JOIN t3 ON  d1=d3 
    LEFT OUTER JOIN t4 ON  e1=e4 
                       AND f1=f4 
    LEFT OUTER JOIN t5 ON  g1=g5 
                       AND h1=h5 
                       AND i1=i5 
                       AND j1=j5 
    LEFT OUTER JOIN t6 ON  k1=k6;

次の問合わせでは結合インデックスのji_outで定義されているよりも少ない数のテーブルを参照していますが、最適化ルーチンでは、この結合インデックスをそのアクセス計画に含めます。この理由は、すべての追加の外部結合が固有の列に基づいて定義され、追加のテーブルは外部結合での内部テーブルだからです。SELECT文のEXPLAINには、最適化ルーチンの計画に「SUM step to aggregate from ji_out」(ji_outから集約するためのSUMステップ)が含まれることが示されます。

    SELECT a1, b1, c1, SUM(x1)
    FROM t1, t2
    WHERE a1=a2 
    AND   b1=b2 
    AND   c1=c2
    GROUP BY 1, 2, 3;

以下の結合インデックス定義は、すべての内部結合をテーブルt1、t2、t3、t4、t5、t6上に指定して、等価条件をこれらのテーブルに含まれるすべての外部キーとプライマリ キーの関係に指定します。

    CREATE JOIN INDEX ji_in AS
    SELECT a1, b1, c1, c2, d1, d3, e1, e4, f1, g1, g5, h1, i1, j1, k1,
           k6, x1 
    FROM t1, t2, t3, t4, t5, t6
    WHERE a1=a2 
    AND   b1=b2 
    AND   c1=c2 
    AND   d1=d3 
    AND   e1=e4 
    AND   f1=f4 
    AND   g1=g5 
    AND   h1=h5 
    AND   i1=i5 
    AND   j1=j5 
    AND   k1=k6;

結合インデックスのji_inでは内部結合で6つのテーブルを参照しますが、最適化ルーチンには下の問合わせのための結合インデックスが含まれます。この結合インデックスは、6つのテーブルのうちの2つのみを参照します。この理由は、この結合インデックス定義内のすべての条件が基礎となる各基本テーブル間の外部キーとプライマリ キーのリレーションシップに基づいているためです。SELECT文のEXPLAINには、最適化ルーチンの計画に「SUM step to aggregate from ji_in」(ji_inから集約するためのSUMステップ)が含まれることが示されます。

    SELECT a1, b1, c1, SUM(x1)
    FROM t1, t2
    WHERE a1=a2 
    AND   b1=b2 
    AND   c1=c2
    GROUP BY 1, 2, 3;

次の例では、定義内のテーブルの結合が適切に定義されているときに、最適化ルーチンで追加の内部結合のある結合インデックスを使用する方法を示しています。

以下の テーブル定義があると想定します。

    CREATE SET TABLE fact (
     f_d1 INTEGER NOT NULL,
     f_d2 INTEGER NOT NULL, 
    FOREIGN KEY (f_d1) REFERENCES WITH NO CHECK OPTION dim1 (d1),
    FOREIGN KEY (f_d2) REFERENCES WITH NO CHECK OPTION dim2 (d2))
    UNIQUE PRIMARY INDEX (f_d1,f_d2);
    
    CREATE SET TABLE dim1 (
     a1 INTEGER NOT NULL,
     d1 INTEGER NOT NULL, 
    FOREIGN KEY (a1) REFERENCES WITH NO CHECK OPTION dim1_1 (d11))
    UNIQUE PRIMARY INDEX (d1);
    
    CREATE SET TABLE dim2 (
     d1 INTEGER NOT NULL,
     d2 INTEGER NOT NULL)
    UNIQUE PRIMARY INDEX (d2);
    
    CREATE SET TABLE dim1_1 (
     d11 INTEGER NOT NULL,
     d22 INTEGER NOT NULL)
    UNIQUE PRIMARY INDEX (d11);

次に定義されている結合インデックスでは、fact.f_d1=dim1.d1fact.f_d2=dim2.d2の条件に基づく外部キーとプライマリ キーの結合によって、ファクト テーブルがディメンション テーブルのdim1とdim2に結合されています。ディメンション テーブルのdim1は、dim1.a1=dim1_1.d11の条件に基づく外部キーとプライマリ キーの結合によって、そのディメンション サブテーブルであるdim1_1にも結合されます。ファクト テーブルとテーブルのdim2に対する問合わせでは、テーブル間の外部キーとプライマリ キーのリレーションシップが使用されるために、結合インデックスのji_allが使用されます。

    CREATE JOIN INDEX ji_all AS 
    SELECT (COUNT(*)(FLOAT)) AS countstar, dim1.d1, dim1_1.d11, dim2.d2,
           (SUM(fact.f_d1)(FLOAT)) AS sum_f1
    FROM fact, dim1, dim2, dim1_1 
    WHERE ((fact.f_d1 = dim1.d1) 
    AND    (fact.f_d2 = dim2.d2)) 
    AND    (dim1.a1 = dim1_1.d11)
    GROUP BY dim1.d1, dim1_1.d11, dim2.d2 
    PRIMARY INDEX (d1);

集約演算のCOUNTとSUMの結果は、FLOAT型になります。<Teradata Vantage™ - SQLデータ定義言語 - 詳細トピック、B035-1184>の「CREATE JOIN INDEX」を参照してください。

最適化ルーチンには、次の問合わせに結合インデックスのji_allが含まれます。ファクト テーブルはdim1とdim2の両方のテーブルの親テーブルですが、それらのテーブルは結合インデックス定義の外部キー列に基づいてファクト テーブルと結合されます。同様に、dim1は外部キー列に基づいて結合インデックス定義でdim1_1に結合されます。SELECT文のEXPLAINには、「SUM step to aggregate from ji_all(ji_allから集約するためのSUMステップ)」が含まれます。

    SELECT d2, SUM(f_d1) 
    FROM fact, dim2 
    WHERE f_d2=d2 
    GROUP BY 1;