Re: 2 table hash join order

"tq" <tomm_quinn_at_yahoo.com> wrote in message news:1118095140.061617.98390_at_g44g2000cwa.googlegroups.com...
>
> I have a simple question - If I have 2 tables, 1 small and 1 large,
> what are the implications of the table order if I'm using a hash join?
> Does it matter? Oracle has got to create the hash keys on both join
> columns anyway. What about if the large table is too large to fit the
> total hashed key into memory - is it better to drive from small to
> large in this case? Thanks for input.
>

Unless you force it to do otherwise, the optimizer will process the smaller data set first (building it into an in-memory hash table if there is sufficient memory), and process the larger data set second, (probing the in-memory hash table)

Prior to version 10g, the only exception to this would be for an outer join where the larger table was the preserved table (i.e. the smaller table had the (+) on its join column)

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005