|
|
|
|
|
|
| Re: hash join vs nested loops [message #450633 is a reply to message #450485] |
Thu, 08 April 2010 08:02  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hash joins are preferred when joining large proportions (say, >10%) of a large table.
Indexed Nested Loops joins are preferred when joining small proportions (say, <1%) of a large table.
In the middle-ground, there are many factors that may make one more preferable to the other, such as the size of the Buffer Cache, memory available for hash joins, amount of disk cache, and probably tons of other stuff.
Oracle's optimizer will take many of these factors into account. Unless all factors are identical in two databases, you cannot expect the same result, even if they are the same version.
Also, 11g collects statistics on data automatically, and it probably gathers statistics of a different quality to those you gathered manually in 9i, leading to better optimizer decisions.
Even if all factors are equal, 11g optimiser will still have different / improved algorithms that will lead to different plans. It is possible that on average these changes favour Nested Loops, but it is MUCH MORE LIKELY to be one of those other factors if you are noticing a trend.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
