Re: Query Transformation

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