You are correct in that it has nothing to do with NCHAR/NVARCHAR2 -
replacing those in testcase still results in the 'crazy' plan
On Thu, 28 Jan 2021 at 14:50, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:
My bad, you need to stick the following lines at the beginning:
var nc1 nchar(30)
var nc2 nchar(30)
exec :nc1 := 'A';
exec :nc2 := 'A';
On Thu, 28 Jan 2021 at 14:41, Patrick Jolliffe <jolliffe@xxxxxxxxx> wrote:
Thanks Jonathan,
(Hope I have ) Managed to reproduce it from scratch on a pristine 19.3
environment ('standard' Oracle docker image in this case).
Not hugely pleased about the SET_TABLE_STATS step, but we do what we can.
SQL for testcase and my output attached.
Going to keep on digging, just glad to have it confirmed that the plan is
indeed crazy and it's not (only) my ability to understand it lacking.
Best Regards
Patrick
On Thu, 28 Jan 2021 at 14:09, Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
I should have said that in my model both the varchar2() and nvarchar2()
plans produced exactly the same 4-branch concatenation.
The indications were then that the generation of the branch code is
purely formulaic and "unthinking" because in my case two of the branches
had an access predicate of
access("T1"."NV1"=U'0050')
followed in one case by the silly filter predicate of
filter(("T1"."NV1"<=U'0200' AND "T1"."NV1">=U'0100')
and in the other case by the even sillier filter predicate:
filter(("T1"."NV1"<=U'0200' AND "T1"."NV1">=U'0100') AND
LNNVL("T1"."NV1"=U'0050')))
Really the optimizer should have spotted the contradiction and filtered
out these two branches
This was all running 19.3.
Regards
Jonathan Lewis
On Wed, 27 Jan 2021 at 12:32, Patrick Jolliffe <jolliffe@xxxxxxxxx>
wrote:
I *think* it's related to national characterset (NCHAR/NVARCHAR2). As
shown below (I hope) current testcase does not reproduce if I switch to
CHAR/VARCHAR2.
Investigation continues, trying to build complete reproducable testcase
from scratch.
