Re: Query Performance issue

Also you need to help CBO with predicates like this:

SELECT ....~58 columns projected...
  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')

> 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?
>>>
>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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