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
日本語 (日本)

次の節では、結合インデックスの使用によって結果として起きる、テーブル選択、削除、挿入、更新で達成される最適化の性能を示します。

結合インデックスについての詳細は、<Teradata Vantage™ - データベースの設計、B035-1094>および<Teradata Vantage™ - SQLデータ定義言語 - 詳細トピック、B035-1184>を参照してください。

単純な結合問合わせ

次は単純な結合問合わせの例です。

     EXPLAIN
     SELECT o_orderdate, o_custkey, l_partkey, l_quantity,
            1_extendedprice
     FROM lineitem, ordertbl
     WHERE l_orderkey=o_orderkey;

EXPLAIN出力の一部を次に示します。

  4) We do an all-AMPs JOIN step in TD_MAP1 from DB1.ordertbl by way
     of a RowHash match scan with no residual conditions, which is
     joined to DB1.lineitem by way of a RowHash match scan with no
     residual conditions.  DB1.ordertbl and DB1.lineitem are joined
     using a sliding-window merge join, with a join condition of (
     "DB1.lineitem.l_orderkey = DB1.ordertbl.o_orderkey").  The
     result goes into Spool 1 (group_amps), which is built locally on
     the AMPs.  The size of Spool 1 is estimated with low confidence to
     be 11 rows (869 bytes).  The estimated time for this step is 0.21
     seconds.

結合インデックスでの検索

次は結合インデックスの探索条件の例です。

     EXPLAIN
     SELECT o_orderdate, o_custkey, l_partkey, l_quantity,
            l_extendedprice
     FROM lineitem, ordertbl
     WHERE l_orderkey=o_orderkey
     AND   o_orderdate>'1997-11-01';

EXPLAIN出力の一部を次に示します。

  4) We do an all-AMPs JOIN step in TD_MAP1 from 500 partitions of
     DB1.ordertbl by way of a RowHash match scan with a condition of (
     "DB1.ordertbl.o_orderdate > DATE '1997-11-01'"), which is joined
     to DB1.lineitem by way of a RowHash match scan with no residual
     conditions.  DB1.ordertbl and DB1.lineitem are joined using a
     sliding-window merge join, with a join condition of (
     "DB1.lineitem.l_orderkey = DB1.ordertbl.o_orderkey").  The
     result goes into Spool 1 (group_amps), which is built locally on
     the AMPs.  The size of Spool 1 is estimated with low confidence to
     be 2 rows (158 bytes).  The estimated time for this step is 0.21
     seconds.

結合インデックスに対する集約

次は結合インデックスに対する集約の例です。

     EXPLAIN
     SELECT l_partkey, AVG(l_quantity), AVG(l_extendedprice)
     FROM lineitem, ordertbl
     WHERE l_orderkey=o_orderkey
     AND   o_orderdate >'1997-11-01'
     GROUP BY l_partkey;

EXPLAIN出力の一部を次に示します。

  4) We do an all-AMPs JOIN step in TD_MAP1 from DB1.ordertbl by way
     of a RowHash match scan with no residual conditions, which is
     joined to DB1.lineitem by way of a RowHash match scan with no
     residual conditions.  DB1.ordertbl and DB1.lineitem are joined
     using a sliding-window merge join, with a join condition of (
     "DB1.lineitem.l_orderkey = DB1.ordertbl.o_orderkey").  The
     result goes into Spool 2 (all_amps), which is built locally on the
     AMPs.  The size of Spool 2 is estimated with low confidence to be
     11 rows (341 bytes).  The estimated time for this step is 0.21
     seconds.
  5) We do an all-AMPs SUM step in TD_Map1 to aggregate from Spool 2
     (Last Use) by way of an all-rows scan, grouping by field1 (
     DB1.lineitem.l_partkey).  Aggregate Intermediate Results are
     computed globally, then placed in Spool 4 in TD_Map1.  The size of
     Spool 4 is estimated with no confidence to be 9 rows (369 bytes).
     The estimated time for this step is 0.23 seconds.
  6) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 4 (Last Use)
     by way of an all-rows scan into Spool 1 (group_amps), which is
     built locally on the AMPs.  The size of Spool 1 is estimated with
     no confidence to be 9 rows (522 bytes).  The estimated time for
     this step is 0.16 seconds.

