Example: Join Index Left Outer Joined on Six Tables - Teradata Vantage

The following join index definition left outer joins table t1 with, in succession, tables t2, t3, t4, t5, and t6 on a series of equality conditions made on foreign key-primary key relationships among the underlying base tables.

     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;

Even though the following query references fewer tables than are defined in the join index, you expect the Optimizer to include join index ji_out in its access plan because all the extra outer joins are defined on unique columns and the extra tables are the inner tables in the outer joins.

The bold EXPLAIN report text indicates that the Optimizer does select ji_out for the query plan:

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

    *** Help information returned. 18 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 high confidence to be 2 rows. 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 high
        confidence to be 2 rows. 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.