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

From: kyle Hailey <kylelf_at_gmail.com>
Date: Sat, 4 Feb 2023 00:21:52 -0500
Message-ID: <CADsdiQi_tynhHDzYZxxu2UPQ56TsJB99S64R85gfjPqVs1gnKQ_at_mail.gmail.com>

Nice

truncate table F_PLAN;
insert into F_PLAN values (29,5009);
insert into F_PLAN values (29,5009);

mysql> SELECT FPE.ID,

-> FPE.GUID

-> FROM F_PLAN FPE

-> WHERE (FPE.ID IN

-> (SELECT MAX(FPE.ID)

-> FROM F_PLAN

-> WHERE FPE.GUID

-> IN (5009, 5008)));

+------+------+

| ID | GUID | +------+------+

| 29 | 5009 |

+------+------+

2 rows in set (0.43 sec)

mysql> SELECT FPE.ID,

-> FPE.GUID

-> FROM F_PLAN

-> FPE,

-> (SELECT GUID, ID

-> FROM F_PLAN

-> WHERE GUID IN(5009,5008)

-> ) T1

-> where FPE.GUID = T1.GUID

-> and FPE.ID = T1.ID;

+------+------+

| ID | GUID | +------+------+

| 29 | 5009 |

+------+------+

4 rows in set (0.02 sec)

On Sat, Feb 4, 2023 at 12:15 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Kyle,
>
> If id is not unique, the second query can return more rows. For example,
> truncate your table and insert just these 2 rows:
> insert into F_PLAN values (29,5009);
> insert into F_PLAN values (29,5009);
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE
> http://orasql.org
>
> On Sat, 4 Feb 2023, 05:04 kyle Hailey, <kylelf_at_gmail.com> wrote:
>
>>
>> Looks to return the same data.
>>
>> mysql> SELECT FPE.ID,
>>
>>     ->        FPE.GUID
>>
>>     -> FROM F_PLAN FPE
>>
>>     -> WHERE (FPE.ID IN
>>
>>     ->          (SELECT MAX(FPE.ID)
>>
>>     ->           FROM F_PLAN
>>
>>     ->           WHERE FPE.GUID
>>
>>     ->              IN (5009, 5008)));
>>
>> +------+------+
>>
>> | ID   | GUID |
>>
>> +------+------+
>>
>> |    8 | 5008 |
>>
>> |   29 | 5009 |
>>
>> |   38 | 5008 |
>>
>> |   59 | 5009 |
>>
>> |   68 | 5008 |
>>
>> |   89 | 5009 |
>>
>> |   98 | 5008 |
>>
>> +------+------+
>>
>> 7 rows in set (0.40 sec)
>>
>>
>> mysql> SELECT FPE.ID,
>>
>>     ->        FPE.GUID
>>
>>     ->  FROM F_PLAN
>>
>>     ->     FPE,
>>
>>     ->         (SELECT GUID, ID
>>
>>     ->          FROM F_PLAN
>>
>>     ->           WHERE GUID IN(5009,5008)
>>
>>     ->      ) T1
>>
>>     ->  where FPE.GUID = T1.GUID
>>
>>     ->    and FPE.ID = T1.ID;
>>
>> +------+------+
>>
>> | ID   | GUID |
>>
>> +------+------+
>>
>> |    8 | 5008 |
>>
>> |   29 | 5009 |
>>
>> |   38 | 5008 |
>>
>> |   59 | 5009 |
>>
>> |   68 | 5008 |
>>
>> |   89 | 5009 |
>>
>> |   98 | 5008 |
>>
>> +------+------+
>>
>> 7 rows in set (0.31 sec)
>>
>>
>> Create table F_PLAN(
>> id bigint,
>> Guid bigint)
>> ;
>>  insert into F_PLAN values (17,5007);
>>  insert into F_PLAN values (20,5000);
>>  insert into F_PLAN values (29,5009);
>>  insert into F_PLAN values (38,5008);
>>  insert into F_PLAN values (47,5007);
>>  insert into F_PLAN values (50,5000);
>>  insert into F_PLAN values (59,5009);
>>  insert into F_PLAN values (68,5008);
>>  insert into F_PLAN values (77,5007);
>>  insert into F_PLAN values (80,5000);
>>  insert into F_PLAN values (89,5009);
>>  insert into F_PLAN values (98,5008);
>>  insert into F_PLAN values (NULL,5008);
>>   insert into F_PLAN values (777,NULL);
>>
>> On Thu, Feb 2, 2023 at 2:42 PM Kim Berg Hansen <kibeha_at_gmail.com> wrote:
>>
>>> You're welcome, Chris.
>>>
>>> Note: *If* my explanation is correct, then both original query and the
>>> rewrite might very well produce wrong output. As your lead developer states
>>> "*Both of your versions do not cater for the latest version, right?" *
>>> So if the actual intention of the query *should have been* to cater for
>>> latest version (if the intent was something like my example using FPE2
>>> table alias), you'll rather need to rewrite it to become correct, and not
>>> worry about getting same output as original.
>>>
>>> Cheerio
>>> /Kim
>>>
>>>
>>> On Thu, Feb 2, 2023 at 4:09 PM Chris Taylor <
>>> christopherdtaylor1994_at_gmail.com> wrote:
>>>
>>>> So, to sum up this reply plus your other one, one potential reason for
>>>> my not understanding this is that the table alias used in the inner query
>>>> might be incorrect?
>>>> Because that would make sense as I've never seen a max() function
>>>> applied in a subquery to an outer object (and I understood this was a
>>>> correlated subquery, I just couldn't wrap my head around what it was
>>>> "wanting to do")
>>>>
>>>> Your explanations seem to make a lot of sense.
>>>>
>>>> Thanks,
>>>> Chris
>>>>
>>>> On Thu, Feb 2, 2023 at 9:49 AM Kim Berg Hansen <kibeha_at_gmail.com>
>>>> wrote:
>>>>
>>>>> 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 Sat Feb 04 2023 - 06:21:52 CET

This message: [ Message body ]
Next message: kyle Hailey: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Previous message: Sayan Malakshinov: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
In reply to: Sayan Malakshinov: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
In reply to kyle Hailey: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Next in thread: kyle Hailey: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"
Reply: kyle Hailey: "Re: Sanity Check - Correlated Select Subquery SQL rewrite equivalent? Or no?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message