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

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Wed, 1 Feb 2023 16:55:46 +0000
Message-ID: <CAOVevU5qtFDQEn-vR=u23qXbyhtCfNVVS=gsE7fzBvZ8CHsQ-g_at_mail.gmail.com>

Since that subquery has no any columns from F_PLAN nor predicates by columns from F_PLAN, we can rewrite original

  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 <http://FPE.ID>*)
                 FROM F_PLAN
                WHERE* FPE.COL3* IN
                          ('some_guid_id_1',
                           'some_guid_id_2')))

ORDER BY FPE.ID as

SELECT FPE.COL1,
       FPE.COL2,
       FPE.COL3,
       FPE.ID,
       FPE.COL4,
       FPE.VERSION

FROM F_PLAN FPE
WHERE FPE.ID is not null
 and exists (SELECT 0 FROM F_PLAN)
 and FPE.COL3 IN ('some_guid_id_1','some_guid_id_2') ORDER BY FPE.ID On Wed, Feb 1, 2023 at 4:42 PM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 2/1/23 11:19, Clay Jackson (Clay.Jackson) wrote:
>
> What Dominic said –
>
>
>
> Among other things –
>
>
>
> where FPE.COL3 = T1.COL3
>
>
>
> is NOT part of the predicate in the original query
>
>
>
> Not only that, the 2nd query doesn't have MAX function in it, thereby
> being definitely not equivalent. I would probably try to separate the
> subquery into a WITH clause, materialize and do join.
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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

Received on Wed Feb 01 2023 - 17:55:46 CET