Example: Creating a Unique Join Index - Advanced SQL Engine - Teradata Database

The examples in this set are based on the following database objects definitions.

     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);

Suppose you populate table t3 with the following rows.

You then attempt to create the following unique join index, uji, on 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.

The request aborts and returns an error because the first two existing rows in t3 have duplicate values for column b3, which was defined as the UPI for uji. Requests that attempt to insert a row that has a duplicate value for the UPI of an underlying unique join index.

To remedy this situation, delete the first row in t3 as follows.

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

Create the unique join index uji successfully.

     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.

Attempt to insert the following row into 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.

This insert fails because the row you attempted to insert has a duplicate value for b3, which is the UPI for uji.

This example demonstrates how the Optimizer can use a unique join index, in this case uji, as a means to a two-AMP access path retrieval. The relevant EXPLAIN text is highlighted in boldface type.

     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.

The following EXPLAIN text demonstrates that the Optimizer can also use uji as a two-AMP access path for other queries against table t3. The relevant EXPLAIN text is highlighted in boldface type.

     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.

This example demonstrates how the Optimizer can use the unique join index uji to qualify for an access path in a two-AMP join from table t3 to the UPI of table upi_t4. The relevant EXPLAIN text is highlighted in boldface type.

     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.

This example demonstrates how the Optimizer can use the unique join index of table t3 to qualify for an access path in a two-AMP join from table t3 to the UPI of table usi_t5, indicating the similarity of using a USI for two-AMP access with using a unique join index for the same purpose. The relevant EXPLAIN text is highlighted in boldface type.

     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.

This example demonstrates how the Optimizer can use the UPI of table upi_t4 to qualify for a join to table t3 using the unique join index uji as an access path in a two-AMP nested join from table t3 to the UPI of table upi_t4. The relevant EXPLAIN text is highlighted in boldface type.

     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.

This example qualifies for a two-AMP join qualified on the USI of usi_t5 and then joins to base table t3 via uji.

     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.