Jonathan,
Could you please elaborate on "c"? If a query contains multiple subqueries,
are you saying the position of each one in the text of the query may influence
the execution plan?
Thanks,Matt
On Thursday, May 27, 2021, 05:05:55 AM EDT, Jonathan Lewis
<jlewisoracle@xxxxxxxxx> 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 errorc) 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.
RegardsJonathan Lewis
On Wed, 26 May 2021 at 15:29, Amit Saroha <dmarc-noreply@xxxxxxxxxxxxx> 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