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

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