Re: Query Transformation

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




text/plain attachment: crazy_result.txt



application/octet-stream attachment: crazy_plan.sql