Re: oracle-l Digest V19 #112

The drawback to writing the query as an existence query is that it is correlated to two other tables, so I think you'd be forcing the optimizer to do one of two things
a) join tab_part and fees before considering tsfs b) drive the whole query from tsfs driving into tab_part and fees.

As it stands the optimizer can choose to rewrite the IN as an existence subquery in exactly the way you suggest, but that's just one of the possible strategies it could choose.

In fact, it looks like that's what it did, give the unnest and semi_to_inner that appear in the outline - and given the statistics on the table that looks like it's probably the best option given how few rows in tab_part will be identified by a single tx_id.

Regards
Jonathan Lewis

On Sat, 4 Jun 2022 at 13:14, Stéphane Faroult <sfaroult_at_roughsea.com> wrote:

> 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
> FROM TSFS
> WHERE OS_ID = TAB_PART.SB_ID)
>
> I'd try something such as
>
> WHERE EXISTS (SELECT null
> FROM TSFS
> WHERE OS_ID = TAB_PART.SB_ID
> and RS_ID = fees.SB_ID)
>
> 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:
> > 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
>
>
>

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