Re: Query Performance issue
Date: Thu, 24 Dec 2020 17:54:07 +0300
Message-ID: <CAOVevU5hZkLAy7ZfBoV=sSLNsX_+3O3vKHw=_HNGKcrmSN0AJg_at_mail.gmail.com>
Also you need to help CBO with predicates like this:
SELECT ....~58 columns projected...
On Thu, Dec 24, 2020 at 5:46 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
wrote:
> Ok, I see the problem, you just need to replace both left joins to 'right
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
RIGHT OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
RIGHT OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID"
-- added a copy of the predicates:
where
"A5"."BI0" = :b1
OR "A5"."BI0" IS NOT NULL AND "A5"."CT1" = 'XXX'
OR "A5"."BI0" IS NULL AND "A5"."CT1" = 'YYY'
-- end
) "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')
> join', because as I said previously, all rows of the final resultset should
> contain rows from COX.
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
> чт, 24 дек. 2020 г., 17:43 Lok P <loknath.73_at_gmail.com>:
>
>> I have just simply , replaced the FULL OUTER join with LEFT OUTER Join ,
>> something as below(with actual aliases) and ran it.
>> I am sensing like, i did something wrong , and not the way which you
>> thought of perhaps. Can you guide me here please, how you want me to test
>> it.
>>
>> SELECT ....~58 columns projected...
>> FROM (SELECT ....~60 columns projected
>> FROM "USER1"."BOS" "A2"
>> *LEFT OUTER JOIN*
>> (SELECT ...~41 columns projected from A4 and A5
>> FROM "USER1"."CS" "A4"
>> *LEFT 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 8:03 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
>> wrote:
>>
>>> Can you show both original and modified queries?
>>>
>>
-- 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:54:07 CET