Nested joins in Teradata

Database
Enthusiast

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 

4 REPLIES
Enthusiast

Re: Nested joins in Teradata

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

Enthusiast

Re: Nested joins in Teradata

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
Enthusiast

Re: Nested joins in Teradata

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

Enthusiast

Re: Nested joins in Teradata

Correct.

Cheers,