Re: Query Transformation

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Sat, 30 Jan 2021 18:52:26 +0000
Message-ID: <CABx0cSWXQ248EqpbcKARKuEGYCmGQ9kL=ihgjE63Y=CVe6B1Ag_at_mail.gmail.com>

I know I'm going to have to delve back into the 10053 trace at some point, but keep postponing that by working on test-case. Below is progress so far if anyone is interested. Continuing tomorrow
Patrick

drop table l purge;

create table l nologging
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)
select ROWNUM id,
to_char(MOD(ROWNUM, 10000), '99999999') id2 from generator g1,
generator g2
where rownum <= 400000;

alter table l add constraint l_pk primary key(id); exec dbms_stats.gather_table_stats(null, 'l');

drop table t1 purge;

create table t1 nologging
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)
select
to_char(MOD(ROWNUM, 10000000), '99999999') c1, MOD(ROWNUM, 1000000) l_id
from
generator v1,
generator v2
where
rownum <= 16000000;

CREATE INDEX t1_I ON t1 (c1, l_id);

exec dbms_stats.gather_table_stats(null, 't1');

drop table t2 purge;

create table t2 nologging
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)

select CAST(ROWNUM AS CHAR(16)) id,
to_char(MOD(ROWNUM, 1000), '9999') m1000,
to_char(MOD(ROWNUM, 2000), '9999') m2000

from
generator v1,
generator v2
where rownum <= 1000000;

alter table t2 add constraint t2_pk primary key (id); create index t2_i on t2(m1000, m2000, id); exec dbms_stats.gather_table_stats(null, 't2')

var v1 char(30)
var v2 char(30)

exec :v1 := 'A';
exec :v2 := 'A';

explain plan for SELECT /*+ OPT_PARAM('_optimizer_cbqt_or_expansion' 'off') */ null FROM l, t1, t2 WHERE

( ( t1.c1 = :v1 AND t2.m1000 BETWEEN 'X' AND 'Y') OR
( t1.c1 = :v2 AND t2.m1000 = 'Z' AND t2.m2000 = 'A' ) ) AND
( l.id = t1.l_id AND l.id2 = t2.id );

select * from dbms_xplan.display();

On Thu, 28 Jan 2021 at 15:00, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> 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




application/octet-stream attachment: tc_2021_30.sql



application/octet-stream attachment: 2021_30.out

Received on Sat Jan 30 2021 - 19:52:26 CET