Re: Optimizer "enhancements" in 19.9?
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
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_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-lReceived on Wed Feb 17 2021 - 18:41:02 CET