query optimization

[rajan1]

Hi all,

I need to optimize the following query,

Select A.*,B.* from A LEFT OUTER JOIN B ON(A.nm=B.nm AND A.dt=B.dt AND A.cd=B.cd AND B.lnnbr = 0)
UNION
Select A.*,B.* from A LEFT OUTER JOIN B ON(A.nm=B.nm AND A.lnnbr=B.lnnbr AND B.lnnbr >0)

Your expert suggestion is welcome.

Thanks in advance.

Thanks,
Rajan

[rajan1]

What are the different ways this SQL can be written?

Thanks in advance.

Thanks,
Rajan

[mike9]

If distinct values is not a issue in your case i recommand to use UNION ALL instead of UNION.

The use of an OR instead of executing twice the query may also imporve the preformance:

(A.nm=B.nm AND

  (  (A.dt=B.dt AND A.cd=B.cd AND B.lnnbr = 0 )

  OR  (A.lnnbr=B.lnnbr AND B.lnnbr >0)  

  )

)

[rajan1]

Hi Mike thanks for your help.

I tried the same query before,i was surprised with the total row counts between using UNION and replacing UNION with OR,the total row count using UNION is 311786 and the total row count using OR is 283383
I am not sure about the difference in the row count,but to my understanding it should not make any difference in the total row count.

I need the expert advice regarding this.

Distinct is an issue and we cannot use UNION ALL.

Thanks in advance.

Regards,
Rajan