別の基本テーブルとの結合に使用される結合インデックス

次は別の基本テーブルとの結合に使用される結合インデックスの例です。

     EXPLAIN
     SELECT o_orderdate, c_name, c_phone, l_partkey,l_quantity,
            l_extendedprice
     FROM lineitem, ordertbl, customer
     WHERE l_orderkey=o_orderkey
     AND   o_custkey=c_custkey;

EXPLAIN出力の一部を次に示します。

  5) We do an all-AMPs JOIN step in TD_MAP1 from DB1.ordertbl by way
     of a RowHash match scan with no residual conditions, which is
     joined to DB1.lineitem by way of a RowHash match scan with no
     residual conditions.  DB1.ordertbl and DB1.lineitem are joined
     using a sliding-window merge join, with a join condition of (
     "DB1.lineitem.l_orderkey = DB1.ordertbl.o_orderkey").  The
     result goes into Spool 2 (all_amps), which is redistributed by the
     hash code of (DB1.ordertbl.o_custkey) to all AMPs in TD_Map1.
     Then we do a SORT to order Spool 2 by row hash.  The size of Spool
     2 is estimated with low confidence to be 11 rows (407 bytes).  The
     estimated time for this step is 0.11 seconds.
  6) We do an all-AMPs JOIN step in TD_MAP1 from DB1.customer by way
     of a RowHash match scan with a condition of (
     "(DB1.customer.c_custkey <= 49999) AND (DB1.customer.c_custkey
     >= 0)"), which is joined to Spool 2 (Last Use) by way of a RowHash
     match scan.  DB1.customer and Spool 2 are joined using a merge
     join, with a join condition of ("o_custkey =
     DB1.customer.c_custkey").  The result goes into Spool 1
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with low confidence to be 11 rows (1,188
     bytes).  The estimated time for this step is 0.21 seconds.

単一テーブルの問合わせを解決するために使用される結合インデックス

次は単一テーブルに対する問合わせを解決するために使用される結合インデックスの例です。

     EXPLAIN
     SELECT l_orderkey, l_partkey, l_quantity, l_extendedprice
     FROM lineitem
     WHERE l_partkey = 1001;

EXPLAIN出力の一部を次に示します。

  3) We do an all-AMPs RETRIEVE step in TD_MAP1 from DB1.ORDERJOINLINE
     by way of an all-rows scan with a condition of (
     "DB1.ORDERJOINLINE.l_partkey = 1001") into Spool 1 (group_amps),
     which is built locally on the AMPs.  The size of Spool 1 is
     estimated with no confidence to be 1 row (69 bytes).  The
     estimated time for this step is 0.15 seconds.

結合インデックス上でのセカンダリ インデックスの作成と使用

次は結合インデックス上でのセカンダリ インデックスの作成と使用の例です。

     CREATE INDEX shipidx(l_shipdate) ON OrderJoinLine;
   
     ***Index has been created.
     ***Total elapsed time was 5 seconds.

     EXPLAIN
     SELECT o_orderdate, o_custkey, l_partkey, l_quantity,  
            l_extendedprice
     FROM lineitem, ordertbl
     WHERE l_orderkey=o_orderkey
     AND   l_shipdate='1997-09-18';

EXPLAIN出力の一部を次に示します。

  3) We do an all-AMPs RETRIEVE step in TD_MAP1 from DB1.ORDERJOINLINE
     by way of an all-rows scan with a condition of ("(NOT
     (DB1.ORDERJOINLINE.o_orderdate IS NULL )) AND
     (DB1.ORDERJOINLINE.l_shipdate = DATE '1997-09-18')") into Spool 1
     (group_amps), which is built locally on the AMPs.  The size of
     Spool 1 is estimated with no confidence to be 8 rows (632 bytes).
     The estimated time for this step is 0.15 seconds.

