sjimmo (5/12/2010)
Unfortunately, many think that a hint is law, and have spent many hours debating hints, as well as showing hints that work and then not work because the optimizeer deemed that it knew better.
Well, actually hints are law. The term "optimizer hint" is highly misleading; "optimizer directive" would have been better. If you use an index hint, the optimizer WILL use that index for the query. If you specify to use hash joins, you WILL get hash joins. No matter how high the cost and how much cheaper an unhinted plan would have been.
If you have any evidence to the contrary, please let me know - it's quite likely to be a bug.