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

ChatGPT is pretty impressive

>> do these two SQL statements return the same data?

The two SQL statements might return the same data, but it depends on the data in the "F_PLAN" table and the requirements of the query.

If there is only one row for each "GUID" value of either 5009 or 5008 and the maximum "ID" value is the same as the "ID" value in that row, then both statements will return the same data.

However, if there are multiple rows for each "GUID" value with different "ID" values, the first statement will return the row with the maximum "ID" value for each "GUID" value, whereas the second statement will return all the rows that have a "GUID" value of either 5009 or 5008.

On Sat, Feb 4, 2023 at 12:21 AM kyle Hailey <kylelf_at_gmail.com> wrote:

>
>
> 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 |
>
> | 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 |
>
> | 29 | 5009 |
>
> | 29 | 5009 |
>
> | 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