6つのテーブルの結合インデックス左外部結合

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

     CREATE JOIN INDEX jiout 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が含まれます。カバーするクエリーで指定されていないテーブルが1つ以上定義に含まれるカバー結合インデックスを、ブロード結合インデックスといいます。さまざまなクエリーでブロード結合インデックスを利用できます。特に便利なのは、ファクト テーブルとディメンション テーブルの間で外部キーとプライマリ キーの関係が定義されており、その結果、ディメンション テーブルのサブセットでクエリーをカバーするためにこのインデックスを利用できるようになっている、という場合です。

太字のEXPLAINレポート テキストは、最適化ルーチンがクエリー計画に対してji_outを選択することを示しています。これは、ブロード結合インデックスが問合わせのカバーに使用される例です。

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

EXPLAIN出力の一部を次に示します。

      3) We do an all-AMPs SUM step to aggregate from  HONG_JI.jiout  
         by way of an all-rows scan with no residual conditions, and 
         the grouping identifier in field 1. Aggregate Intermediate 
         Results are computed locally, then placed in Spool 3. The 
         size of Spool 3 is estimated with high confidence to be 2
         rows. The estimated time for this step is 0.03 seconds.
      4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by 
         way of an all-rows scan into Spool 1 (group_amps), which is 
         built locally on the AMPs. The size of Spool 1 is estimated 
         with high confidence to be 2 rows. The estimated time for 
         this step is 0.04 seconds.

問合わせによる参照よりも多くのテーブルが結合インデックス定義で参照される

以下の結合インデックス定義は、すべての内部結合をテーブル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;

結合インデックス定義では6つのテーブルが参照され、結合条件はすべて内部結合ですが、最適化ルーチンでは次のクエリーのクエリー計画に、6つのテーブルのうち2つだけを参照する結合インデックスji_ininが含まれるようになります。これは、結合インデックス定義内のすべての条件が、基礎となる基本テーブル間の外部キーとプライマリ キーの関係に基づいているためです。これも、ブロード結合インデックスが問合わせのカバーに使用される例です。

太字のEXPLAINレポート テキストは、最適化ルーチンがクエリー計画に対してji_inを選択することを示しています。

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

EXPLAIN出力の一部を次に示します。

      3) We do an all-AMPs SUM step to aggregate from  HONG_JI.ji_in  
         by way of an all-rows scan with no residual conditions, 
         and the grouping identifier in field 1. Aggregate Intermediate
         Results are computed locally, then placed in Spool 3. The size
         of Spool 3 is estimated with high confidence to be 2 rows.
         The estimated time for this step is 0.03 seconds.
      4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by 
         way of an all-rows scan into Spool 1 (group_amps), which
         is built locally on the AMPs. The size of Spool 1 is estimated
         with high confidence to be 2 rows. The estimated time for
         this step is 0.04 seconds.

多方向結合結果で定義された結合インデックスの使用

次は多方向結合結果で定義された結合インデックスの定義と使用の例です。

     CREATE JOIN INDEX CustOrderJoinLine
     AS SELECT (1_orderkey,o_orderdate,c_nationkey,o_totalprice),
               (1_partkey,1_quantity,1_extendedprice,1_shipdate)
     FROM (lineitem
     LEFT OUTER JOIN ordertbl ON 1_orderkey=o_orderkey)
     INNER JOIN customer ON o_custkey=c_custkey
     PRIMARY INDEX (1_orderkey);
   
     *** Index has been created.
     *** Total elapsed time was 20 seconds.


     EXPLAIN
     SELECT (1_orderkey,o_orderdate,c_nationkey,o_totalprice),
            (1_partkey,1_quantity,1_extendedprice,1_shipdate)
     FROM lineitem,ordertbl,customer
     WHERE 1_orderkey=o_custkey
     AND o_custkey=c_custkey
     AND c_nationkey=10;

EXPLAIN出力の一部を次に示します。

   3) We do an all-AMPs RETRIEVE step from join index table
      df2.CustOrderJoinLine by way of an all-rows scan with a
      condition of (“df2.CustOrderJoinLine.c_nationkey=10”) into
      Spool 1, which is built locally on the AMPs. The input table will
      not be cached in memory, but it is eligible for synchronized
      scanning. The result spool file will not be cached in memory.
      The size of Spool 1 is estimated to be 200 rows. The estimated 
      time for this step is 3 minutes and 57 seconds.