Also you need to help CBO with predicates like this:

<=
br>

SELECT ....~58 columns projected...<=
br>=C2=A0 FROM (SELECT ....~60 columns projected

=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 FROM "USER1"."BOS" "A2"

=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RIGHT OUTER JOIN

=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(SELECT ...~41 columns = projected from A4 and A5

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 FROM "USER1"."CS" "A4"

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0RIGHT OUTER JOIN "USER1"."COX" "A5"=

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ON "A5"."EID" =3D "A4".&= quot;EID"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- added a copy of the predicates:

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 where

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0"A5".&quo= t;BI0" =3D :b1

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 OR "A5"."BI0"= IS NOT NULL AND "A5"."CT1" =3D 'XXX'

=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 OR "A5"."BI0" IS NULL =C2=A0 =C2=A0 AND &quo= t;A5"."CT1" =3D 'YYY'

=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- end

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ) "A3"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 ON =C2=A0 =C2=A0 "A2"."BI" = =3D "A3"."BID1"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND "A2"."OID"= =3D TO_NUMBER ("A3"."OID2")) =C2=A0 =C2=A0 "A1&qu= ot;

=C2=A0WHERE =C2=A0 =C2=A0"COX"."BI0" =3D :b1

= =C2=A0 =C2=A0 =C2=A0 =C2=A0OR =C2=A0 =C2=A0 "BOS"."COl1"= ; =3D :b2

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND "BOS"."I= _DT" IS NULL

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND ( =C2=A0 =C2=A0= =C2=A0 "COX"."BI0" IS NOT NULL

=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND "COX"."CT1&qu= ot; =3D 'XXX'

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0OR "COX"."BI0" IS NULL AND "COX"."= CT1" =3D 'YYY')=C2=A0=C2=A0

=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 FROM "USER1"."BOS" "A2"

=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RIGHT OUTER JOIN

=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(SELECT ...~41 columns = projected from A4 and A5

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 FROM "USER1"."CS" "A4"

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0RIGHT OUTER JOIN "USER1"."COX" "A5"=

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ON "A5"."EID" =3D "A4".&= quot;EID"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- added a copy of the predicates:

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 where

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0"A5".&quo= t;BI0" =3D :b1

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 OR "A5"."BI0"= IS NOT NULL AND "A5"."CT1" =3D 'XXX'

=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 OR "A5"."BI0" IS NULL =C2=A0 =C2=A0 AND &quo= t;A5"."CT1" =3D 'YYY'

=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- end

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ) "A3"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 ON =C2=A0 =C2=A0 "A2"."BI" = =3D "A3"."BID1"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND "A2"."OID"= =3D TO_NUMBER ("A3"."OID2")) =C2=A0 =C2=A0 "A1&qu= ot;

=C2=A0WHERE =C2=A0 =C2=A0"COX"."BI0" =3D :b1

= =C2=A0 =C2=A0 =C2=A0 =C2=A0OR =C2=A0 =C2=A0 "BOS"."COl1"= ; =3D :b2

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND "BOS"."I= _DT" IS NULL

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND ( =C2=A0 =C2=A0= =C2=A0 "COX"."BI0" IS NOT NULL

=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND "COX"."CT1&qu= ot; =3D 'XXX'

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0OR "COX"."BI0" IS NULL AND "COX"."= CT1" =3D 'YYY')=C2=A0=C2=A0

On Thu, Dec 24, 20=
20 at 5:46 PM Sayan Malakshinov <x=
t.and.r@gmail.com> wrote:

Ok, I see the problem, you just need to = replace both left joins to 'right join', because as I said previous= ly, all rows of the final resultset should contain rows from COX.=C2=A0

=Best regards,

Sayan Malakshinov

Oracle performance tuning e= xpert

Oracle Database Developer Choice Award winner

Oracle ACE Assoc= iate

http://orasql.org<= /a><= div dir=3D"ltr">I have just simply , replaced the FULL OUTER join with=C2= =A0 LEFT OUTER Join , something as below(with actual aliases) and ran it.= =C2=A0I am sensing like,=C2=A0 i did something wrong , and not the way= which you thought of perhaps. Can you guide me here please, how you want m= e to test it.SELECT ....~5= 8 columns projected...

=C2=A0 FROM (SELECT ....~60 columns projected

= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM "USER1"."BOS" &= quot;A2"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= LEFT OUTER JOIN

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0(SELECT ...~41 columns projected from A4 and A5

=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM "USER1"."CS&q= uot; "A4"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0LEFT OUTER JOIN"USER1".&qu= ot;COX" "A5"

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ON "A5"."EI= D" =3D "A4"."EID") "A3"

=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ON =C2=A0 =C2=A0 "A2= "."BI" =3D "A3"."BID1"

=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND "A2&= quot;."OID" =3D TO_NUMBER ("A3"."OID2")) =C2= =A0 =C2=A0 "A1"

=C2=A0WHERE =C2=A0 =C2=A0"COX"."= ;BI0" =3D :b1

=C2=A0 =C2=A0 =C2=A0 =C2=A0OR =C2=A0 =C2=A0 "BOS= "."COl1" =3D :b2

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND &= quot;BOS"."I_DT" IS NULL

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 AND ( =C2=A0 =C2=A0 =C2=A0 "COX"."BI0" IS NOT NULL<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 AND "= ;COX"."CT1" =3D 'XXX'

=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0OR "COX"."BI0" IS NULL AND = "COX"."CT1" =3D 'YYY')=C2=A0=C2=A0On Thu, Dec 24, 2020 at 8:03 PM Sayan Malakshinov <xt.and.r@gm= ail.com> wrote:Can you show both original and mod= ified queries?

Best=
regards,

Sayan Malakshinov

Oracle=
performance tuning engineerSayan Malakshinov

Orac=
le ACE Associate

http://= orasql.org

http://= orasql.org