Re: Optimizer "enhancements" in 19.9?

Jonathon, as always, thanks for jumping in and commenting. :)

As to your three cases, yes, it is probably one of those; while it would be nice to know (just to satisfy that itch in my head), it is not really paramount in regards to fixing our current problem (which is now more of an academic question at this point).

To follow up on your last two suggestions, adding the "is not null" predicate did not elicit an unnest operation (unfortunately). Additionally, the only transformation I could find via the 10053 trace was that the "IN" was properly replaced with "=" - I think all the other pieces are exactly the same (other than the usual practice of all object names being formerly prefixed with object owners). Resisting the urge to dig for more detail in the 10053 is a challenge. :)

On Wed, Feb 17, 2021 at 10:31 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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

--
http://www.freelists.org/webpage/oracle-l