Re: Hash Join vs. Nested Loops

Scott David Daniels <Scott.Daniels_at_Acm.Org> wrote in message news:<3e606387$1_at_nntp0.pdx.net>...
> Also, it is rare to have join indices for all
> join conditions; while you may have in mind a class of
> applications where that is true, tossing out H-J and M-J
> misses some big optimizations when the join condition is
> not fully indexed.

Wouldn't building [temporary] join index for Non-Indexed Nested Loops be much different [performance-wize] from building [temporary] hash table for Hash Join, or from sorting one input relation in case of Sort Merge Join?

http://www.cs.cornell.edu/database/quo/lec.pods99.ps introduces the concept of "least expected cost". Basically, optimizer costing is so much error prone, that selecting join method is never reliable for even modest complexity queries. If performance of HJ and MJ is only marginally better on large inputs, and several order of magnitude worse for small inputs, wouldn't discarding the former 2 methods be just a straightforward application of the "least expected cost" method? (Let alone that we'll trim optimizer's search space to 1/3:-) Received on Fri Mar 21 2003 - 01:38:57 CET