just out of curiosity can you ask him which one he believes will run faster
and why :)
În mie., 1 feb. 2023 la 21:27, kyle Hailey <kylelf@xxxxxxxxx> a scris:
chat GPT says:
what is the difference between the following two SQL queries?
It is important to note that the first query will only return one row, as
it is retrieving the row with the maximum ID value, while the second
query will return multiple rows if there are multiple rows in the F_PLAN
table with the same COL3 value in the list ('some_guid_id_1',
'some_guid_id_2').
Both queries will return the same data, as long as the data in the F_PLAN
table does not change between the two queries. The difference lies in the
way the data is filtered and retrieved from the F_PLAN table.
Query 1 uses a subquery to get the maximum ID of the F_PLAN table where
the COL3 value is in the list of values ('some_guid_id_1',
'some_guid_id_2'). The main query then retrieves all columns of the F_PLAN
table where the ID is equal to the maximum ID from the subquery.
Query 2 uses a derived table t1 to get the COL3 and ID of the F_PLAN
table where the COL3 value is in the list of values ('some_guid_id_1',
'some_guid_id_2'). The main query then joins this derived table with the
F_PLAN table on the COL3 and ID columns.
In both cases, the result set will contain the columns COL1, COL2, COL3,
ID, COL4, and VERSION from the F_PLAN table, filtered by the COL3 values
in the list ('some_guid_id_1', 'some_guid_id_2'). The resulting data will
be ordered by ID.
On Wed, Feb 1, 2023 at 1:00 PM Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
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@xxxxxxxxxxx>
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@xxxxxxxxx>
*Sent: *01 February 2023 15:40
*To: *oracle-l@xxxxxxxxxxxxx
*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>
