Example: All Outer Joins in Join Index Definition - Teradata Vantage

The following join index definition left outer joins t1 to t3 on da=d3 and then left outer joins that result to t6 on k1=k6.

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

You expect the Optimizer to use jiout with the following query, because all the outer joins in the join index are inner joined to the query tables on unique columns (d1 and k1 are declared foreign keys in t1 and d3 and k6, the primary keys for t3 and t6, respectively, are declared as the unique primary index for those tables).

The bold EXPLAIN report text indicates that the Optimizer does use jiout in its query plan.

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

     *** Help information returned. 17 rows.
     *** Total elapsed time was 1 second.
Explanation
------------------------------------------------------------------------
1) First, we lock HONG_JI.jiout for read on a reserved RowHash to prevent a global deadlock.
2) Next, 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 low confidence to be 1 row. The estimated time for
    this step is 0.03 seconds.
3) 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. The estimated time for this step is 0.04 seconds.
4)  Finally, we send out an END TRANSACTION step to all AMPs involved
    in processing the request.
   -> The contents of Spool 1 are sent back to the user as the result of
      statement 1.