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..
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 :-)