Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: Help on Explain
From:   Vivek Pandey

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);

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

  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
     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
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019