Re: Replace Not Exist with Left Outer Join and Is Null

Hi Amit,

I don't want to speculate, but few years ago when I tried second one, it was producing different traces (don't remember exactly why: were that because of different catched bind variables or modified session parameters or only for dml... Doesn't matter..maybe it works better now... ), so I just think that first one is more reliable.

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

On Wed, Jun 2, 2021, 17:29 Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Jonathan and Sayan,
>
> The UNNEST hint didn't work but the query is completed within 30 secs
> post-re-wrote to outer join.
>
> Also, could you help me understand, what is the difference between the
> 10053 traces manually enabled on the query and retrieving from the
> DBMS_SQLDIAG .DUMP_TRACE API?
>
>
> Best Regards,
> Amit S
>
>
> On Thu, May 27, 2021 at 5:05 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Before trying a rewrite - or going though the painful process of trying
>> to interpret a CBO trace file, you could pursue three ideas:
>> a) Does Oracle consider subquery unnesting to be illegal ... try adding
>> an /*+ unnest */ hint to the subquery itself to see if it is possible (even
>> it produces a very bad plan as a result)
>> b) Does Oracle think the subquery will happen only a very small number of
>> times - if so address address the cause of of the bad cardinality estimate
>> before chasing the subquery error
>> c) Has Oracle run the subquery at the earliest possible moment or the
>> latest possible moment - if there are multiple subqueries have they been
>> run in the wrong order: would it help to change the timing of when the
>> subquery runs.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply_at_freelists.org>
>> wrote:
>>
>>> Hi,
>>>
>>> In one of the queries TKProof shows, most time is spent in accessing a
>>> table thousands of times inside NOT EXIST condition.
>>>
>>> I am looking for your inputs if it's a promising idea to replace NOT
>>> EXIST with the LEFT OUTER JOIN and IS NULL condition?
>>>
>>> Any inputs in this regard are appreciated.
>>>
>>>
>>> Regards,
>>> Amit S
>>>
>>>
>>>

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