Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Hash Join vs. Nested Loops

Re: Hash Join vs. Nested Loops

From: Jan Hidders <jan.hidders_at_REMOVE.THIS.ua.ac.be>
Date: 28 Feb 2003 21:22:50 +0100
Message-ID: <3e5fc51a.0@news.ruca.ua.ac.be>


Mikito Harakiri wrote:
>
>Once again, ordinary Nested Loops is so obviously flawed that it doesn't
>make sence to compare it to anything. Indexed Nested Loops, on the other
>hand, have performance profile comarable to either Hash Join or Sort Merge
>Join on large inputs. It blows them away on small inputs.

I believe your original guess was right: the differences are important when the data is mainly on disk. The advantage of an hash-index vs. something like a B-tree index is that if you have a good hash function you probably only need to acces 1 disk page where with a B-tree this is only true if the whole index fits in main memory. The advantage of the merge join vs. nested loop with index is that chances are bigger that you still have the relevant pages in your page buffer when you re-encounter the same join value in the relation of the outside loop.

Received on Fri Feb 28 2003 - 14:22:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US