Re: Query Transformation

I think the nvarhar() thing is a bit of a red herring - it may simply be changing some costs very slightly because of variations in the arithmetic dependent on assumed byte lengths when comparing nvarchar2() with varchar2().
I've run up a model to try and work this out, and taken a close look at the two plans you produced but don't have a definite answer yet, however the 3-part vs. 4 part plan boils down to the following - you have a compound predicate of the form:

((A and B) or (C and D))

Expanding this in three steps this can also be expressed as

((A and B) or C) and ((A and B) or D)

((A or C) and (B or c)) and ((A or D) and (B or D))

(A or C) and (B or c) and (A or D) and (B or D)

And that 4-part expansion is clearly visible in the varchar() plan.

Toon Koopelaars would like this, by the way, because it's in conjunctive normal form (or possibly disjunctive normal form, I always forget which is which) i.e. it's a conjunct of simple disjuncts.

Having the potential for 4 branches of a concatenation, Oracle has then implemented two of the branches in the same way in both plans. But the other two have been handled very differently.

In the varchar2() plan operations 2 to 9 show two parts of the expansion - but they're wrapped into an IN-LIST ITERATOR that does the same thing twice.

In the nvarchar2() plan operations 2 to 9 show one of the disjuncts and operations 10 to 15 show the other as two obviously separate pieces.

