例: 固有結合インデックスの作成 - Advanced SQL Engine - Teradata Database

このセットの各例は、次のデータベース オブジェクト定義に基づいています。

     CREATE TABLE t3 (
       a3 INTEGER, 
       b3 INTEGER, 
       c3 INTEGER);
     CREATE TABLE upi_t4 (
       a4 INTEGER, 
       b4 INTEGER, 
       c4 INTEGER) 
     UNIQUE PRIMARY INDEX(a4);
     CREATE TABLE usi_t5 (
       a5 INTEGER, 
       b5 INTEGER, 
       c5 INTEGER) 
     UNIQUE INDEX (b5);
     CREATE JOIN INDEX uji AS 
     SELECT b3, ROWID 
     FROM t3 
     WHERE c3 BETWEEN 200 AND 1000 
     UNIQUE PRIMARY INDEX (b3);

t3表に次の行を読み込むとします。

それから次の固有結合インデックスujiをt3に作成してみます。

     CREATE JOIN INDEX uji AS 
       SELECT b3, ROWID 
       FROM t3 
       WHERE c3 BETWEEN 200 AND 1000
     UNIQUE PRIMARY INDEX (b3);
*** Failure 2801 Duplicate unique prime key error in  user_name.target table.
               Statement# 1, Info =0
*** Total elapsed time was 1 second.

t3に存在していた最初の2行が、ujiのUPIとして定義された列b3に対して重複する値を持っているため、リクエストはアボートし、エラーを返します。基になる固有結合インデックスのUPIについて重複値を持つ行を挿入するリクエストです。

この状況を救済するには、t3の第1行を次のように削除します。

     DELETE t3 
     WHERE a3=5;
*** Delete completed. One row removed.
*** Total elapsed time was 2 seconds.

固有結合インデックスujiを正常に作成します。

     CREATE JOIN INDEX uji AS 
       SELECT b3, ROWID 
       FROM t3 
       WHERE c3 BETWEEN 200 AND 1000
     UNIQUE PRIMARY INDEX (b3);
      *** Index has been created.
      *** Total elapsed time was 11 seconds.

次の行をt3に挿入してみます。

     INSERT INTO t3 VALUES (5,6,200);
*** Failure 2801 Duplicate unique prime key error in  user_name 
.uji.
Statement# 1, Info =0
*** Total elapsed time was 3 seconds.

挿入しようとした行に、b3のUPIであるujiと重複する値があるため、この挿入は失敗します。

この例では、最適化ルーチンが固有結合インデックス(この場合はuji)を2 AMPアクセス パス検索の手段として使用する方法を示します。該当するEXPLAINテキストは太字印字で強調表示されています。

     EXPLAIN SELECT * 
             FROM t3 
             WHERE b3=10 
             AND c3 BETWEEN 300 AND 400;
*** Help information returned. 6 rows.
*** Total elapsed time was 1 second.

Explanation
------------------------------------------------------------------------
1) First, we do a two-AMP RETRIEVE step from  user_name.t3  by way of 
    uji "user_name.t3.b3 = 10" with a residual condition of 
    ("(user_name.t1.c3 >= 300) AND (user_name.t1.c3 <= 400)").  The
     estimated time for this step is 0.01 seconds.
  -> The row is sent directly back to the user as the result of
       statement 1.  The total estimated time is 0.01 seconds.

次のEXPLAINテキストは、t3表に対する他の問合わせの2 AMPアクセス パスとしても、最適化ルーチンがujiを使用できることを示しています。該当するEXPLAINテキストは太字印字で強調表示されています。

     EXPLAIN SELECT c3, SUM(a3) 
             FROM t3 
             WHERE b3=10 
             AND   c3 BETWEEN 300 AND 400
             GROUP BY 1;

Explanation
------------------------------------------------------------------------
1) First, we do a two-AMP SUM step to aggregate from user_name.t3  by way of uji  "user_name.t3.b3 = 10" with a residual condition
   of ("(user_name.t1.c3 <= 400) AND (user_name.t1.c3 >= 300)")
   , grouping by field1 ( user_name.t3.c3).  Aggregate Intermediate
   Results are computed locally, then placed in Spool 3.  The size of
   Spool 3 is estimated with low confidence to be 1 row (25 bytes).
   The estimated time for this step is 0.09 seconds.
2) Next, 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 low
   confidence to be 1 row (43 bytes).  The estimated time for this
   step is 0.03 seconds.
3) Finally, we send out an END TRANSACTION step to all AMPs involved
   in processing the request.

この例では、最適化ルーチンが固有結合インデックスujiを使用して、t3表からupi_t4表のUPIへの2 AMP結合のアクセス パスを修飾する方法を示します。該当するEXPLAINテキストは太字印字で強調表示されています。

     EXPLAIN SELECT * 
                      FROM t3, upi_t4 
                      WHERE t3.b3=1 
                      AND   t3.c3 BETWEEN 300 AND 400 
                      AND   t3.c3=upi_t4.a4;

