Join Cardinality W/ Single-Row Unique Index Access to One Table | Vantage - Estimating Join Cardinality With Single-Row Unique Index Access to One of the Tables - Advanced SQL Engine - Teradata Database

About Estimating Join Cardinality With Single-Row Unique Index Access to One of the Tables

If one of the source tables of a join has single-row access using a unique index, the Optimizer retrieves the row during the optimization phase, substitutes the values of the columns from the retrieved row, and makes its cardinality estimates based on the substitution.

For example, if a join condition has a form like fact_table.yr_wk BETWEEN calendar.ytd_beg_wk AND calendar.ytd_end_wk, and the calendar table has single-row access path using either a UPI or a USI, the Optimizer fetches the row during the optimization phase, substitutes the actual values from the retrieved row for ytd_beg_wk and ytd_end_wk, and estimates the cardinality of the join based on the substitution.

EXPLAIN
SELECT *
FROM ordertbl, calendar
WHERE yr_wk BETWEEN ytd_beg_wk AND ytd_end_wk   <--  join predicate 
AND   calendar_date = 970101;    <--  UPI access to calendar table

...
  3)  We do a single-AMP RETRIEVE step from TPCD_OCES3.calendar by way 
      of the unique primary index "TPCD_OCES3.calendar.calendar_date = 
      DATE '1997-01-01'" with no residual conditions into Spool 2 
      (all_amps), which is duplicated on all AMPs.   The size of Spool 2
     is estimated with high confidence to be 14 rows (1,134 bytes).
     The estimated time for this step is 0.01 seconds.
  4) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
     all-rows scan, which is joined to TPCD_OCES3.order_75pct by way of
     an all-rows scan with no residual conditions.  Spool 2 and 
      TPCD_OCES3.order_75pct are joined using a product join, with a 
      join condition of ("(TPCD_OCES3.ordertbl.YR_WK >= Ytd_Beg_wk) 
      AND (TPCD_OCES3.ordertbl.YR_WK <= Ytd_End_wk)"). The input
     table TPCD_OCES3.ordertbl will not be cached in memory, but it
     is eligible for synchronized scanning. The result goes into Spool
     1 (group_amps), which is built locally on the AMPs. The size of
     Spool 1 is estimated with low confidence to be 365,114 rows (
     140,934,004 bytes). The estimated time for this step is 2.55
     seconds.

As you can see in step 4 of the query plan, the cardinality estimate for the range join predicate is computed by substituting the values from the row of the calendar table that was retrieved in step 3.