Re: Hash Joins vs. Nested Loops

rgaffuri_at_cox.net said...
> Someone I was working with told me the following. I was wondering if
> anyone has any comments, because I have not seen this in any
> articles/books.
>
> He said that when he tunes he generally likes to try to force nested
> loop joins over hash joins because these tend to increase performance?
> He has done this for a long time and he said that is based on
> experience.
>
> Anyone else notice this? I have never seen this anywhere else. BTW, I
> know the difference between hash joins and nested loops and know when
> either is generally used by the optimizer.
>

Speaking as an old-timer myself, experience can sometimes lead you to false conclusions. Things change so rapidly in this field that whenever I find myself about to say something like "I've always done it this way," an alarm goes off in my head. Remember, Oracle is constantly and continually changing the Cost-Based Optimizer. Anything done one way "for a long time" is bound to be flushed from the "correct way to do it" pool.

Never rely on what worked in the past. Well, almost never. I used to work in the physics arena, where I learned a lesson. Try something. If it works, great. If it doesn't work, make a hypothesis about what you can change to make it better. Test your hypothesis. Refine until it works the way you expect, remembering that you don't always have to make it perfect ... you can stop when it's good enough.

I remember when such advice was worth about two cents.

-- 
/Karsten
DBA > retired > DBA