All Forums Database
ah255012 8 posts Joined 07/11
24 Jun 2012
Teradata EXPLAIN

Hi all,


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.






Regards, Aamir Hussain.
D3V1L 10 posts Joined 01/12
25 Jun 2012

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



Experience is what we get, when we don't get what we want!

dnoeth 4628 posts Joined 11/04
25 Jun 2012

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.



You must sign in to leave a comment.