Re: Optimizer "enhancements" in 19.9?

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 17 Feb 2021 11:41:02 -0600
Message-ID: <CAPZQniUJP0q_dp8+1EeJ=jAp1844MSCwww0uEe4rmGtya6E-xw_at_mail.gmail.com>



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


On Wed, Feb 17, 2021 at 11:30 AM Sayan Malakshinov <xt.and.r_at_gmail.com> 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_at_gmail.com>:
>
>>
>> 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
Received on Wed Feb 17 2021 - 18:41:02 CET

Original text of this message