@Sayan:
Yes, picking the right branches of the decision tree is a heck of a
complex job for the optimizer... And most likely the real deep
underlying causes for "buggy behaviour" like Doc ID 4112254.8 that
Mauro pointed me to, often really is when the optimizer mistakenly
eliminates certain branches that shouldn't have been eliminated.
Why a hint then can force the optimizer down a path it wouldn't have
considered otherwise, that's more a mystery to me. I can better
understand when a hint is ignored because the optimizer already has
chosen a path for which the hint is not applicable.
I may (perhaps) get smarter about that when I try a 10053 (which I am
not an expert in ;-)
@Stephen:
Actually it does not use the old execution plan (that would have been
nice), but gets me a new when I create the check constraint. If I drop
the check constraint I go back to the old plan. If I create constraint
again I get the "bad" plan. This is reproducible (also after flush) on
full scale test environment (dataguard copy in snapshot standby mode.)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx <mailto:kibeha@xxxxxxxxx>
@kibeha
On Tue, Jan 20, 2015 at 3:39 PM, Sayan Sergeevich Malakshinov
<malakshinovss@xxxxxxxxx <mailto:malakshinovss@xxxxxxxxx>> wrote:
Kim,
> 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?
In my humble opinion, that because desired plan branch was not
analyzed by CBO because of transformation(optimizer choose another
branch on previous steps), but when you hinted it, CBO chose right
branch.
It's quite often reason, for example a couple days ago i had issue
on 11.2.0.3 with join_elimination: test case -
https://gist.github.com/xtender/f0871ffa99b1413232e6
--
Best regards,
Sayan Malakshinov
http://orasql.org <http://orasql.org/>
