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

From: Clay Jackson <"Clay>
Date: Wed, 1 Feb 2023 16:19:42 +0000
Message-ID: <CO1PR19MB4984C370FF84533362A0C4459BD19_at_CO1PR19MB4984.namprd19.prod.outlook.com>

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://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%3D&reserved=0>,
         FPE.COL4,
         FPE.VERSION

    FROM F_PLAN FPE
   WHERE (FPE.ID<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%3D&reserved=0> IN

              (SELECT MAX (FPE.ID<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%3D&reserved=0>)
                 FROM F_PLAN
                WHERE FPE.COL3 IN


                          ('some_guid_id_1',


                           'some_guid_id_2')))
ORDER BY FPE.ID<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%3D&reserved=0>
  SELECT FPE.COL1,
         FPE.COL2,
         FPE.COL3,
         FPE.ID<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%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://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%3D&reserved=0> = T1.ID<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft1.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=MHvygFl7lR0ZgzexLbxIZV%2BjkGe6a1kS%2F3VUi3XPjQM%3D&reserved=0> ORDER BY FPE.ID<https://nam12.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7Cclay.jackson%40quest.com%7C1d7057fbe13047c24faa08db046f078b%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C638108647154732203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2B5Xw4kcgoEu9eMp5QtWD9%2BMO9kc1eWYmo9L1nBTi8rI%3D&reserved=0>

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

Received on Wed Feb 01 2023 - 17:19:42 CET