Re: Query Performance issue

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