Example: More Tables Referenced by Join Index Definition than Referenced by Query - Teradata VantageCloud Lake

The following join index definition specifies all inner joins on tables t1, t2, t3, t4, t5 and t6 and specifies equality conditions on all the foreign key-primary key relationships among those tables.

     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;

Even though 6 tables are referenced in the join index definition, and all its join conditions are inner joins, you expect the Optimizer to include join index ji_in in its query plan for the following query, which only references 2 of the 6 tables, because all the conditions in the join index definition are based on foreign key-primary key relationships among the underlying base tables.

The bold EXPLAIN report text indicates that the Optimizer does select ji_in 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.ji_in 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.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.
     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.