Home -> Mailing Lists -> Oracle-L -> Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?

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

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Thu, 2 Feb 2023 15:49:46 +0100
Message-ID: <CA+S=qd3BzMEy3SWhXw=o7ZgQgJffeu_S5XVvbuN89sYkq4DWMg_at_mail.gmail.com> 








In the original subquery, the use of FPE.{something} makes the subquery
correlated - usually you would only correlate when using EXISTS, not when
using IN.



What happens because of the correlation basically seems to be, that the
subquery will select rows from F_PLAN (inner table), but *only* if FPE
(outer table) has either of two particular values in COL3.
*If* FPE.COL3 has those values, the subquery will either return 1 row
containing the ID from the outer FPE row, or zero rows if the F_PLAN table
is empty.


If on the other hand FPE.COL3 does *not *have those values, the subquery
returns zero rows.



Because of the correlation, this happens for each row in FPE - so the IN
evaluates as true if the subquery returns 1 row with the ID in question
(then it becomes FPE.ID = FPE.ID), but *not* true if the subquery returns 0
rows (then it becomes FPE.ID = NULL).



In total this is what the rewritten query emulates.
It returns those FPE rows where COL3 has the desired values, ID is not
null, and there exists at least one row in the F_PLAN table.
This does the same as the original.



Typically an IN subquery is used *not *correlated, like for example:



  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 (FPE2.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 FPE2

                WHERE FPE2.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>






Though that could perhaps better have been rewritten using analytic
functions (depending on circumstances.)



Cheerio


/Kim




Regards



Kim Berg Hansen


Oracle ACE Director



Author of Practical Oracle SQL


<https://www.apress.com/gp/book/9781484256169>


http://www.kibeha.dk


kibeha_at_kibeha.dk


_at_kibeha


<http://twitter.com/kibeha>






On Thu, Feb 2, 2023 at 3:15 PM Chris Taylor <
christopherdtaylor1994_at_gmail.com> wrote:


> _at_Dominic / _at_Sayan
>
> So my SQL skills have a strong weakness when it comes to understanding
> when/how to use EXISTS (NOT EXISTS) regularly.  Can you help me understand
> by explaining how this rewrite is equivalent when the first query uses a
> MAX function?
>
> (I hate that I have to ask but I'm having a hard time understanding how
> this works out)
>
> My lead developer mentions this:
> *"If Iā€™m not mistaken this is a table where for every single new flight
> plan (even if for the same leg) we have a new row, and the select seeks to
> get the latest.*
>
> *Both of your versions do not cater for the latest version, right?"*
>
> What's killing me is that the MAX() function in the original is
> referencing the outer table which breaks my brain.
> (I'm trying to work through it myself as well to understand)
>
> Original
>
> Rewritten
>
>   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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0> 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
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
>
>
>
> Thanks,
>
>
> Chris
>
>
> On Wed, Feb 1, 2023 at 12:00 PM Dominic Brooks <dombrooks_at_hotmail.com>
> wrote:
>
>> Oh good spot ā€“ the FPE. Alias within MAX(FPE.ID) seems so obvious
>> now.... how did I miss it šŸ˜Š
>>
>>
>>
>>
>>
>> *From: *Sayan Malakshinov <xt.and.r_at_gmail.com>
>> *Sent: *01 February 2023 16:57
>> *To: *gogala.mladen_at_gmail.com
>> *Cc: *oracle-l_at_freelists.org
>> *Subject: *Re: Sanity Check - Correlated Select Subquery SQL rewrite
>> equivalent? Or no?
>>
>>
>>
>> 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
>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605540044%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=65mR53YTMHUZdLJhWpm%2FNsMkXaFC5%2BRm8v3S5Xn%2FTJw%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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605540044%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=65mR53YTMHUZdLJhWpm%2FNsMkXaFC5%2BRm8v3S5Xn%2FTJw%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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605540044%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=65mR53YTMHUZdLJhWpm%2FNsMkXaFC5%2BRm8v3S5Xn%2FTJw%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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
>>
>>
>>
>> as
>>
>>
>>
>> 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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%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%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
>> 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
>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Ffpe.id%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=v4j5UFcyATdPbEaoauNsvCRbq3ARLaoRQSfAob4XXPY%3D&reserved=0>
>>
>>
>>
>> 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-1217
>>
>> https://dbwhisperer.wordpress.com <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdbwhisperer.wordpress.com%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=KRb7WaFr9WygzFaKIlO2adcfe2pXNgJQ3VzpVMl4Z7U%3D&reserved=0>
>>
>>
>>
>>
>> --
>>
>> Best regards,
>> Sayan Malakshinov
>>
>> Oracle performance tuning engineer
>>
>> Oracle ACE
>> http://orasql.org
>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=05%7C01%7C%7C40c1698e781f4d5ae91108db047569b1%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638108674605696751%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2FkgXwMfPbJtaD6tkKVyxrEtyoIeLiCv75AfguRa1W5Q%3D&reserved=0>
>>
>>
>>
>




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


Received on Thu Feb 02 2023 - 15:49:46 CET












Original text of this message