Re: Optimizer "enhancements" in 19.9?

Hi Charles,

OK, Mikhail Velikikh has found the new limitation blocking subquery unnesting, he will write in details soon, but I just want to highlight strange storing time in date column and so complex type conversions... I'd try to avoid such problems in future

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org

ср, 17 февр. 2021 г., 20:41 Charles Schultz <sacrophyte_at_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