Re: Optimizer "enhancements" in 19.9?
Date: Wed, 17 Feb 2021 16:30:27 +0000
Message-ID: <CAGtsp8=VQcPDTCDpA4aemdDdNF4mSAgivuzGS5ZdLjMUVHxumg_at_mail.gmail.com>
Various possibilties.
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
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
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)
(A.LASTMODTIME,'hh24miss'),'yyyymmddhh24miss')is not null
Jonathan Lewis
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 17 2021 - 17:30:27 CET