Archives of the TeradataForum
Message Posted: Wed, 03 Dec 2003 @ 18:10:23 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|