Joining Strategy-

Database
Enthusiast

Joining Strategy-

Hi all,

I was looking at the  various Joining strategies followed by TD optimizer like

a) MERGE Join

b) HASh Join

c) Nested Join

d) Exclusion Join

e) Product Join

when we think in terms of spool usage by each Joining strategy then i belive Nested Join is the best as it doesnt requires any Redistribution/sorting  or merging of the anawer sets as we are querying on a UPI/NUPI or a USI/NUSI in the where cluase.

Please correct me if i am wrong or add some thing here..

cheers!

Nishant

1 REPLY
Junior Contributor

Re: Joining Strategy-

Hi Nishant,

yes, the Nested Join might need no spool, but it's for joining a single row (or a very small set of data). 

There are several subtypes of Nested Joins with differnt plans, you'll find more than you ever want to read about join planning and optimization in the "SQL Request and Transaction Processing" manual :-)

Dieter