Re: Query Performance issue
Date: Thu, 24 Dec 2020 19:34:04 +0530
Message-ID: <CAKna9VYzL2eDY0ZwLTEOEU5DEfPz=ZknG0bNzW8XL7nzV4OOeQ_at_mail.gmail.com>
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-lReceived on Thu Dec 24 2020 - 15:04:04 CET
- text/plain attachment: Left_outer_join_sql_monitor.txt