Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-ab.freelists.org (smtp-ab.freelists.org [34.228.148.125]) by malta2546.startdedicated.com (Postfix) with ESMTPS id BE73B100323D43 for ; Thu, 2 Feb 2023 15:51:30 +0100 (CET) Received: from turing.freelists.org (turing [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-ab.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id C0177400C8; Thu, 2 Feb 2023 14:51:29 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 95CB83FF85; Thu, 2 Feb 2023 14:51:29 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1675349489; bh=gZve337KvtM5CAPsjkrHazp2iXkmshYUZk0oHDTUS2E=; h=From:Sender:Sender:From; b=FkRvXv7JzoWOVRkgPFrTy13wSwJaArOMKlR2oy7ITc2GcfImIRrzG8kkb3Ft2sqd6 Ccs4yl4COrZt0Kxp6C18H6TwAQViGLXHTCgSaix2kj65ADjMMMxPPoHsPECteN15k5 wdurvv2J6Ipcn2x6jEBAqMLkK3hqK6CQMrPXEl4s= X-Virus-Scanned: by FreeLists at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4JHYQB_pjjl4; Thu, 2 Feb 2023 14:51:29 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 42A8B46B2B; Thu, 2 Feb 2023 14:50:43 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1675349487; bh=gZve337KvtM5CAPsjkrHazp2iXkmshYUZk0oHDTUS2E=; h=From:Sender:Sender:From; b=k9OQrA9IXmLh8dirq4/B4leZtdbeaveomaVdHkXhdsQyTGzcEZb7M6qMTxnompKMx tDA2Z/k3QtHtR60JW5LtysruZwXW5nEItOLujoTe3KKX8KfaBSIRgq4UN9n7IVdHZH tyW0//JzHKyQHKGFh73IAWE1CeFTxQNlX2N8EZbU= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Feb 2023 14:49:58 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 3F54546B23 for ; Thu, 2 Feb 2023 14:49:58 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20210112 header.b=Lteuo/GD; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id yhmtNKU4v8CN for ; Thu, 2 Feb 2023 14:49:58 +0000 (UTC) Received: from mail-oa1-f52.google.com (mail-oa1-f52.google.com [209.85.160.52]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id 09DFA3FF85 for ; Thu, 2 Feb 2023 14:49:58 +0000 (UTC) Received: by mail-oa1-f52.google.com with SMTP id 586e51a60fabf-1631b928691so2683910fac.11 for ; Thu, 02 Feb 2023 06:49:57 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=f3F1SbepgGvXOdeir05UV6OWuuMKtpGCDoVBDHU6J2I=; b=7pW3PG2HhWTaFRbk2ZdXT6eh1usXa2Ghdce0smj1fq1/hY1BhBZgheCVOD3rjz87OW u+PY4F4etWu9jY68KRBwTCS9ffijTFsuUMtNKrjd50M01j54jh+8jcyAplDxdBybe6tJ 2y9XrYtNhq7AW2VfDfXF2s0+NJUW923qNDEH+CPrnbH286H/f1VGpGrBv2tegmYIUY+x S9z+pdVcelzA7S1NQrPiBxpITACN8z5QW2szoOGAPovbQe/CWzCRxjGB8TGN4OxPpZPI bxyIYpkioVYvp3vqmo3aUvdmA8dbXCGVPt/lT1oNSaDblpKhIIW5FmVYVjPY+caEn3GK qNBw== X-Gm-Message-State: AO0yUKXmWN59D10gjmgsjyX7dMmUoo+mDLeKYY2Xy09bDDpRzDDo9g52 xaDgsmFKE7i/dr8YrUQkbu15U7UkXD5PQOLPKPI= X-Google-Smtp-Source: AK7set+Xjf5cs8qeXIjwGAXMvY8tmK/w8DjpXpY6OeVXmlkGla/aJl5RufVep+NcegMOyiJVbzek+DvKHdILesr+CXA= X-Received: by 2002:a05:6870:e0ca:b0:163:8f96:9b34 with SMTP id a10-20020a056870e0ca00b001638f969b34mr324505oab.103.1675349397216; Thu, 02 Feb 2023 06:49:57 -0800 (PST) MIME-Version: 1.0 References: <4c0f1b49-7605-7b4d-1567-b072a749081e@gmail.com> In-Reply-To: From: Kim Berg Hansen Date: Thu, 2 Feb 2023 15:49:46 +0100 Message-ID: Subject: Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no? To: christopherdtaylor1994@gmail.com Cc: dombrooks@hotmail.com, "xt.and.r@gmail.com" , "gogala.mladen@gmail.com" , "oracle-l@freelists.org" Content-Type: multipart/alternative; boundary="000000000000b2210e05f3b8ada9" X-archive-position: 83509 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: kibeha@gmail.com Precedence: normal Reply-To: kibeha@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --000000000000b2210e05f3b8ada9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable In the original subquery, the use of FPE.{something} makes the subquery correlated - usually you would only correlate when using EXISTS, not when using IN. What happens because of the correlation basically seems to be, that the subquery will select rows from F_PLAN (inner table), but *only* if FPE (outer table) has either of two particular values in COL3. *If* FPE.COL3 has those values, the subquery will either return 1 row containing the ID from the outer FPE row, or zero rows if the F_PLAN table is empty. If on the other hand FPE.COL3 does *not *have those values, the subquery returns zero rows. Because of the correlation, this happens for each row in FPE - so the IN evaluates as true if the subquery returns 1 row with the ID in question (then it becomes FPE.ID =3D FPE.ID), but *not* true if the subquery returns= 0 rows (then it becomes FPE.ID =3D NULL). In total this is what the rewritten query emulates. It returns those FPE rows where COL3 has the desired values, ID is not null, and there exists at least one row in the F_PLAN table. This does the same as the original. Typically an IN subquery is used *not *correlated, like for example: SELECT FPE.COL1, FPE.COL2, FPE.COL3, FPE.ID , FPE.COL4, FPE.VERSION FROM F_PLAN FPE WHERE (FPE.ID IN (SELECT MAX (FPE2.ID ) FROM F_PLAN FPE2 WHERE FPE2.COL3 IN ('some_guid_id_1', 'some_guid_id_2') ) ) ORDER BY FPE.ID Though that could perhaps better have been rewritten using analytic functions (depending on circumstances.) Cheerio /Kim Regards Kim Berg Hansen Oracle ACE Director Author of Practical Oracle SQL http://www.kibeha.dk kibeha@kibeha.dk @kibeha On Thu, Feb 2, 2023 at 3:15 PM Chris Taylor < christopherdtaylor1994@gmail.com> wrote: > @Dominic / @Sayan > > So my SQL skills have a strong weakness when it comes to understanding > when/how to use EXISTS (NOT EXISTS) regularly. Can you help me understan= d > by explaining how this rewrite is equivalent when the first query uses a > MAX function? > > (I hate that I have to ask but I'm having a hard time understanding how > this works out) > > My lead developer mentions this: > *"If I=E2=80=99m not mistaken this is a table where for every single new = flight > plan (even if for the same leg) we have a new row, and the select seeks t= o > get the latest.* > > *Both of your versions do not cater for the latest version, right?"* > > What's killing me is that the MAX() function in the original is > referencing the outer table which breaks my brain. > (I'm trying to work through it myself as well to understand) > > Original > > Rewritten > > SELECT FPE.COL1, > > FPE.COL2, > > FPE.COL3, > > FPE.ID > > , > > FPE.COL4, > > FPE.VERSION > > FROM F_PLAN FPE > > WHERE (FPE.ID > > IN > > (SELECT MAX (FPE.ID > > ) > > FROM F_PLAN > > WHERE FPE.COL3 IN > > ('some_guid_id_1', > > 'some_guid_id_2') > > ) > > ) > > ORDER BY FPE.ID > > > > > SELECT FPE.COL1, > FPE.COL2, > FPE.COL3, > FPE.ID > > , > FPE.COL4, > FPE.VERSION > FROM F_PLAN FPE > WHERE FPE.ID > i= s > not null > and exists (SELECT 0 FROM F_PLAN) > and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2') > ORDER BY FPE.ID > > > > > Thanks, > > > Chris > > > On Wed, Feb 1, 2023 at 12:00 PM Dominic Brooks > wrote: > >> Oh good spot =E2=80=93 the FPE. Alias within MAX(FPE.ID) seems so obviou= s >> now.... how did I miss it =F0=9F=98=8A >> >> >> >> >> >> *From: *Sayan Malakshinov >> *Sent: *01 February 2023 16:57 >> *To: *gogala.mladen@gmail.com >> *Cc: *oracle-l@freelists.org >> *Subject: *Re: Sanity Check - Correlated Select Subquery SQL rewrite >> equivalent? Or no? >> >> >> >> Since that subquery has no any columns from F_PLAN nor predicates by >> columns from F_PLAN, we can rewrite original >> >> SELECT FPE.COL1, >> FPE.COL2, >> FPE.COL3, >> FPE.ID >> >> , >> FPE.COL4, >> FPE.VERSION >> FROM F_PLAN FPE >> WHERE (FPE.ID >> >> IN >> (SELECT MAX (*FPE.ID >> * >> ) >> FROM F_PLAN >> WHERE* FPE.COL3* IN >> ('some_guid_id_1', >> 'some_guid_id_2'))) >> ORDER BY FPE.ID >> >> >> >> >> as >> >> >> >> SELECT FPE.COL1, >> FPE.COL2, >> FPE.COL3, >> FPE.ID >> >> , >> FPE.COL4, >> FPE.VERSION >> FROM F_PLAN FPE >> WHERE FPE.ID >> >> is not null >> and exists (SELECT 0 FROM F_PLAN) >> and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2') >> ORDER BY FPE.ID >> >> >> >> >> On Wed, Feb 1, 2023 at 4:42 PM Mladen Gogala >> wrote: >> >> On 2/1/23 11:19, Clay Jackson (Clay.Jackson) wrote: >> >> What Dominic said =E2=80=93 >> >> >> >> Among other things =E2=80=93 >> >> >> >> where FPE.COL3 =3D T1.COL3 >> >> >> >> is NOT part of the predicate in the original query >> >> >> >> Not only that, the 2nd query doesn't have MAX function in it, thereby >> being definitely not equivalent. I would probably try to separate the >> subquery into a WITH clause, materialize and do join. >> >> Regards >> >> -- >> >> Mladen Gogala >> >> Database Consultant >> >> Tel: (347) 321-1217 >> >> https://dbwhisperer.wordpress.com >> >> >> >> >> -- >> >> Best regards, >> Sayan Malakshinov >> >> Oracle performance tuning engineer >> >> Oracle ACE >> http://orasql.org >> >> >> >> > --000000000000b2210e05f3b8ada9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
In the original subquery, the use of FPE.{something} makes= the subquery correlated - usually you would only correlate when using EXIS= TS, not when using IN.

What happens because of the corre= lation basically seems to be, that the subquery will select rows from F_PLA= N (inner table), but only if FPE (outer table) has either of two par= ticular values in COL3.
If FPE.COL3 has those values, the = subquery will either return 1 row containing the ID from the outer FPE row,= or zero rows if the F_PLAN table is empty.
If on the other hand = FPE.COL3 does not have those values, the subquery returns zero rows.=

Because of the correlation, this happens for each= row in FPE - so the IN evaluates as true if the subquery returns 1 row wit= h the ID in question (then it becomes FPE.ID = =3D FPE.ID), but not true if the subqu= ery returns 0 rows (then it becomes FPE.ID = =3D NULL).

In total this is what the rewritten que= ry emulates.
It returns those FPE rows where COL3 has the desired= values, ID is not null, and there exists at least one row in the F_PLAN ta= ble.
This does the same as the original.

Typically an IN subquery is used not correlated, like for example:<= /div>

=C2=A0 SELECT FPE.COL1,

=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 FPE.COL2,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 FPE.COL3,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0FPE.ID,

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FPE.COL4,

=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FPE.VERSION

=C2=A0=C2=A0=C2=A0 FROM F_P= LAN FPE

=C2=A0=C2=A0 WHERE (FPE.ID=C2=A0IN

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (SELECT= =C2=A0MAX (FPE2.ID<= /a>)

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 FROM F_PLAN = FPE2

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHERE=C2=A0FPE2.COL3=C2=A0IN

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 ('some_guid_id_1',

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'some_guid= _id_2')

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 )

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 =C2=A0)

ORDER BY=C2=A0FPE.ID=



Though that could p= erhaps better have been rewritten using analytic functions (depending on ci= rcumstances.)

Cheerio
/Kim

Regards

Kim Berg Hansen
Oracle ACE Director

Author of=C2=A0= = Practical Oracle SQL
<= /div>


On Thu, Feb 2, 2023 at 3:15 PM Chris Taylor &= lt;christopherdtaylor19= 94@gmail.com> wrote:
@Dominic /=C2=A0@Sayan=C2=A0

So my SQL skills have a strong we= akness when it comes to understanding when/how to use EXISTS (NOT EXISTS) r= egularly.=C2=A0 Can you help me understand by explaining how this rewrite i= s equivalent when the first query uses a MAX function?=C2=A0=C2=A0

(= I hate that I have to ask but I'm having a hard time understanding how = this works out)

My lead developer mentions this:
=
"I= f I=E2=80=99m not mistaken this is a table where for every single new flight = plan (even if for the same leg) we have a new row, and the select seeks to get the lat= est.

Both of your versions do not cater for the latest version, right?"

<= br>What's killing me is that the MAX() function in the original is refe= rencing the outer table which breaks my brain.=C2=A0
(I'm tryin= g to work through it myself as well to understand)

Origin= al

Rewrit= ten

=C2= =A0 SELECT FPE.COL1,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FPE.COL2,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FPE.COL3,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0FPE.ID,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FPE.COL4,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FPE.VERSION

=C2= =A0=C2=A0=C2=A0 FROM F_PLAN FPE

=C2= =A0=C2=A0 WHERE (FPE.ID=C2=A0IN

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (SELECT MAX (FPE.ID)

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 FROM F_PLAN

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 WHERE FPE.COL3 IN

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ('some_guid_id_1',

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 'some_guid_id_2')

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= )

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0)

ORD= ER BY=C2=A0FPE.ID

=C2=A0=

SEL= ECT FPE.COL1,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL2,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL3,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FP= E.ID,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL4,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.VERSION
FROM F_PLAN FPE
WHERE=C2=A0FPE.ID=C2=A0is not nul= l
=C2=A0and exists (SELECT 0 FROM F_PLAN)
=C2=A0and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2')
ORDER BY=C2=A0FPE.ID=

=C2=A0

Thanks,


<= p class=3D"MsoNormal" style=3D"margin:0in;font-size:11pt;font-family:Calibr= i,sans-serif">Chris



On Wed, Feb 1, 2023 at 12:00 PM Dominic Br= ooks <dombroo= ks@hotmail.com> wrote:

Oh good spot =E2=80=93 the FPE. Alias within MAX(FPE.ID) seems so obvious now..= .. how did I miss it =F0=9F=98= =8A

=C2=A0

=C2=A0

From: Sayan Malakshinov Sent: 01 February 2023 16:57
To: gog= ala.mladen@gmail.com
Cc: orac= le-l@freelists.org
Subject: Re: Sanity Check - Correlated Select Subquery SQL rewrite e= quivalent? Or no?

=C2=A0

Since that subquery has no any columns from F_PLAN n= or predicates by columns from F_PLAN, we can rewrite original=

= =C2=A0 SELECT FPE.COL1,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL2,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL3,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.ID,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL4,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.VERSION
=C2=A0 =C2=A0 FROM F_PLAN FPE
=C2=A0 =C2=A0WHERE (FPE.ID IN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (SELECT MAX (FPE.ID)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM F_PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE FPE.COL3 IN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ('some_guid_id_1',
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0'some_guid_id_2')))
ORDER BY FPE.ID

=C2=A0

as

=C2=A0

= SELECT FPE.COL1,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL2,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL3,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.ID,=
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.COL4,
=C2=A0 =C2=A0 =C2=A0 =C2=A0FPE.VERSION
FROM F_PLAN FPE
WHERE FPE.ID is not null
=C2=A0and exists (SELECT 0 FROM F_PLAN)
=C2=A0and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2') ORDER BY FPE.ID

=C2=A0

On Wed, Feb 1, 2023 at 4:42 PM Mladen Gogala <gogala.mladen@gma= il.com> wrote:

On 2/1/23 11:19, Clay Jackson (Clay.Jackson) wrote:<= u>

What Dominic said =E2=80=93

=C2=A0

Among other things =E2=80=93

=C2=A0

where FPE.COL3 =3D T1.COL3

=C2=A0

is NOT part of the predicate in th= e original query =C2=A0

=C2=A0

Not only that, the 2nd query doesn't have MAX function in it, thereb= y being definitely not equivalent. I would probably try to separate the sub= query into a WITH clause, materialize and do join.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhi=
sperer.wordpress.com


=C2=A0

--

Best regards,
Sayan Malakshinov

Oracle performance tu= ning engineer

Oracle ACE
http://orasql.org<= /u>

=C2=A0

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