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

---

• ------------------------------------------------ NOTEID NOT NULL VARCHAR2(14 CHAR) DATENOTE DATE TIMENOTE DATE NOTETOPIC VARCHAR2(80 CHAR) NOTETEXT CLOB USERID VARCHAR2(14 CHAR) ENTITYTYPEID NUMBER(38) ENTITYID VARCHAR2(14 CHAR) ENTITYSUBTYPEID NUMBER(38) ENTITYSUBID VARCHAR2(14 CHAR) NOTECATEGORY VARCHAR2(80 CHAR) CNVID VARCHAR2(20 CHAR) CNVID2 VARCHAR2(20 CHAR) LASTMODDATE 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-l

Received on Wed Feb 17 2021 - 18:41:02 CET