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.
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
ORDER BY FPE.ID
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 01 2023 - 16:38:12 CET