Re: Query Transformation

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Thu, 28 Jan 2021 15:00:42 +0000
Message-ID: <CABx0cSVaPSwMBdtPWT5z958jkBPoO2ME6bC5iq_iv34rOmPKdA_at_mail.gmail.com>



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_at_gmail.com> 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_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
Received on Thu Jan 28 2021 - 16:00:42 CET

Original text of this message