Re: Query Transformation

  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_optimizer_cbqt_or_expansion' 'off')
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$9E43CB6E")
      MERGE(_at_"SEL$58A6D7F6" >"SEL$3")
      OUTLINE_LEAF(_at_"SEL$9E43CB6E_1")
      USE_CONCAT(_at_"SEL$9E43CB6E" 8 OR_PREDICATES(0 36))
      OUTLINE_LEAF(_at_"SEL$9E43CB6E_2")
      OUTLINE_LEAF(_at_"SEL$9E43CB6E_3")
      OUTLINE(_at_"SEL$3")
      OUTLINE(_at_"SEL$58A6D7F6")
      MERGE(_at_"SEL$1" >"SEL$2")
      OUTLINE(_at_"SEL$2")
      OUTLINE(_at_"SEL$1")
      INDEX(_at_"SEL$9E43CB6E_1" "T2"_at_"SEL$2" ("T2"."T2F1" "T2"."T2F2"
              "T2"."ID2"))
      INDEX(_at_"SEL$9E43CB6E_1" "T1"_at_"SEL$1" ("T1"."T1F" "T1"."ID1"))
      INDEX(_at_"SEL$9E43CB6E_1" "L"_at_"SEL$1" ("L"."ID1"))
      INDEX(_at_"SEL$9E43CB6E_2" "T1"_at_"SEL$9E43CB6E_2" ("T1"."T1F" "T1"."ID1"))
      INDEX_RS_ASC(_at_"SEL$9E43CB6E_2" "L"_at_"SEL$9E43CB6E_2" ("L"."ID1"))
      INDEX(_at_"SEL$9E43CB6E_2" "T2"_at_"SEL$9E43CB6E_2" ("T2"."T2F1" "T2"."T2F2"
              "T2"."ID2"))
      INDEX(_at_"SEL$9E43CB6E_3" "T1"_at_"SEL$9E43CB6E_3" ("T1"."T1F" "T1"."ID1"))
      INDEX_RS_ASC(_at_"SEL$9E43CB6E_3" "L"_at_"SEL$9E43CB6E_3" ("L"."ID1"))
      INDEX(_at_"SEL$9E43CB6E_3" "T2"_at_"SEL$9E43CB6E_3" ("T2"."ID2"))
      LEADING(_at_"SEL$9E43CB6E_1" "T2"_at_"SEL$2" "T1"@"SEL$1" "L"@"SEL$1")
      LEADING(_at_"SEL$9E43CB6E_2" "T1"_at_"SEL$9E43CB6E_2" "L"@"SEL$9E43CB6E_2"
              "T2"_at_"SEL$9E43CB6E_2")
      LEADING(_at_"SEL$9E43CB6E_3" "T1"_at_"SEL$9E43CB6E_3" "L"@"SEL$9E43CB6E_3"
              "T2"_at_"SEL$9E43CB6E_3")
      USE_NL(_at_"SEL$9E43CB6E_1" "T1"_at_"SEL$1")
      USE_NL(_at_"SEL$9E43CB6E_1" "L"_at_"SEL$1")
      NLJ_BATCHING(_at_"SEL$9E43CB6E_1" "L"_at_"SEL$1")
      USE_NL(_at_"SEL$9E43CB6E_2" "L"_at_"SEL$9E43CB6E_2")
      USE_NL(_at_"SEL$9E43CB6E_2" "T2"_at_"SEL$9E43CB6E_2")
      USE_NL(_at_"SEL$9E43CB6E_3" "L"_at_"SEL$9E43CB6E_3")
      USE_NL(_at_"SEL$9E43CB6E_3" "T2"_at_"SEL$9E43CB6E_3")
      NLJ_BATCHING(_at_"SEL$9E43CB6E_3" "T2"_at_"SEL$9E43CB6E_3")
      END_OUTLINE_DATA