Explanation
---------------------------------------------------------------------------
1) First, we do a two-AMP JOIN step from user_name.t3  by way of uji 
    "user_name.t3.b3 = 1" with a residual condition of (
   "(user_name.t3.c3 >= 300) AND (user_name.t3.c3 <= 400)"), which is
   joined to user_name.upi_t4 by way of the primary index
   "user_name.upi_t4.a4 = user_name.t3.c3" with a residual condition of (
   "(user_name.upi_t4.a4 >= 300) AND (user_name.upi_t4.a4 <= 400)").
    user_name.t3 and user_name.upi_t4 are joined using a nested join, with a
   join condition of ("(1=1)").  The result goes into Spool 1
   (one-amp), which is built locally on the AMPs.  The size of Spool
   1 is estimated with high confidence to be 1 row (111 bytes).  The
   estimated time for this step is 0.03 seconds.
-> The contents of Spool 1 are sent back to the user as the result of
   statement 1.  The total estimated time is 0.03 seconds.

この例では、最適化ルーチンがt3表の固有結合インデックスを使用して、t3表からusi_t5, indicating the similarity of using a USI for two-AMP access with using a unique join index for the same purpose表のUPIへの2 AMP結合のアクセス パスを修飾する方法を示します。該当するEXPLAINテキストは太字印字で強調表示されています。

     EXPLAIN SELECT * 
                  FROM t3, usi_t5
                  WHERE t3.b3=1 
                  AND   t3.c3 BETWEEN 300 AND 400 
                  AND   t3.c3=usi_t5.b5;

Explanation
------------------------------------------------------------------------- 
1) First, we do a two-AMP JOIN step from user_name.t3  by way of uji 
    "user_name.t3.b3 = 1" with a residual condition of ( 
   "(user_name.t3.c3 >= 300) AND (user_name.t3.c3 <= 400)"),  which is joined to  user_name.usi_t5 by way of uji  
   "user_name.usi_t5.b5  = user_name.t3.c3"
    with a residual condition of ( 
   "(user_name.usi_t5.b5 >= 300) AND (user_name.usi_t5.b5 <= 400)").
    user_name.t3 and user_name.usi_t5 are joined using a nested join, with a 
   join condition of ("user_name.t3.c3 = user_name.usi_t5.b5").  The result 
   goes into Spool 1 (one-amp), which is built locally on the AMPs. 
   The size of Spool 1 is estimated with high confidence to be 1 row 
   (87 bytes).  The estimated time for this step is 0.03 seconds. 
-> The contents of Spool 1 are sent back to the user as the result of 
   statement 1.  The total estimated time is 0.03 seconds.

この例では、最適化ルーチンがupi_t4表のUPIを使用して、t3からupi_t4表のUPIへの2 AMPネスト結合のアクセス パスとして固有結合インデックスujiを使用し、t3表への結合を修飾する方法を説明します。該当するEXPLAINテキストは太字印字で強調表示されています。

     EXPLAIN SELECT * 
                     FROM t3,upi_t4 
                     WHERE upi_t4.a4=1 
                     AND   upi_t4.b4=t3.b3 
                     AND   t3.c3 BETWEEN 300 AND 400;

Explanation
------------------------------------------------------------------------
1) First, we do a single-AMP JOIN step from user_name.upi_t4 by way of
   the unique primary index "user_name.upi_t4.a4 = 1" with no residual
   conditions, which is joined to user_name.t3  by way of unique join index uji 
    "user_name.t3.b3 = user_name.upi_t4.b4" with a residual condition of
   ("(NOT (user_name.t3.b3 IS NULL )) AND ((user_name.t3.c3 >= 300) AND
   (user_name.t3.c3 <= 400 ))"). user_name.upi_t4 and user_name.t3 are joined
   using a nested join, with a join condition of ("(1=1)").  The
   result goes into Spool 1 (one-amp), which is built locally on the
   AMPs.  The size of Spool 1 is estimated with high confidence to be
   1 row (177 bytes).  The estimated time for this step is 0.03
   seconds.
-> The contents of Spool 1 are sent back to the user as the result of
   statement 1.  The total estimated time is 0.03 seconds.

次の例は、usi_t5のUSI上で修飾された2-AMP結合を修飾し、次にujiを介して基本テーブルt3に結合します。

     EXPLAIN SELECT * 
                     FROM t3, usi_t5 
                     WHERE usi_t5.b5=1 
                     AND   usi_t5.c5=t3.b3 
                     AND   t3.c3 BETWEEN 300 AND 400;

Explanation
------------------------------------------------------------------------
1) First, we do a two-AMP JOIN step from user_name.usi_t5  by way of unique join index uji  "user_name.usi_t5.b5 = 1" with a residual condition
   of ("NOT (user_name.usi_t5.c5 IS NULL)"), which is joined to
    user_name.t3 by way of unique index uji "user_name.t3.b3 =
   user_name.usi_t5.c5" with a residual condition of ("(user_name.t3.c3 <=
   400) AND (user_name.t3.c3 >= 300)"). user_name.usi_t5 and user_name.t3 are
   joined using a nested join, with a join condition of ("(1=1)").
   The result goes into Spool 1 (one-amp), which is built locally on
   the AMPs.  The size of Spool 1 is estimated with high confidence
   to be 1 row (87 bytes).  The estimated time for this step is 0.03
   seconds.
-> The contents of Spool 1 are sent back to the user as the result of
   statement 1.  The total estimated time is 0.03 seconds.