Re: Query Performance issue

Hi Sayan,
I tried by replacing the FULL OUTER JOIN with LEFT JOIN but the results do not match. Attaching the sql monitor for the same, its resulting zero rows as opposed to the original query which results into 3 rows. i am always bit confused regarding working of the outer Joins, but it seems we just cant rewrite it by simply left Join.

 Also I tried replacing the aliases with the table name from which it came from. Below is the one. Let me know if you see any issue.

SELECT ....~58 columns projected...
  FROM (SELECT ....~60 columns projected

          FROM "USER1"."BOS" "A2"
               FULL OUTER JOIN
               (SELECT ...~41 columns projected from A4 and A5
                  FROM "USER1"."CS" "A4"
                       FULL OUTER JOIN "USER1"."COX" "A5"
                          ON "A5"."EID" = "A4"."EID") "A3"
                  ON     "A2"."BI" = "A3"."BID1"
                     AND "A2"."OID" = TO_NUMBER ("A3"."OID2"))     "A1"
 WHERE    "COX"."BI0" = :b1
       OR     "BOS"."COl1" = :b2
          AND "BOS"."I_DT" IS NULL
          AND (       "COX"."BI0" IS NOT NULL
                  AND "COX"."CT1" = 'XXX'
               OR "COX"."BI0" IS NULL AND "COX"."CT1" = 'YYY')

On Thu, Dec 24, 2020 at 5:20 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Lok,
>
> In below BIO- comes from - A5 i.e table COX,
>> COL1 comes from A2 i.e table BOS
>> and CT1 comes from A5 i.e table COX
>> and I_DT comes from A2 i.e table BOS.
>>
>> WHERE "A1"."BI0" = :b1
>> OR "A1"."COl1" = :b2
>> AND "A1"."I_DT" IS NULL
>> AND ( "A1"."BI0" IS NOT NULL
>> AND "A1"."CT1" = 'XXX'
>> OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
>
>
> Are you sure in this? I've replaced "A1" with the aliases you specified
> for simplicity:
> WHERE
> "A5"."BI0" = :b1
> OR
> "A2"."COl1" = :b2
> AND "A2"."I_DT" IS NULL
> AND ( "A5"."BI0" IS NOT NULL AND "A5"."CT1" = 'XXX'
> OR "A5"."BI0" IS NULL AND "A5"."CT1" = 'YYY'
> )
>
> and looks like that means that it shouldn't be Full outer join, because
> all filtered rows must satisfy this filter:
>
> COX. BI0 = :B1
> OR COX.BI0 IS NOT NULL AND COX."CT1" = 'XXX'
> OR COX.BI0 IS NULL AND COX."CT1" = 'YYY'
>
> Ie we can rewrite it to "from COX left join CS left join BOS"
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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




text/plain attachment: Left_outer_join_sql_monitor.txt