Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 1 Feb 2023 12:58:27 -0500
Message-ID: <CAP79kiQJXEpSf-p==LXUCVod7nw_=0vkopYtr5vvnSUbPUsqdA_at_mail.gmail.com>



That's what my gut was saying also - but having a devil of a time turning it into an equivalent version to get rid of the correlated subquery.

Thanks,
Chris

On Wed, Feb 1, 2023 at 11:11 AM Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> No. Not equivalent. But they might appear to be depending on what data you
> run it with.
>
> There might be constraints etc which mean they return the same results
> under your circumstances. But semantically they are not equivalent.
>
>
>
>
>
> *From: *Chris Taylor <christopherdtaylor1994_at_gmail.com>
> *Sent: *01 February 2023 15:40
> *To: *oracle-l_at_freelists.org
> *Subject: *Sanity Check - Correlated Select Subquery SQL rewrite
> equivalent? Or no?
>
>
>
> Hey guys, just need a quick sanity check here.
>
> Are these queries equivalent? My gut says not exactly but maybe. The
> results returned are identical in the test cases I ran, but I fear I'm
> missing something.
>
> Original SQL (27 seconds to return 26 rows)
> Modified Version - Same rows but 336 ms
>
>
>
> Original
>
> Modified
>
> SELECT FPE.COL1,
>
> FPE.COL2,
>
> FPE.COL3,
>
> FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
> ,
>
> FPE.COL4,
>
> FPE.VERSION
>
> FROM F_PLAN FPE
>
> WHERE (FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
> IN
>
> (SELECT MAX (FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
> )
>
> FROM F_PLAN
>
> WHERE FPE.COL3 IN
>
> ('some_guid_id_1',
>
> 'some_guid_id_2')))
>
> ORDER BY FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>
> SELECT FPE.COL1,
>
> FPE.COL2,
>
> FPE.COL3,
>
> FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
> ,
>
> FPE.COL4,
>
> FPE.VERSION
>
> FROM F_PLAN FPE,
>
> (SELECT COL3, ID
>
> FROM F_PLAN
>
> WHERE COL3 IN
>
> ('some_guid_id_1',
>
> 'some_guid_id_2')
>
> ) t1
>
> where FPE.COL3 = T1.COL3
>
> and FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
> = T1.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft1.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=HQamwon1ueUsJNjPKU2%2FZiWw0CQtDQZQk7xOBvvGKjs%3D&reserved=0>
>
> ORDER BY FPE.ID
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C0b18ac90f2044325546f08db046a9261%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108628037188702%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=cWQlSnsYk3NZCzd3Ab3Mq9ibclbXkAcHfL0OT85d6Io%3D&reserved=0>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 01 2023 - 18:58:27 CET

Original text of this message