Example: More Inner Joined Tables in Aggregate Join Index Definition than in Query - Teradata VantageCloud Lake

The following aggregate join index definition specifies inner joins on tables t1, t3, and t6 using conditions that exploit a foreign key-primary key relationship between table t1 and tables t3 and t6, respectively.

     CREATE JOIN INDEX jiin_aggr AS
      SELECT d1, k1, SUM(x1) AS total
      FROM t1, t3, t6
      WHERE d1=d3
      AND   k1=k6
      GROUP BY 1, 2;

You expect the Optimizer to include join index jiin_aggr in its access plan for the following query even though jiin_aggr is defined with an inner joined table, t6, that the query does not reference. This is acceptable to the Optimizer because of the foreign key-primary key relationship between t1 and the extra table, t6, on columns k1 and k6,which have a foreign key-primary key relationship and are explicitly defined as a foreign key and as the unique primary index for their respective tables.

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