Re: Very unstable execution plan
From: Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
Date: Tue, 11 Jun 2013 13:29:38 +0200
Message-ID: <51B70A22.9000607_at_mgm-tp.com>
Jonathan Lewis, 10.06.2013 11:52:
> If you check line 5 of the "German-sort" plan you'll see that the
> join predicate hasn't been pushed into the view, and since the join
> predicate is the thing that allows an indexed access path into
> VU_TMP_SPARTE this is the first level explanation of the full scan.
> The question is then, why is the predicate not pushed. I suspect
> it's the combination of the analytic functions (window sort) and
> German NLS-Sort - in other words, it's nothing to do with the index,
> it's all to do with the analytic order by.
>
> There are many cases where a predicate on a view cannot safely be
> pushed inside a view with an analytic function in case it changes the
> result - selecting the first value after using a German sort then
> eliminating some rows may give you a different result from
> eliminating rows and then doing a German sort on the remainder.
Date: Tue, 11 Jun 2013 13:29:38 +0200
Message-ID: <51B70A22.9000607_at_mgm-tp.com>
Jonathan Lewis, 10.06.2013 11:52:
> If you check line 5 of the "German-sort" plan you'll see that the
> join predicate hasn't been pushed into the view, and since the join
> predicate is the thing that allows an indexed access path into
> VU_TMP_SPARTE this is the first level explanation of the full scan.
> The question is then, why is the predicate not pushed. I suspect
> it's the combination of the analytic functions (window sort) and
> German NLS-Sort - in other words, it's nothing to do with the index,
> it's all to do with the analytic order by.
>
> There are many cases where a predicate on a view cannot safely be
> pushed inside a view with an analytic function in case it changes the
> result - selecting the first value after using a German sort then
> eliminating some rows may give you a different result from
> eliminating rows and then doing a German sort on the remainder.
Thanks for the detailed analysis, I haven't noticed that part.
In this special case we could actually change the vertrag_ext_ref column to a number do to the data being processed and then the plan is (obviously) independent from the NLS_SORT setting. This kind of supports your theory I guess.
Regards
Thomas
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 11 2013 - 13:29:38 CEST