RE: Very unstable execution plan
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Jun 2013 09:52:56 +0000
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. (I haven't checked your query in detail to see if this is actually
the case in your example, I'm just expressing a general principle.) Notice how
the projection section shows all the places where NLS-SORT = 'German' appears.
It's possible that a push_pred() hint may force Oracle to push the join
predicate into the view - but that depends on why Oracle doesn't do it
automatically.
Regards
Jonathan Lewis
________________________________________
From: Thomas Kellerer [thomas.kellerer@xxxxxxxxxx]
Sent: 10 June 2013 08:08
To: oracle-l@xxxxxxxxxxxxx
Cc: Jonathan Lewis
Subject: Re: Very unstable execution plan
Jonathan Lewis, 07.06.2013 20:18:
> Any progress ?
> I'm still curious about the german index problem.
I did re-send my answer, but apparently my email again didn't make it to the
list.
Seems there *is* some kind of size restriction on this list.
So here it is again, I uploaded all plans to my personal web-site in order to
reduce the size of the email.--
//www.freelists.org/webpage/oracle-l
Other related posts:
