Re: Query Transformation

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_at_gmail.com> 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_at_gmail.com>
> 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_at_gmail.com>
>> 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.
>>>
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l