Bryant,

You are right about the indexes being one of the keys. Another is size of the data inputs. It is hard (nearly impossible) to "push" the optimizer to choose one join over the other without adding/removing indexes or dramatically growing/decreasing the size of the result set.

I usually scan for the join type and if it is not the one I think the optimizer should be using (my best guess) then I try to figure out why. Most of the time hash joins show up when I think merge joins should have been used. I can usually trace these back to lack of indexes or out-dated statistics. Every now and then I find a nested loop join when I think the optimizer should be doing a merge. (My guess is usually based on result set size.) I sometimes try to push the optimizer into a merge, but in the end this does not really buy me much in optimization.

Randy