Re: NESTED LOOPS VS. HASH JOINS

"Saikat Chakraborty" <saikatchak_at_hotmail.com> wrote:

>What I had noticed that hash join is more effective when:
>1. The resultset data volume is large. In fact the larger the data
>volume, the more effective is the hash join.

The size of the result set, does not have much to do with the join method.

Consider a join of two huge tables doing an aggregation using the GROUP BY clause. Only 10 rows could be in the result set. However, millions of rows have been joined.

>2. Of the two tables that are joined, one is of less volume compared
>to the other.

That is more applicable regarding what join method is the best - the sizes of the data sets to be joined.

>Nested loop is effective when the resultset is very small.

Again, not true. The size of the result set is not the issue. The sizes of the data sets that needs to be joined is.

--
Billy