Re: NESTED LOOPS VS. HASH JOINS

"Marcelo Parra" <marcelop_at_pidcgroup.com> wrote:

>In wich case it is mos convenient to use hash joins and in
> which others nested loops for making a query?

Nested loops are usually used when you work through a small data set, retrieved the rows meeting the criteria, and then join those results with the corresponding rows in the large table. The idea is to work through the smaller data set first, and only then tackle the large table using an index to quickly access the corresponding rows in there.

Hash joins generate hash keys for the join criteria.

There are no hard and fast rules for optimising joins and SELECTs. A join can use an index scan today and provide acceptable performance. Two months from now, that same index scan could cause severe performance degredation due to the size of growing index... in which case you need to change that serial index scan to something else.

The bottom line is that you need to treat each case on its own merits. Explain plan and test. There are no magic wands when it comes to performance issues.

--
Billy