RE: Very unstable execution plan
Date: Mon, 10 Jun 2013 09:52:56 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90D51C3_at_exmbx06.thus.corp>
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_at_mgm-tp.com] Sent: 10 June 2013 08:08
To: oracle-l_at_freelists.org
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.-- http://www.freelists.org/webpage/oracle-l Received on Mon Jun 10 2013 - 11:52:56 CEST