RE: In what circumstances might optimizer not choose the lowest cost?
- From: Iggy Fernandez <iggy_fernandez@xxxxxxxxxxx>
- To: "kibeha@xxxxxxxxx" <kibeha@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jan 2015 08:25:34 -0800
RE: Are there known circumstances where the optimizer does NOT choose the
lowest cost, even though same query with a hint CAN produce a plan with a lower
cost?
Obviously, yes, because otherwise the Oracle optimizer would be perfect. I
would even say that we should expect it to happen a lot because the search
space is large and the optimizer prunes it aggressively for obvious reasons.
"No optimizer is perfect and directives such as Oracle’s hints provide the
simplest workaround in situations in which the optimizer has chosen a
suboptimal plan. Hints are useful tools not just to remedy an occasional
suboptimal plan, but also for users who want to experiment with access paths,
or simply have full control over the execution of a query."
Iggy
Other related posts:
