Re: oracle-l Digest V19 #112
Date: Sat, 4 Jun 2022 14:14:08 +0200
Message-ID: <b829bf9a-f153-29e6-67b3-c213adfa18c0_at_roughsea.com>
Hello,
Sorry for being late in this thread but I'm hardly surprised by a
correlated IN subquery leading the optimizer astray. The query is asking
for rows that contain a value that itself depends on the current row -
see the logical problem here? I have seen more than once the optimizer
choking on such a query. You have a kind of unhealthy triangular
relationship between the three tables involved.
Instead of
WHERE fees.SB_ID IN (SELECT DISTINCT RS_ID
I'd try something such as
WHERE EXISTS (SELECT null
This assumes of course an index on TSFS(OS_ID, RS_ID) (column order
irrelevant in that case).
Depending of TSFS being large or not, I might try to turn this into what
IN was designed for, an uncorrelated subquery.
HTH,
Séphane Faroult
On 31/05/2022 03:05, FreeLists Mailing List Manager wrote:
FROM TSFS
WHERE OS_ID = TAB_PART.SB_ID)
FROM TSFS
WHERE OS_ID = TAB_PART.SB_ID
and RS_ID = fees.SB_ID)
> oracle-l Digest Mon, 30 May 2022 Volume: 19 Issue: 112
>
> In This Issue:
> Nested loop cost looks too high on 19c
>
> ----------------------------------------------------------------------
>
> From: Pap <oracle.developer35_at_gmail.com>
> Date: Mon, 30 May 2022 20:41:28 +0530
> Subject: Nested loop cost looks too high on 19c
>
> Hello, While testing the behavior of queries on one of the 19.11.0.0.0
> database. Seeing the same query which running in quick time on version
> 11.2.0.4 is running longer on 19C with different path. But when forced the
> outline of the 11.2 path the cost comes higher in the 19C database , which
> i was expecting but not this much high it's 760k vs 61million . And when i
> checked the path the cost of nested loop operation is too high on 19C at
> plan_line_id-2. So wanted to understand if this is expected behavior or
> hitting any bug here?
> Below is the sql and i have added its execution path with version 11.2, 19c
> and forced 11.2 path on 19c database. Also i have added the sql monitor for
> each of those in below location.
>
> https://gist.github.com/oracle9999/69651125d9a3942e8fb261669611e7aa
>
>
> select *
>
> FROM TAB_PART TAB_PART, FEES fees
>
> WHERE fees.SB_ID IN (SELECT DISTINCT RS_ID FROM TSFS WHERE OS_ID =
> TAB_PART.SB_ID)
>
> AND fees.B_STS = 'XXX'
>
> AND TAB_PART.SM_ID = fees.OB_ID
>
> AND TAB_PART.TX_ID = :b2;
>
>
>
> ------------------------------
>
> End of oracle-l Digest V19 #112
> *******************************
>
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jun 04 2022 - 14:14:08 CEST