All Forums Database
drmkd17 54 posts Joined 10/12
28 Dec 2013
Nested joins in Teradata

Can anybody please explain how nested joins work in Teradata???
 
What I know about the other joining strategies is that
 
Merge Join : PI-PI join is on the same AMP. Hence no redistribution
                     PI -Non PI join: Non PI column redistributed temporarily in spool.
                     Non PI - NON PI join: Both are redistributed temporarily in spool.
                     Small Table- Large Table : Samll table duplicated in spool and copied on all AMPS
 
HAsh join: Same aas merge join case 4. but small table copied in the AMP memory.
 
PLease validate and let me know for the Nested join 

Raja_KT 1246 posts Joined 07/09
29 Dec 2013

Nested join provides one of the fastest performance compared to other joins, since it does not touch all amps to join tables. It works mostly with PI/SI. The  WHERE clause uses an equijoin  with a constant value( for example  emp.empno=10) for a unique index in one table and those conditions also match some column of that single row to a PI/SI of the second table.

 

example: 

Select EMP.Ename , DEP.Deptno, EMP.salary

from

EMPLOYEE EMP ,

DEPARTMENT DEP

Where EMP.Enum = DEP.Enum

and EMp.Enum= 1234

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
29 Dec 2013

Hi,
Nested joins are the most efficient join types used in Teradata. They are mostly useful with OLTP requests. 
In a nested join a row is selected from one table using an equality condition on PI or USI. and this single row is then joined with the one or more rows from the other table selected based on a PI, USI, or NUSI.
Raja has qouted a good simple example of Nested join, I would add that in this case EMP.NUM must be a PI or USI.
 

Khurram

drmkd17 54 posts Joined 10/12
03 Jan 2014

Thanks Raja.
Thanks Khurram.
Please validate my understanding for the other joins as well.
Merge Join : PI-PI join is on the same AMP. Hence no redistribution
                     PI -Non PI join: Non PI column redistributed temporarily in spool.
                     Non PI - NON PI join: Both are redistributed temporarily in spool.
                     Small Table- Large Table : Samll table duplicated in spool and copied on all AMPS
 
HAsh join: Same as merge join case 4. but small table copied in the AMP memory.
 
Nested join: Happens on USI/PI column and when this column is equated to a constant.
so when optimizer comes across this scenario it will go for a nested join
is that right?
 
Thanks for your help

Raja_KT 1246 posts Joined 07/09
03 Jan 2014

Correct.
Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.