Hash Join vs. Nested Loops
From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Tue, 25 Feb 2003 15:03:11 -0800
Message-ID: <hIS6a.12$Ih6.23_at_news.oracle.com>
I'm trying to understand the reason why different join methods exist, in particular, if Hash and Merge join are theoretically legitimate methods, or just some artifacts of Non Randomly Accessed Memory (aka disks). After all, when programming I'm always using "for/while" loop operator, not some obscure hash join.
Date: Tue, 25 Feb 2003 15:03:11 -0800
Message-ID: <hIS6a.12$Ih6.23_at_news.oracle.com>
I'm trying to understand the reason why different join methods exist, in particular, if Hash and Merge join are theoretically legitimate methods, or just some artifacts of Non Randomly Accessed Memory (aka disks). After all, when programming I'm always using "for/while" loop operator, not some obscure hash join.
Suppose both tables are cached into the memory. Then, we can ignore Random
IO versus Sequential IO difference. Is Nested Loop algorithm inherently
"nonoptimal" compared to Hash Join? What HJ does
differently? It does put a hash table in front of the inner table. That's
just extra work to me. Then, it does the similar nested looping which is
only sugarcoated by the hash table probe. Specifically, database engine
probes the hash table, so that if there is no match, then the target table
is not accessed. But Indexed Nested Loop does the similar probing of the
join index! Why Hash Join is
considered "more scalable"? Do Hash and Merge joins make sence for in-memory
databases?
Received on Wed Feb 26 2003 - 00:03:11 CET