The oracle optimizer is a software, trying to generate the best execution plan for a query, with the information it has. So in theory, if we provide it the most info we can, it should always be able to generate the best execution plan. But does it? Not always.
Even if we provide the optimizer fresh statistics of all the tables involved, there is still a risk that the optimizer
will take poor decisions, since it does not know how many rows will be returned by a join and it has to estimate the
result. Sometimes, wrongly estimating that a 2 table join will return a very small amount of rows, and this result being
used as the driving table in a Nested Loop join, can seriously hinder performance.
The solution in this particular case is to use the use_hash hint, to avoid the NL altogether.
No comments:
Post a Comment