Re: Query Transformation
- From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 28 Jan 2021 14:41:53 +0000
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@xxxxxxxxx> 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@xxxxxxxxx> 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 p purge;
Table P dropped.
SQL>
SQL> create table p 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 ROWNUM rypyid,
11 cast (trim(to_char(MOD(ROWNUM, 10000), '099999999')) as
nvarchar2(10)) ryglba from
12 generator v1,
13 generator v2
14 where rownum <= 400000;
Table P created.
SQL>
SQL> alter table p add constraint p_pk primary key(rypyid);
Table P altered.
SQL> exec dbms_stats.gather_table_stats(null, 'p');
PL/SQL procedure successfully completed.
SQL>
SQL> drop table c1 purge;
Table C1 dropped.
SQL>
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 rpad('X', 255, 'X') padding
14 from
15 generator v1,
16 generator v2
17 where
18 rownum <= 16000000;
Table C1 created.
SQL>
SQL> CREATE INDEX c1_I ON c1 (RZVR01, RZPYID);
Index C1_I created.
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'c1');
PL/SQL procedure successfully completed.
SQL>
SQL> drop table c2 purge;
Table C2 dropped.
SQL>
SQL> create table c2 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 CAST(ROWNUM AS NCHAR(16)) gmaid,
11 cast (trim(to_char(MOD(ROWNUM, 1038), '099999999')) as nchar(12))
gmobj,
12 cast (trim(to_char(MOD(ROWNUM, 1441), '099999999')) as nchar(16))
gmsub,
13 rpad('X', 34, 'X') PADDING from
14 generator v1,
15 generator v2
16 where rownum <= 1000000;
Table C2 created.
SQL>
SQL> alter table c2 add constraint c2_pk primary key (gmaid);
Table C2 altered.
SQL> create index c2_i on c2(gmobj, gmsub, gmaid);
Index C2_I created.
SQL> exec dbms_stats.gather_table_stats(null, 'c2')
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> explain plan for SELECT /*+ OPT_PARAM('_optimizer_cbqt_or_expansion'
'off') P1 */ null FROM P, 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 ( P.rypyid = C1.RZPYID AND P.RYGLBA = C2.GMAID );
Explained.
SQL>
SQL> select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
____________________________________________________________________________________________
Plan hash value: 2640408289
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 417 | 23 (0)|
00:00:01 |
| 1 | CONCATENATION | | | | |
|
| 2 | NESTED LOOPS | | 1 | 139 | 9 (0)|
00:00:01 |
| 3 | NESTED LOOPS | | 1 | 139 | 9 (0)|
00:00:01 |
| 4 | NESTED LOOPS | | 1 | 115 | 7 (0)|
00:00:01 |
|* 5 | INDEX RANGE SCAN | C2_I | 1 | 91 | 3 (0)|
00:00:01 |
| 6 | INLIST ITERATOR | | | | |
|
|* 7 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4 (0)|
00:00:01 |
|* 8 | INDEX UNIQUE SCAN | P_PK | 1 | | 1 (0)|
00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | P | 1 | 24 | 2 (0)|
00:00:01 |
| 10 | NESTED LOOPS | | 1 | 139 | 7 (0)|
00:00:01 |
| 11 | NESTED LOOPS | | 1 | 139 | 7 (0)|
00:00:01 |
| 12 | NESTED LOOPS | | 1 | 48 | 6 (0)|
00:00:01 |
|* 13 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4 (0)|
00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID| P | 1 | 24 | 2 (0)|
00:00:01 |
|* 15 | INDEX UNIQUE SCAN | P_PK | 1 | | 1 (0)|
00:00:01 |
|* 16 | INDEX UNIQUE SCAN | C2_PK | 1 | | 1 (0)|
00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | C2 | 1 | 91 | 1 (0)|
00:00:01 |
| 18 | NESTED LOOPS | | 1 | 139 | 7 (0)|
00:00:01 |
| 19 | NESTED LOOPS | | 1 | 139 | 7 (0)|
00:00:01 |
| 20 | NESTED LOOPS | | 1 | 48 | 6 (0)|
00:00:01 |
|* 21 | INDEX RANGE SCAN | C1_I | 1 | 24 | 4 (0)|
00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID| P | 1 | 24 | 2 (0)|
00:00:01 |
|* 23 | INDEX UNIQUE SCAN | P_PK | 1 | | 1 (0)|
00:00:01 |
|* 24 | INDEX UNIQUE SCAN | C2_PK | 1 | | 1 (0)|
00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID | C2 | 1 | 91 | 1 (0)|
00:00:01 |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C2"."GMOBJ"=U'2019' AND "C2"."GMSUB"=U'IDT')
7 - access("C1"."RZVR01"=SYS_OP_C2C(:C1) OR "C1"."RZVR01"=SYS_OP_C2C(:C2))
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("P"."RYPYID"="C1"."RZPYID")
9 - filter("P"."RYGLBA"="C2"."GMAID")
13 - access("C1"."RZVR01"=SYS_OP_C2C(:C2))
filter("C1"."RZVR01"=SYS_OP_C2C(:C1) OR "C1"."RZVR01"=SYS_OP_C2C(:C2))
15 - access("P"."RYPYID"="C1"."RZPYID")
16 - access("P"."RYGLBA"="C2"."GMAID")
17 - filter("C2"."GMSUB"=U'IDT' AND "C2"."GMOBJ"=U'2019' AND
"C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ"<=U'2299' AND
(LNNVL("C2"."GMSUB"=U'IDT')
OR LNNVL("C2"."GMOBJ"=U'2019')))
21 - access("C1"."RZVR01"=SYS_OP_C2C(:C1))
filter("C1"."RZVR01"=SYS_OP_C2C(:C1) OR "C1"."RZVR01"=SYS_OP_C2C(:C2))
23 - access("P"."RYPYID"="C1"."RZPYID")
24 - access("P"."RYGLBA"="C2"."GMAID")
25 - filter("C2"."GMOBJ">=U'2201' AND "C2"."GMOBJ">=U'2201' AND
"C2"."GMOBJ"<=U'2299' AND "C2"."GMOBJ"<=U'2299' 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 - P1
66 rows selected.
Attachment:
crazy_plan.sql
Description: Binary data
Other related posts:
