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
