Hi Charles,
An over-simplified example to demonstrate the same issue which we discussed
with Sayan:
create table t(id int, x date, y date);
I am running the queries below in both 19.4 and 19.9:
explain plan for
select *
from t driver
where x||y in (select --+ unnest
max(x||y)
from t
where id = driver.id);
select * from dbms_xplan.display();
*19.4: unnested*
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 5 (20)|
00:00:01 |
|* 1 | HASH JOIN | | 1 | 64 | 5 (20)|
00:00:01 |
| 2 | TABLE ACCESS FULL | T | 1 | 31 | 2 (0)|
00:00:01 |
| 3 | VIEW | VW_SQ_1 | 1 | 33 | 3 (34)|
00:00:01 |
| 4 | HASH GROUP BY | | 1 | 31 | 3 (34)|
00:00:01 |
| 5 | TABLE ACCESS FULL| T | 1 | 31 | 2 (0)|
00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MAX(X||Y)"=INTERNAL_FUNCTION("X")||INTERNAL_FUNCTION("Y")
AND "ITEM_1"="DRIVER"."ID")
*19.9: not unnested (the same as in your example)*
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T | 1 | 31 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 31 | | |
|* 4 | TABLE ACCESS FULL| T | 1 | 31 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("X")||INTERNAL_FUNCTION("Y")= (SELECT
/*+ UNNEST */
MAX(INTERNAL_FUNCTION("X")||INTERNAL_FUNCTION("Y")) FROM
"T" "T" WHERE "ID"=:B1))
4 - filter("ID"=:B1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$2
U - unnest / Failed basic validity checks
*10053: SU: SU bypassed: More than 1 column in connect condition.*
*Next query: unnested in 19.4, not unnested in 19.9:*
explain plan for
select *
from t driver
where *nvl(x,y)* in (select --+ unnest
max(nvl(x,y))
from t
where id = driver.id);
select * from dbms_xplan.display();
*Single column: unnested in both 19.4/19.9*
explain plan for
select *
from t driver
where *(x)* in (select --+ unnest
max(x)
from t
where id = driver.id);
select * from dbms_xplan.display();
*Multi-column IN:*
explain plan for
select *
from t driver
where *(x,y)* in (select --+ unnest
max(x), max(y)
from t
where id = driver.id);
select * from dbms_xplan.display();
Has the same plan in both - there is no SU:
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T | 1 | 31 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 31 | | |
|* 4 | TABLE ACCESS FULL| T | 1 | 31 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("X","Y")= (SELECT /*+ UNNEST */ MAX("X"),MAX("Y") FROM
"T" "T" WHERE "ID"=:B1))
4 - filter("ID"=:B1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$2
U - unnest / Failed basic validity checks
*To conclude,* when more than one column is present in the IN condition,
then SU is blocked with the following reason in 19.9:
*10053: SU: SU bypassed: More than 1 column in connect condition.*
19.4 was less restricted. Both versions block unnesting with multi-column
IN.
Best regards,
Mikhail Velikikh
On Wed, 17 Feb 2021 at 17:45, Sayan Malakshinov <xt.and.r@xxxxxxxxx> wrote:
Hi Charles,
OK, Mikhail Velikikh has found the new limitation blocking subquery
unnesting, he will write in details soon, but I just want to highlight
strange storing time in date column and so complex type conversions... I'd
try to avoid such problems in future
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org
ср, 17 февр. 2021 г., 20:41 Charles Schultz <sacrophyte@xxxxxxxxx>:
Sayan, to answer your question, both are DATE. Yes, I agree it is better
to rewrite the query; however, that is not the point of this email thread.
:)
SQL > desc notes
Name
Null? Type
-----------------------------------------------------------------------
-------- ------------------------------------------------
NOTEID
NOT NULL VARCHAR2(14 CHAR)
DATENOTE
DATE
TIMENOTE
DATE
NOTETOPIC
VARCHAR2(80 CHAR)
NOTETEXT
CLOB
USERID
VARCHAR2(14 CHAR)
ENTITYTYPEID
NUMBER(38)
ENTITYID
VARCHAR2(14 CHAR)
ENTITYSUBTYPEID
NUMBER(38)
ENTITYSUBID
VARCHAR2(14 CHAR)
NOTECATEGORY
VARCHAR2(80 CHAR)
CNVID
VARCHAR2(20 CHAR)
CNVID2
VARCHAR2(20 CHAR)
LASTMODDATE
DATE
LASTMODTIME
DATE
LASTMODUSERID
VARCHAR2(14 CHAR)
ISPRIORITYITEM
NUMBER(38)
EMAILMID
VARCHAR2(14 CHAR)
ISSUMMARYITEM
NUMBER(38)
SITEID
VARCHAR2(4 CHAR)
LFSWDDID
VARCHAR2(14 CHAR)
On Wed, Feb 17, 2021 at 11:30 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Hello,
What are datatypes of the columns LASTMODDATE and LASTMODTIME?
To be honest it's much better to rewrite this query using analytic
function dense_rank() over(...) = 1, ie without the need to scan table
twice
--
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org
ср, 17 февр. 2021 г., 19:30 Jonathan Lewis <jlewisoracle@xxxxxxxxx>:
Various possibilties.
a) Oracle Corp. has realised that there's a boundary condition with
this pattern that could produce wrong results and blocks the transformation
(e.g. are either of lastmoddate and lastmodtime declared not null - if not
the unnest ought to be invalid)
b) Oracle Corp. has modified the optimizer code to produced more
efficient plans in almost all cases, but the change introduces certain
restrictiions that your SQL now meets (e.g. even if both lastmoddate and
lastmodetime the code may now assume that to_char() or to_date() could
produce a null from a non-null.
c) Some relatively simple code change has introduced a bug
Since the report suggests the blocking of the UNNEST is for a failed
validity test I'd suspect it may be deliberate.
One quick and dirty test I'd try is to add to the subquery the predicate
where to_date(to_char(A.LASTMODDATE,'yyyymmdd') || to_char
(A.LASTMODTIME,'hh24miss'),'yyyymmddhh24miss')is not null
and see if that allows the unnest.
The other Q&D I would do is produce the 10053 trace file and look for
the final "Unparsed" query to see what transformation (if any) Oracle had
applied in case that gave you a clue. (I'd resist looking at more detail
in the 10053 for as long as possible).
Regards
Jonathan Lewis
--
Charles Schultz
