Re: HASH vs NESTED LOOP join: when is one better than the other ?

Spendius wrote:
> Hello,
>
> I'm *not* talking about the cost as computed by the optimizer here.
> Please let's forget this figure.
>
> When, internally, does Oracle consider that a certain join will give
> better results than the other to join 2 tables ? Can the algorithm be
> simply summarized ?

Hash usually works better when you are retrieving many/most/all of the rows.

A nested loop will work better when you have good selectivity ( a small percentage ) of rows from one table that will need to find matches in the other table.

Does this help any? Received on Wed Mar 22 2006 - 10:24:56 CST