Re: Query Transformation
From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Wed, 27 Jan 2021 12:32:30 +0000
Message-ID: <CABx0cSU1g5F3wsdGyJUvb=2um_OpG86OVsqSc036ttYEbqraSQ_at_mail.gmail.com>
9 )
10 select
11 cast (trim(to_char(MOD(ROWNUM, 10000000), '099999999')) as nvarchar2(10)) RZVR01,
12 MOD(ROWNUM, 1000000) RZPYID 13 from
Plan hash value: 4208298132
(0)| 00:00:01 |
9 )
10 select
11 cast (trim(to_char(MOD(ROWNUM, 10000000), '099999999')) as varchar2(10)) RZVR01,
12 MOD(ROWNUM, 1000000) RZPYID 13 from
Plan hash value: 2909362125
(0)| 00:00:01 |
|* 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 |
Date: Wed, 27 Jan 2021 12:32:30 +0000
Message-ID: <CABx0cSU1g5F3wsdGyJUvb=2um_OpG86OVsqSc036ttYEbqraSQ_at_mail.gmail.com>
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-lReceived on Wed Jan 27 2021 - 13:32:30 CET