RE: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?
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?
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
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
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>
Subject: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?
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-lReceived on Wed Feb 01 2023 - 17:32:30 CET