On Tue, 16 Feb 2021 at 14:40, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> What does your outline say?
> (display_cursor(...,format=>'BASIC +OUTLINE'));
>
>
> Am 16.02.2021 um 15:14 schrieb Patrick Jolliffe:
>
> Been looking at this on and off for the last few weeks. Below is my
> simplest testcase, it reproduces on my plain 19c Docker Image.
> Getting a bit bogged down in the exact species of OR expansion it is
> making and why.
> I'm going to keep looking, but thought I'd share my (small) progress.
> Patrick
>
>
> drop table l purge;
> create table l nologging
> as
> select rownum id1,
> rownum id2
> from dual
> connect by level <= 10;
> alter table l add constraint l_id1 primary key(id1);
> exec dbms_stats.gather_table_stats(null, 'l');
> drop table t1 purge;
> create table t1 nologging
> as
> select ROWNUM id1,
> MOD(ROWNUM, 5) t1f
> from dual connect by rownum <= 10;
> create index t1_t1f_id1 ON t1 (t1f, id1);
> exec dbms_stats.gather_table_stats(null, 't1');
> drop table t2 purge;
> create table t2 nologging
> as
> select ROWNUM id2,
> MOD(ROWNUM, 8) t2f1,
> MOD(ROWNUM, 8) t2f2
> from dual
> connect by level <= 10000;
> alter table t2 add constraint t2_id2 primary key (id2);
> create index t2_t2f1_t2f2_id2 on t2(t2f1, t2f2, id2);
> exec dbms_stats.gather_table_stats(null, 't2')
> explain plan for
> select /*+ OPT_PARAM('_optimizer_cbqt_or_expansion' 'off') */ null
> from t1
> join l on l.id1 = t1.id1
> join t2 on t2.id2 = l.id2
> where ( ( t1.t1f = 0 AND t2.t2f1 BETWEEN 9 AND 10) OR
> ( t1.t1f = 1 AND t2.t2f1 = 14 AND t2.t2f2 = 13 ) );
> select * from dbms_xplan.display();
>
>
> ----------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> | Cost (%CPU)| Time |
>
> ----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 3 | 66
> | 10 (0)| 00:00:01 |
> | 1 | CONCATENATION | | |
> | | |
> | 2 | NESTED LOOPS | | 1 | 22
> | 4 (0)| 00:00:01 |
> | 3 | NESTED LOOPS | | 1 | 22
> | 4 (0)| 00:00:01 |
> | 4 | NESTED LOOPS | | 1 | 16
> | 3 (0)| 00:00:01 |
> |* 5 | INDEX RANGE SCAN | T2_T2F1_T2F2_ID2 | 1 | 10
> | 2 (0)| 00:00:01 |
> | 6 | INLIST ITERATOR | | |
> | | |
> |* 7 | INDEX RANGE SCAN | T1_T1F_ID1 | 1 | 6
> | 1 (0)| 00:00:01 |
> |* 8 | INDEX UNIQUE SCAN | L_ID1 | 1 |
> | 0 (0)| 00:00:01 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | L | 1 | 6
> | 1 (0)| 00:00:01 |
> | 10 | NESTED LOOPS | | 1 | 22
> | 3 (0)| 00:00:01 |
> | 11 | NESTED LOOPS | | 1 | 12
> | 2 (0)| 00:00:01 |
> |* 12 | INDEX RANGE SCAN | T1_T1F_ID1 | 1 | 6
> | 1 (0)| 00:00:01 |
> | 13 | TABLE ACCESS BY INDEX ROWID | L | 1 | 6
> | 1 (0)| 00:00:01 |
> |* 14 | INDEX UNIQUE SCAN | L_ID1 | 1 |
> | 0 (0)| 00:00:01 |
> |* 15 | INDEX RANGE SCAN | T2_T2F1_T2F2_ID2 | 1 | 10
> | 1 (0)| 00:00:01 |
> | 16 | NESTED LOOPS | | 1 | 22
> | 3 (0)| 00:00:01 |
> | 17 | NESTED LOOPS | | 1 | 22
> | 3 (0)| 00:00:01 |
> | 18 | NESTED LOOPS | | 1 | 12
> | 2 (0)| 00:00:01 |
> |* 19 | INDEX RANGE SCAN | T1_T1F_ID1 | 1 | 6
> | 1 (0)| 00:00:01 |
> | 20 | TABLE ACCESS BY INDEX ROWID| L | 1 | 6
> | 1 (0)| 00:00:01 |
> |* 21 | INDEX UNIQUE SCAN | L_ID1 | 1 |
> | 0 (0)| 00:00:01 |
> |* 22 | INDEX UNIQUE SCAN | T2_ID2 | 1 |
> | 0 (0)| 00:00:01 |
> |* 23 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 10
> | 1 (0)| 00:00:01 |
>
> ----------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 5 - access("T2"."T2F1"=14 AND "T2"."T2F2"=13)
> 7 - access("T1"."T1F"=0 OR "T1"."T1F"=1)
> filter("T1"."T1F"=0 AND "T2"."T2F1">=9 AND "T2"."T2F1"<=10 OR
> "T1"."T1F"=1 AND
> "T2"."T2F1"=14 AND "T2"."T2F2"=13)
> 8 - access("L"."ID1"="T1"."ID1")
> 9 - filter("T2"."ID2"="L"."ID2")
> 12 - access("T1"."T1F"=1)
> filter("T1"."T1F"=0 OR "T1"."T1F"=1)
> 14 - access("L"."ID1"="T1"."ID1")
> 15 - access("T2"."T2F1"=14 AND "T2"."T2F2"=13 AND "T2"."ID2"="L"."ID2")
> filter("T2"."T2F1">=9 AND "T2"."T2F1"<=10 AND
> (LNNVL("T2"."T2F1"=14) OR
> LNNVL("T2"."T2F2"=13)))
> 19 - access("T1"."T1F"=0)
> filter("T1"."T1F"=0 OR "T1"."T1F"=1)
> 21 - access("L"."ID1"="T1"."ID1")
> 22 - access("T2"."ID2"="L"."ID2")
> 23 - filter("T2"."T2F1">=9 AND "T2"."T2F1">=9 AND "T2"."T2F1"<=10 AND
> "T2"."T2F1"<=10 AND
> (LNNVL("T1"."T1F"=1) OR LNNVL("T2"."T2F1"=14) OR
> LNNVL("T2"."T2F2"=13)) AND
> (LNNVL("T2"."T2F1"=14) OR LNNVL("T2"."T2F2"=13)))
>
> On Sat, 30 Jan 2021 at 18:52, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:
>
>> 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