Hi Team,
I have a query, which is taking longer time., Some of the join conditions performed between the tables are on same columns and with similar filters. Also advise the suitable indexes needed on which columns which I highlighted.
Can you please help in better way of rewriting the the same query for better performance and suitable hints.
I have tables a,b,c,d,e.
select distinct
a.purch_doc_nbr
a.purch_doc_itm_nbr
b.mat_doc_nbr
c.invc_doc_nbr
e.clr_doc_nbr
from
master.a a
left join master.b b -- Table B
on b.purch_ord_nbr =a.purch_doc_nbr
and b.purch_doc_itm_nbr= a.purch_doc_itm_nbr
and b.actv_ind = 'A' and a.actv_ind = 'A'
left join master.c c -- Table C
on a.purch_doc_nbr=c.purch_doc_nbr
and c.purch_doc_itm_nbr = a.purch_doc_itm_nbr
and a.actv_ind = 'A' and c.actv_ind = 'A'
left join master.d d
on d.ref_ky_nbr = cast(c.invc_doc_nbr as varchar(20)) + cast (c.fy_nbr as varchar(20))
and d.actv_ind = 'A'
left join master.e e
on e.acct_doc_nbr=d.acct_doc_nbr
and e.actv_ind = 'A'
and e.ln_itm_nbr=c.invc_itm_nbr
and e.fy_nbr = d.fy_nbr
and e.post_ky_cd='31'
where c.purch_doc_nbr in ('6758','26546')