Re: Query Performance issue

Ok, I see the problem, you just need to replace both left joins to 'right 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?
>>
>

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