RE: In what circumstances might optimizer not choose the lowest cost?
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Tue, 20 Jan 2015 08:25:34 -0800
Message-ID: <BLU179-W692F1EFBC91B089FE86F13EB4B0_at_phx.gbl>
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?
Date: Tue, 20 Jan 2015 08:25:34 -0800
Message-ID: <BLU179-W692F1EFBC91B089FE86F13EB4B0_at_phx.gbl>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 20 2015 - 17:25:34 CET