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

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 1 Feb 2023 10:38:12 -0500
Message-ID: <CAP79kiTvBreRt2j0=nBR-eU2GJwQeA4b2cHwhfto8HtWdB1hpA_at_mail.gmail.com>



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,          FPE.COL4,          FPE.VERSION     FROM F_PLAN FPE    WHERE (FPE.ID IN               (SELECT MAX (FPE.ID)

                 FROM F_PLAN

                WHERE FPE.COL3 IN


('some_guid_id_1',
'some_guid_id_2')))

ORDER BY FPE.ID   SELECT FPE.COL1,          FPE.COL2,          FPE.COL3,          FPE.ID,          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 = T1.ID

ORDER BY FPE.ID

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 01 2023 - 16:38:12 CET

Original text of this message