Archives of the TeradataForum

Message Posted: Wed, 03 Dec 2003 @ 18:10:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Narayan,

The explain tells that it is a nested join invloving two AMPs.

The nested join will involve 2 AMPs if you specify a value for a USI in the where condition. This is obvious as USI needs 2 AMPs to be touched to determine the row.

You can make a single AMP nested join as well if the where condition specifies a value for a UPI.

ct t3 ( a int, b int) unique primary index(a) unique index(b);

explain
sel A.b,B.b from t3 A, t3 B where A.b=B.b and A.a=1;

Explanation
  1) First, we do a single-AMP JOIN step from ONE_DWH_LOAD_DB.A by way
     of the unique primary index "ONE_DWH_LOAD_DB.A.a = 1", which is
     joined to ONE_DWH_LOAD_DB.B by way of unique index # 4
     "ONE_DWH_LOAD_DB.B.b = ONE_DWH_LOAD_DB.A.b".  ONE_DWH_LOAD_DB.A
     and ONE_DWH_LOAD_DB.B are joined using a nested join, with a join
     condition of ("ONE_DWH_LOAD_DB.A.b = ONE_DWH_LOAD_DB.B.b").  The
     result goes into Spool 1 (one-amp), which is built locally on that
     AMP.  The size of Spool 1 is estimated with high confidence to be
     1 row.  The estimated time for this step is 0.67 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.67 seconds.

Thanks, Vivek.

	<Prev	Next>		<<First	<Prev	Next>	Last>>