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
Explanation
---------------------------------------------------------------------------------
…
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.