Re: Optimizer "enhancements" in 19.9?
Date: Wed, 17 Feb 2021 20:29:44 +0300
Message-ID: <CAOVevU6x2tjNtJ9j8b8deaUzYiT+2muzOziMRH18Q_n11jWfZQ_at_mail.gmail.com>
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_at_gmail.com>:
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 17 2021 - 18:29:44 CET
>
> 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
>
>
>
>
>
>
>
>