Can any one of you guide me that in Teradata EXPLAIN plan what will result if it might indicate a missing join condition?
A. all-AMP join, B. all-row scan or C. missing join condition.
Option A and B?
Step1: First, we lock TableA ..and TableB
Step2: Next, we do an all-AMPs RETRIEVE step from TableA
by way of an all-rows scan with
no residual conditions into Spool 4 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs. The size of Spool 4 is ..
step3: We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to TableB by way of an all-rows scan with no
residual conditions. Spool 4 and TableB are joined
You'll get a step to prepare the smaller table using "duplicated to all AMPs" without an additional sort followed by a step with a "joined using a product join" and a dummy join condition "1=1".
But this should have be easy to find out on your own, just explain such a query.