As I say, it's probably just a small difference in costs due to differences in bytes.
A possible check of this - you've cast your columns to varchar2(10) and nvarchar2(10), which is implicitly character. If you cast your varchar2() example to use varcahr2(40 byte) - or whatever the ratio is between your two character sets - you may find that the varchar2() plan switches to the nvarchar2() plan.

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.
>
>
> SQL> drop table c1 purge;
>
>
>
> Table C1 dropped.
>
> SQL> create table c1 nologging
> 2 as
> 3 with generator as (
> 4 select --+ materialize
> 5 rownum id
> 6 from dual
> 7 connect by
> 8 level <= 1e4
> 9 )
> 10 select
> 11 cast (trim(to_char(MOD(ROWNUM, 10000000), '099999999')) as
> nvarchar2(10)) RZVR01,
> 12 MOD(ROWNUM, 1000000) RZPYID
> 13 from
> 14 generator v1,
> 15 generator v2
> 16 where
> 17 rownum <= 17000000;
>
>
>
> Table C1 created.
>
> SQL>
> SQL> CREATE INDEX c1_I ON c1 (RZVR01, RZPYID);
>
>
>
> Index C1_I created.
>
> SQL> exec dbms_stats.gather_table_stats(null, 'c1');
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL>
> SQL> var c1 nvarchar2(10)
>
>
> SQL> var c2 nvarchar2(10)
>
>
> SQL>
> SQL> exec :c1 := 'A';
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL> exec :c2 := 'A';
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> explain plan for SELECT /*+ OPT_PARAM('_optimizer_cbqt_or_expansion'
> 'off') x */ null FROM P2, C1, C2 WHERE
> 2 ( ( C1.RZVR01 = :c1 AND C2.GMOBJ BETWEEN '2201' AND '2299') OR
> 3 ( C1.RZVR01 = :c2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' )
> ) AND
> 4 ( P2.RYPYID = C1.RZPYID AND P2.RYGLBA = C2.GMAID );
>
>
>
> Explained.
>
> SQL>
> SQL> select * from dbms_xplan.display();
>
>
>
> PLAN_TABLE_OUTPUT
>
> ____________________________________________________________________________________________
> Plan hash value: 4208298132
>
>
> -----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> -----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 3 | 279 | 37
> (0)| 00:00:01 |
> | 1 | CONCATENATION | | | |
> | |
> | 2 | NESTED LOOPS | | 1 | 93 | 19
> (0)| 00:00:01 |
> | 3 | NESTED LOOPS | | 1 | 93 | 19
> (0)| 00:00:01 |
> | 4 | NESTED LOOPS | | 1 | 71 | 17
> (0)| 00:00:01 |
> | 5 | INLIST ITERATOR | | | |
> | |
> |* 6 | INDEX RANGE SCAN | C1_I | 3 | 72 | 5
> (0)| 00:00:01 |
> |* 7 | INDEX RANGE SCAN | C21I | 1 | 47 | 4
> (0)| 00:00:01 |
> |* 8 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> | 10 | NESTED LOOPS | | 1 | 93 | 9
> (0)| 00:00:01 |
> | 11 | NESTED LOOPS | | 1 | 46 | 6
> (0)| 00:00:01 |
> |* 12 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4
> (0)| 00:00:01 |
> | 13 | TABLE ACCESS BY INDEX ROWID | P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> |* 14 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
> |* 15 | INDEX RANGE SCAN | C21I | 1 | 47 | 3
> (0)| 00:00:01 |
> | 16 | NESTED LOOPS | | 1 | 93 | 9
> (0)| 00:00:01 |
> | 17 | NESTED LOOPS | | 1 | 93 | 9
> (0)| 00:00:01 |
> | 18 | NESTED LOOPS | | 1 | 46 | 6
> (0)| 00:00:01 |
> |* 19 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4
> (0)| 00:00:01 |
> | 20 | TABLE ACCESS BY INDEX ROWID| P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> |* 21 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
> |* 22 | INDEX UNIQUE SCAN | C2_PK | 1 | | 2
> (0)| 00:00:01 |
> |* 23 | TABLE ACCESS BY INDEX ROWID | C2 | 1 | 47 | 3
> (0)| 00:00:01 |
>
> -----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>
> PLAN_TABLE_OUTPUT
>
> _________________________________________________________________________________________________
>
> 6 - access("C1"."RZVR01"=SYS_OP_C2C(:C1) OR
> "C1"."RZVR01"=SYS_OP_C2C(:C2))
> 7 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
> filter("C1"."RZVR01"=SYS_OP_C2C(:C1) AND "C2"."GMOBJ">=U'2201' AND
> "C2"."GMOBJ"<=U'2299' OR "C1"."RZVR01"=SYS_OP_C2C(:C2) AND
> "C2"."GMOBJ"=U'2019'
> AND "C2"."GMSUB"=U'IDT')
> 8 - access("P2"."RYPYID"="C1"."RZPYID")
> 9 - filter("P2"."RYGLBA"="C2"."GMAID")
> 12 - access("C1"."RZVR01"=SYS_OP_C2C(:C2))
> filter("C1"."RZVR01"=SYS_OP_C2C(:C1) OR
> "C1"."RZVR01"=SYS_OP_C2C(:C2))
> 14 - access("P2"."RYPYID"="C1"."RZPYID")
> 15 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT' AND
> "P2"."RYGLBA"="C2"."GMAID")
> filter("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ">=U'2201' AND
> (LNNVL("C2"."GMSUB"=U'IDT') OR LNNVL("C2"."GMOBJ"=U'2019')))
> 19 - access("C1"."RZVR01"=SYS_OP_C2C(:C1))
> filter("C1"."RZVR01"=SYS_OP_C2C(:C1) OR
> "C1"."RZVR01"=SYS_OP_C2C(:C2))
> 21 - access("P2"."RYPYID"="C1"."RZPYID")
> 22 - access("P2"."RYGLBA"="C2"."GMAID")
> 23 - filter("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ"<=U'2299' AND
> "C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ">=U'2201' AND
> (LNNVL("C1"."RZVR01"=SYS_OP_C2C(:C2)) OR
> LNNVL("C2"."GMOBJ"=U'2019') OR
> LNNVL("C2"."GMSUB"=U'IDT')) AND (LNNVL("C2"."GMSUB"=U'IDT')
> OR
> LNNVL("C2"."GMOBJ"=U'2019')))
>
> Hint Report (identified by operation id / Query Block Name / Object Alias):
> Total hints for statement: 1 (E - Syntax error (1))
> ---------------------------------------------------------------------------
>
> 1 - SEL$1
> E - x
>
>
> 64 rows selected.
>
> SQL>
> SQL>
> SQL> drop table c1 purge;
>
>
>
> Table C1 dropped.
>
> SQL> create table c1 nologging
> 2 as
> 3 with generator as (
> 4 select --+ materialize
> 5 rownum id
> 6 from dual
> 7 connect by
> 8 level <= 1e4
> 9 )
> 10 select
> 11 cast (trim(to_char(MOD(ROWNUM, 10000000), '099999999')) as
> varchar2(10)) RZVR01,
> 12 MOD(ROWNUM, 1000000) RZPYID
> 13 from
> 14 generator v1,
> 15 generator v2
> 16 where
> 17 rownum <= 17000000;
>
>
>
> Table C1 created.
>
> SQL>
> SQL> CREATE INDEX c1_I ON c1 (RZVR01, RZPYID);
>
>
>
> Index C1_I created.
>
> SQL> exec dbms_stats.gather_table_stats(null, 'c1');
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL>
> SQL> var c1 varchar2(10)
>
>
> SQL> var c2 varchar2(10)
>
>
> SQL>
> SQL> exec :c1 := 'A';
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL> exec :c2 := 'A';
>
>
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> explain plan for SELECT /*+ OPT_PARAM('_optimizer_cbqt_or_expansion'
> 'off') x */ null FROM P2, C1, C2 WHERE
> 2 ( ( C1.RZVR01 = :c1 AND C2.GMOBJ BETWEEN '2201' AND '2299') OR
> 3 ( C1.RZVR01 = :c2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' )
> ) AND
> 4 ( P2.RYPYID = C1.RZPYID AND P2.RYGLBA = C2.GMAID );
>
>
>
> Explained.
>
> SQL>
> SQL> select * from dbms_xplan.display();
>
>
>
> PLAN_TABLE_OUTPUT
>
> ____________________________________________________________________________________________
> Plan hash value: 2909362125
>
>
> -----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> -----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 4 | 336 | 33
> (0)| 00:00:01 |
> | 1 | CONCATENATION | | | |
> | |
> | 2 | NESTED LOOPS | | 1 | 84 | 9
> (0)| 00:00:01 |
> | 3 | NESTED LOOPS | | 1 | 84 | 9
> (0)| 00:00:01 |
> | 4 | MERGE JOIN CARTESIAN | | 1 | 62 | 7
> (0)| 00:00:01 |
> |* 5 | INDEX RANGE SCAN | C21I | 1 | 47 | 5
> (0)| 00:00:01 |
> | 6 | BUFFER SORT | | 1 | 15 | 2
> (0)| 00:00:01 |
> |* 7 | INDEX RANGE SCAN | C1_I | 1 | 15 | 2
> (0)| 00:00:01 |
> |* 8 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
> |* 9 | TABLE ACCESS BY INDEX ROWID | P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> | 10 | NESTED LOOPS | | 1 | 84 | 8
> (0)| 00:00:01 |
> | 11 | NESTED LOOPS | | 1 | 37 | 5
> (0)| 00:00:01 |
> |* 12 | INDEX RANGE SCAN | C1_I | 1 | 15 | 3
> (0)| 00:00:01 |
> | 13 | TABLE ACCESS BY INDEX ROWID | P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> |* 14 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
> |* 15 | INDEX RANGE SCAN | C21I | 1 | 47 | 3
> (0)| 00:00:01 |
> | 16 | NESTED LOOPS | | 1 | 84 | 8
> (0)| 00:00:01 |
> | 17 | NESTED LOOPS | | 1 | 37 | 5
> (0)| 00:00:01 |
> |* 18 | INDEX RANGE SCAN | C1_I | 1 | 15 | 3
> (0)| 00:00:01 |
> | 19 | TABLE ACCESS BY INDEX ROWID | P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> |* 20 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
> |* 21 | INDEX RANGE SCAN | C21I | 1 | 47 | 3
> (0)| 00:00:01 |
> | 22 | NESTED LOOPS | | 1 | 84 | 8
> (0)| 00:00:01 |
> | 23 | NESTED LOOPS | | 1 | 84 | 8
> (0)| 00:00:01 |
> | 24 | NESTED LOOPS | | 1 | 37 | 5
> (0)| 00:00:01 |
> |* 25 | INDEX RANGE SCAN | C1_I | 1 | 15 | 3
> (0)| 00:00:01 |
> | 26 | TABLE ACCESS BY INDEX ROWID| P2 | 1 | 22 | 2
> (0)| 00:00:01 |
> |* 27 | INDEX UNIQUE SCAN | P2_PK | 1 | | 1
> (0)| 00:00:01 |
>
>
> PLAN_TABLE_OUTPUT
>
> ____________________________________________________________________________________________
> |* 28 | INDEX UNIQUE SCAN | C2_PK | 1 | | 2
> (0)| 00:00:01 |
> |* 29 | TABLE ACCESS BY INDEX ROWID | C2 | 1 | 47 | 3
> (0)| 00:00:01 |
>
> -----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 5 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
> filter("C2"."GMSUB"=U'IDT' AND "C2"."GMOBJ"=U'2019')
> 7 - access("C1"."RZVR01"=:C2)
> filter("C1"."RZVR01"=:C1 OR "C1"."RZVR01"=:C2)
> 8 - access("P2"."RYPYID"="C1"."RZPYID")
> 9 - filter("P2"."RYGLBA"="C2"."GMAID")
> 12 - access("C1"."RZVR01"=:C1)
> filter("C1"."RZVR01"=:C1 OR "C1"."RZVR01"=:C2)
> 14 - access("P2"."RYPYID"="C1"."RZPYID")
> 15 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT' AND
> "P2"."RYGLBA"="C2"."GMAID")
> filter("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ">=U'2201' AND
> (LNNVL("C1"."RZVR01"=:C2) OR LNNVL("C2"."GMOBJ"=U'2019') OR
> LNNVL("C2"."GMSUB"=U'IDT')))
> 18 - access("C1"."RZVR01"=:C2)
> filter("C1"."RZVR01"=:C1 OR "C1"."RZVR01"=:C2)
> 20 - access("P2"."RYPYID"="C1"."RZPYID")
> 21 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT' AND
> "P2"."RYGLBA"="C2"."GMAID")
> filter("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ">=U'2201' AND
> (LNNVL("C2"."GMSUB"=U'IDT') OR LNNVL("C2"."GMOBJ"=U'2019')))
> 25 - access("C1"."RZVR01"=:C1)
> filter("C1"."RZVR01"=:C1 OR "C1"."RZVR01"=:C2)
> 27 - access("P2"."RYPYID"="C1"."RZPYID")
> 28 - access("P2"."RYGLBA"="C2"."GMAID")
> 29 - filter("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ"<=U'2299' AND
>
>
> PLAN_TABLE_OUTPUT
>
> __________________________________________________________________________________________________
> "C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ">=U'2201' AND
> (LNNVL("C1"."RZVR01"=:C2) OR
> LNNVL("C2"."GMOBJ"=U'2019') OR LNNVL("C2"."GMSUB"=U'IDT'))
> AND
> (LNNVL("C2"."GMSUB"=U'IDT') OR LNNVL("C2"."GMOBJ"=U'2019')))
>
> Hint Report (identified by operation id / Query Block Name / Object Alias):
> Total hints for statement: 1 (E - Syntax error (1))
> ---------------------------------------------------------------------------
>
> 1 - SEL$1
> E - x
>
>
> 76 rows selected.
>
> SQL>
>
> On Wed, 27 Jan 2021 at 09:20, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:
>
>> No need to apologize, appreciate the effort. I'm spending the time to
>> reproduce and build a test-case on my lab environment.
>> Did anybody (JL?) say that the hardest part of the problem is building
>> the simplest possible test-case, once you've done that the rest is plain
>> sailing. (If not I'll claim it).
>> More motivated now I've got confirmation that the plan is crazy, I was
>> thinking it was my brain that was at fault for not understanding it.
>> I'll update if I get anywhere
>> Thanks
>> Patrick
>>
>>
>> On Tue, 26 Jan 2021 at 22:26, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>
>>> so sorry. since you already have the 10053, that tells you how the CBO
>>> got the plan.
>>>
>>>
>>>
>>> I thought you wanted a useful plan. Reducing the pieces of the Cartesian
>>> product inputs is an odds on favorite to do that, but if it is already
>>> plenty fast for you, never mind.
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Patrick Jolliffe
>>> *Sent:* Tuesday, January 26, 2021 3:40 AM
>>> *To:* oracle-l
>>> *Cc:* Mark W. Farnham
>>> *Subject:* Re: Query Transformation
>>>
>>>
>>>
>>> Thanks Mark, but as detailed, concern is not about optimizing SQL which
>>> I have already done, just understanding how and why the optimizer came up
>>> with the plan for the unchanged SQL.
>>>
>>>
>>>
>>> On Mon, 25 Jan 2021 at 17:31, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>>>
>>> First: Just made it so I could read it
>>>
>>>
>>>
>>> SELECT /*+gather_plan_statistics Y */ null FROM P, C1, C2
>>>
>>> WHERE
>>>
>>> (
>>>
>>> (C1.RZVR01 = :nc1 AND C2.GMOBJ BETWEEN '2201' AND '2299')
>>>
>>> OR (C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT')
>>>
>>> )
>>>
>>> AND P.RYPYID = C1.RZPYID
>>>
>>> AND P.RYGLBA = C2.GMAID
>>>
>>>
>>>
>>> Second: Made your claimed correction to a single bind with :nc1 = :nc2,
>>> by the way presuming they are not allowed to be null, since
>>>
>>> they appear in an equals.
>>>
>>>
>>>
>>> SELECT /*+gather_plan_statistics Y */ null FROM P, C1, C2
>>>
>>> WHERE
>>>
>>> (
>>>
>>> (C1.RZVR01 = :nc1 AND C2.GMOBJ BETWEEN '2201' AND '2299')
>>>
>>> OR (C1.RZVR01 = :nc1 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT')
>>>
>>> )
>>>
>>> AND P.RYPYID = C1.RZPYID
>>>
>>> AND P.RYGLBA = C2.GMAID
>>>
>>>
>>>
>>> Third: reformed with C1 and C2 pruned prior to the join.
>>>
>>>
>>>
>>> with c1_pruned as (select c1.rzpyid from c1 where c1.rzvr01 = :nc1)
>>>
>>> ,c2_pruned as (select c2.gmaid from c2
>>>
>>> where c2.gmobj between '2201' and '2299'
>>>
>>> or (c2.gmobj = '2019' and c2.gmsub = 'IDT')
>>>
>>> )
>>>
>>> select /* */ null from p, c1_pruned, c2_pruned
>>>
>>> where p.rypyid = c1_pruned.rzpyid
>>>
>>> and p.ryglba = c2_pruned.gmaid
>>>
>>>
>>>
>>> Now, IF the single table predicate selectivity is good (meaning highly
>>> selective) on c1 and/or c2, then they should appear as relatively
>>>
>>> smaller objects for their respective joins with p. IF nc1 and nc2 must
>>> be identical, notice that these single table prunings don't
>>>
>>> involve a join at all. You possibly need no merge hints on the with
>>> clause objects in case Oracle evaluates the cost is better pruning
>>>
>>> on the join clauses. IF nc1 and nc2 actually can be different, then you
>>> can factor out c1_pruned_nc1 and c1_pruned_nc2 and produced with results
>>> for each half of the or on c2.
>>>
>>>
>>>
>>> Good luck.
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Patrick Jolliffe
>>> *Sent:* Monday, January 25, 2021 4:40 AM
>>> *To:* oracle-l
>>> *Subject:* Query Transformation
>>>
>>>
>>>
>>> Dear List,
>>>
>>> On Oracle 19c been looking at a poorly performing plan for a little
>>> while now. I've simplified it somewhat as per the details below.
>>>
>>> I've resolved the problem itself by
>>>
>>> 1) creating an extended statistic for column group GMOBJ, GMSUB (which
>>> were correlated)
>>>
>>> 2) getting developers to replace the 2 binds :nc1 and :nc2 with a single
>>> bind, as the values are actually the same although the optimizer doesn't
>>> know this, so this limits it's options.
>>>
>>> However I've got myself bogged down in trying to understand the
>>> transformation that has taken place, really for intellectual curiosity.
>>>
>>> I think it's some variation on "cost based or expansion", but how has it
>>> transformed into a union of 3 different parts?
>>>
>>> Any help greatly appreciated
>>>
>>> Patrick
>>>
>>>
>>>
>>> SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS
>>> LAST'));
>>>
>>>
>>> PLAN_TABLE_OUTPUT
>>>
>>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>> SQL_ID 5f67d104uv8ht, child number 0
>>> -------------------------------------
>>> SELECT /*+gather_plan_statistics Y */ null FROM P, C1, C2 WHERE ( (
>>> C1.RZVR01 = :nc1 AND C2.GMOBJ BETWEEN '2201' AND '2299') OR (
>>> C1.RZVR01 = :nc2 AND C2.GMOBJ = '2019' AND C2.GMSUB = 'IDT' ) ) AND
>>> ( P.RYPYID = C1.RZPYID AND P.RYGLBA = C2.GMAID )
>>>
>>> Plan hash value: 1470685083
>>>
>>>
>>> ---------------------------------------------------------------------------------------------------
>>> | Id | Operation | Name | Starts | E-Rows |
>>> A-Rows | A-Time | Buffers |
>>>
>>> ---------------------------------------------------------------------------------------------------
>>> | 0 | SELECT STATEMENT | | 1 | |
>>> 0 |00:00:00.18 | 89916 |
>>> | 1 | CONCATENATION | | 1 | |
>>> 0 |00:00:00.18 | 89916 |
>>> | 2 | NESTED LOOPS | | 1 | 1 |
>>> 0 |00:00:00.18 | 89886 |
>>> | 3 | NESTED LOOPS | | 1 | 1 |
>>> 89115 |00:00:00.13 | 771 |
>>> | 4 | NESTED LOOPS | | 1 | 1 |
>>> 89115 |00:00:00.06 | 745 |
>>> | 5 | INLIST ITERATOR | | 1 | |
>>> 3 |00:00:00.01 | 8 |
>>> |* 6 | INDEX RANGE SCAN | C1_I | 2 | 3 |
>>> 3 |00:00:00.01 | 8 |
>>> |* 7 | INDEX RANGE SCAN | C21I | 3 | 1 |
>>> 89115 |00:00:00.05 | 737 |
>>> |* 8 | INDEX UNIQUE SCAN | P_PK | 89115 | 1 |
>>> 89115 |00:00:00.04 | 26 |
>>> |* 9 | TABLE ACCESS BY INDEX ROWID | P | 89115 | 1 |
>>> 0 |00:00:00.05 | 89115 |
>>> | 10 | NESTED LOOPS | | 1 | 1 |
>>> 0 |00:00:00.01 | 26 |
>>> | 11 | NESTED LOOPS | | 1 | 1 |
>>> 3 |00:00:00.01 | 15 |
>>> |* 12 | INDEX RANGE SCAN | C1_I | 1 | 1 |
>>> 3 |00:00:00.01 | 4 |
>>> | 13 | TABLE ACCESS BY INDEX ROWID | P | 3 | 1 |
>>> 3 |00:00:00.01 | 11 |
>>> |* 14 | INDEX UNIQUE SCAN | P_PK | 3 | 1 |
>>> 3 |00:00:00.01 | 8 |
>>> |* 15 | INDEX RANGE SCAN | C21I | 3 | 1 |
>>> 0 |00:00:00.01 | 11 |
>>> | 16 | NESTED LOOPS | | 1 | 1 |
>>> 0 |00:00:00.01 | 4 |
>>> | 17 | NESTED LOOPS | | 1 | 1 |
>>> 0 |00:00:00.01 | 4 |
>>> | 18 | NESTED LOOPS | | 1 | 1 |
>>> 0 |00:00:00.01 | 4 |
>>> |* 19 | INDEX RANGE SCAN | C1_I | 1 | 1 |
>>> 0 |00:00:00.01 | 4 |
>>> | 20 | TABLE ACCESS BY INDEX ROWID| P | 0 | 1 |
>>> 0 |00:00:00.01 | 0 |
>>> |* 21 | INDEX UNIQUE SCAN | P_PK | 0 | 1 |
>>> 0 |00:00:00.01 | 0 |
>>> |* 22 | INDEX UNIQUE SCAN | C2_PK | 0 | 1 |
>>> 0 |00:00:00.01 | 0 |
>>> |* 23 | TABLE ACCESS BY INDEX ROWID | C2 | 0 | 1 |
>>> 0 |00:00:00.01 | 0 |
>>>
>>> ---------------------------------------------------------------------------------------------------
>>>
>>> Predicate Information (identified by operation id):
>>> ---------------------------------------------------
>>>
>>> 6 - access(("C1"."RZVR01"=:NC1 OR "C1"."RZVR01"=:NC2))
>>> 7 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
>>> filter((("C1"."RZVR01"=:NC1 AND "C2"."GMOBJ">=U'2201' AND
>>> "C2"."GMOBJ"<=U'2299') OR
>>> ("C1"."RZVR01"=:NC2 AND "C2"."GMOBJ"=U'2019' AND
>>> "C2"."GMSUB"=U'IDT')))
>>> 8 - access("P"."RYPYID"="C1"."RZPYID")
>>> 9 - filter("P"."RYGLBA"="C2"."GMAID")
>>> 12 - access("C1"."RZVR01"=:NC2)
>>> filter(("C1"."RZVR01"=:NC1 OR "C1"."RZVR01"=:NC2))
>>> 14 - access("P"."RYPYID"="C1"."RZPYID")
>>> 15 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT' AND
>>> "P"."RYGLBA"="C2"."GMAID")
>>> filter(("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ">=U'2201' AND
>>> (LNNVL("C2"."GMSUB"=U'IDT') OR
>>> LNNVL("C2"."GMOBJ"=U'2019'))))
>>> 19 - access("C1"."RZVR01"=:NC1)
>>> filter(("C1"."RZVR01"=:NC1 OR "C1"."RZVR01"=:NC2))
>>> 21 - access("P"."RYPYID"="C1"."RZPYID")
>>> 22 - access("P"."RYGLBA"="C2"."GMAID")
>>> 23 - filter(("C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ"<=U'2299' AND
>>> "C2"."GMOBJ">=U'2201'
>>> AND "C2"."GMOBJ">=U'2201' AND (LNNVL("C1"."RZVR01"=:NC2)
>>> OR LNNVL("C2"."GMOBJ"=U'2019') OR
>>> LNNVL("C2"."GMSUB"=U'IDT')) AND
>>> (LNNVL("C2"."GMSUB"=U'IDT') OR
>>> LNNVL("C2"."GMOBJ"=U'2019'))))
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l