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

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Wed, 1 Feb 2023 16:32:30 +0000
Message-ID: <DBAPR02MB6470E52AF854014C5ABD46FEA1D19_at_DBAPR02MB6470.eurprd02.prod.outlook.com>

The original is horrible 😊

And things which are written... “unnaturally” ... have the best chance of underperforming.

I’m pretty sure, although it’s making me doubt myself, that the original is actually the equivalent of:
  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)
     AND FPE.COL3 IN ('some_guid_id_1',
                      'some_guid_id_2'))

ORDER BY FPE.ID because if the condition on FPE.COL3 is not met then the subquery should not run (ideally – but plan should tell you) and even if it was run then the subquery would return null and eliminate that row.

If that optimisation is not happening that might be why it’s slow.

And if that rewrite is correct, hopefully you can see why it’s not the same as the modified version.

From: Clay Jackson (cjackson)<mailto:Clay.Jackson_at_quest.com> Sent: 01 February 2023 16:19
To: dombrooks_at_hotmail.com<mailto:dombrooks_at_hotmail.com>; christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>; oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?

What Dominic said –

Among other things –

where FPE.COL3 = T1.COL3

is NOT part of the predicate in the original query

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Dominic Brooks Sent: Wednesday, February 1, 2023 8:11 AM To: christopherdtaylor1994_at_gmail.com; oracle-l_at_freelists.org Subject: RE: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?

CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.

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<mailto:christopherdtaylor1994_at_gmail.com> Sent: 01 February 2023 15:40
To: oracle-l_at_freelists.org<mailto: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%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%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%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%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%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%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%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%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%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%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%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%3D&reserved=0> = T1.ID<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft1.id%2F&data=05%7C01%7C%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=zHEqtNBHCuEjyo6zE0TtsTFxsGiSl869j%2B82Uq4iJv8%3D&reserved=0> ORDER BY FPE.ID<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C5192dc5ede08412b3cb608db04702124%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108651867073801%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=0mOVdSGYo2hq%2FV1fw%2BQ17X39HO%2BHofmcbxngaKMqa84%3D&reserved=0>

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

Received on Wed Feb 01 2023 - 17:32:30 CET

This message: [ Message body ]
Next message: Mladen Gogala: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Previous message: Clay Jackson: "RE: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
In reply to: Clay Jackson: "RE: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Next in thread: Mladen Gogala: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Reply: Mladen Gogala: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Reply: Chris Taylor: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message