Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 24904100315748 for ; Thu, 2 Feb 2023 15:15:45 +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-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id B35B24013B; Thu, 2 Feb 2023 14:15:43 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 844343FF53; Thu, 2 Feb 2023 14:15:43 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1675347343; bh=itxiAWxubjtmCYAoe4/2HP1CpUAPm5608cw9y2v/W1c=; h=From:Sender:Sender:From; b=BTY698VNthxN2CN5mIAawN+FcSDudlpT8ry/ZzUvI59f4GbHh465mZe03uP3dr9/z CD9VmDhgkJzzZ4zIA7aPYLws/rnn2w8k63QnWxKwo7hk14H8YFlT09EcRuZxwXfYpJ 6qqEO+Aofy5mPUOAsZC7dVMT8p10Hs9cC7BEDz1Y= 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 pwGCueOUMBX0; Thu, 2 Feb 2023 14:15:43 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 8413E48D3F; Thu, 2 Feb 2023 14:14:57 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1675347341; bh=itxiAWxubjtmCYAoe4/2HP1CpUAPm5608cw9y2v/W1c=; h=From:Sender:Sender:From; b=IbpZThhRAGWEoEB5HWr1wGBINlULdWlkr0JvOiMfXeZVE8dHWHRnpUKrAZRLmokQ/ dg+xZNzvxcrs/tVN0kDUORmHHO+wpfskhUwAO0jnBQm4IlSrZae6CB1cHR9x+Kq+sm OizExmbsDjQHTa90TEi4q33k3Xvmez4GOGOnYLWI= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Feb 2023 14:14:12 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 7E30A3FEF9 for ; Thu, 2 Feb 2023 14:14:12 +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=aYWFfq8l; 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 jnwEeBOZjxsT for ; Thu, 2 Feb 2023 14:14:12 +0000 (UTC) Received: from mail-ed1-f50.google.com (mail-ed1-f50.google.com [209.85.208.50]) (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 38FCD3FEF8 for ; Thu, 2 Feb 2023 14:14:12 +0000 (UTC) Received: by mail-ed1-f50.google.com with SMTP id eq11so2126429edb.6 for ; Thu, 02 Feb 2023 06:14:12 -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=0CVwNejNnwBTmh6xCu69HAwml9m0Zm7zrmuT9UXtnfA=; b=rGDJluGxXWTQ8EmdH5k/2tE7gmrd3D+Up9BdAEXhf/vO0mEAdN2F/1SlPmC5r4NuD5 cMAbjRZra7rqxq60YEdgT4z9/yDdK8ujRd5y4PI699AZHt3QvdN2tViTSDPFHquu5ZQ9 DqvagSQaH2jcwwhK6s9KQko3WlLtoT5mlujAYYjqnENsEDpo5pfjaUNf/hjfyC8OvEft wanaFdBCcmhuW88kprA3SgkO5RnpztdjcvmKTauvOKbDLxq+uRxcYK3uozLn8jyNcNa0 kEQj3v1ezyWYF0CAlA6NHT0Yi9fV/LuXMmtvlX/M3XpzGQ9xnt+Od/wD+LA6iZiVBf9M 7Zvg== X-Gm-Message-State: AO0yUKU4HgZtN+CD+clKZraAt6C1V9sTnpuIZCei3ZSoyOSsNaVJuwK5 dM2Cu/sgQ+vNyJIonFE1yorKsgcTfraeq9nlYYM= X-Google-Smtp-Source: AK7set9BUPIMSY6QBgwzSN3FjzUOfPyQepsNaLbeMQfz4DTyEfjP8mV2ScpX3LkaxfjFdy16xrUl0Up5YlHdBu0czAg= X-Received: by 2002:a05:6402:22ee:b0:4a2:1d19:ca14 with SMTP id dn14-20020a05640222ee00b004a21d19ca14mr1991678edb.68.1675347250976; Thu, 02 Feb 2023 06:14:10 -0800 (PST) MIME-Version: 1.0 References: <4c0f1b49-7605-7b4d-1567-b072a749081e@gmail.com> In-Reply-To: From: Chris Taylor Date: Thu, 2 Feb 2023 09:13:59 -0500 Message-ID: Subject: Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no? To: dombrooks@hotmail.com Cc: "xt.and.r@gmail.com" , "gogala.mladen@gmail.com" , "oracle-l@freelists.org" Content-Type: multipart/alternative; boundary="000000000000c51c0705f3b82d26" X-archive-position: 83508 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: christopherdtaylor1994@gmail.com Precedence: normal Reply-To: christopherdtaylor1994@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 --000000000000c51c0705f3b82d26 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable @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 understand 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 fl= ight plan (even if for the same leg) we have a new row, and the select seeks to 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 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 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 obvious= 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 > > > > --000000000000c51c0705f3b82d26 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
"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 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

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