Pfthb

Im trying to execute the below query and it is taking long time to execute. Is there anyway i can make changes in the below query? It is taking more than 60mins for executing and sometimes it is getting hanged.

select distinct z.por_s as Portfolio_Number,z.lse_s as 
Account_Number,y.les_s as Lessee_Number,z.unit_S as Asset,to_char(x.d_com_s, 
'MM/DD/YYYY') as Start_Date, to_char(x.d_mat_s,'MM/DD/YYYY') as 
Maturity_Date, x.p_con_d as Yield,k.nam_long_s as Venodr_Name,z.dlr_s as 
Venodr_Number,y.client_s_name_s as Customer_Name,y.short_add_s as 
Billing_Address,y.short_cty_s as City,y.st_s as State,z.eq_add_s as 
Equipment_Address, x.trm_l as Original_Term,(x.TRM_L - x.PMT_L) as 
Remaining_Term,z.id_S as EquipmentSerialID,z.org_d as Original_Amount, 
(NVL(x.lsd_unit_d, 0.0) - NVL(x.acl_depr_d, 0.0) - NVL(x.sec_d, 0.0) - 
NVL(SUM(b.bal_d), 0.0) + NVL(SUM(NVL(a.pamt_b_d, 0.0) + NVL(a.iamt_b_d, 0.0) 
- NVL(a.pamt_p_d, 0.0) - NVL(a.iamt_p_d, 0.0)), 0.0) - NVL(x.op_bill_adj_d, 
0.0) + NVL(x.unrec_in_d, 0.0)) as OPR_Remaining_Net_Investment,
(NVL(x.rec_d, 0.0) + NVL(SUM(NVL(d.aamt_b_d, 0.0) - NVL(d.aamt_p_d, 0.0)), 
0.0) + NVL(SUM(NVL(a.pamt_b_d, 0.0) + NVL(a.iamt_b_d, 0.0) - 
NVL(a.pamt_p_d, 0.0) - NVL(a.iamt_p_d, 0.0)), 0.0) + NVL(x.tot_res_d, 0.0) - 
NVL(x.res_guar_d, 0.0) - NVL(x.l_ue_d, 0.0) + NVL(x.r_ue_d, 0.0) + 
NVL(x.i_ue_d, 0.0) + NVL(x.d_ue_d, 0.0) + NVL(x.l_in_ptd_d, 0.0) + 
NVL(x.r_in_ptd_d, 0.0) + NVL(x.i_in_ptd_d, 0.0) + NVL(x.d_in_ptd_d, 0.0) - 
NVL(x.sec_d, 0.0) - NVL(SUM(b.bal_d), 0.0) - NVL(x.susp_l_acc_d, 0.0) + 
NVL(x.susp_r_acc_d, 0.0) + NVL(x.susp_d_acc_d, 0.0) + NVL(x.susp_i_acc_d, 
0.0)) as PRE_Remaining_Net_Investment
,z.res_d as Residual_Asset ,n.bus_phn_s as Business_Phone, n.hom_phn_s as 
Home_phone,o.field19_s as Credit_Guarantee, o.field20_s as 
Residual_Guarantee,m.add_s as Guarantor_Address,m.add2_s as 
Guarantor_Address2,m.cty_s as Guarantor_City,m.st_s as 
Guarantor_State,m.zip_s as Guarantor_Zip
from rgc c 
FULL join rls x on c.APP_LSE_S = x.LSE_S
FULL Join rar a on x.LSE_S = a.LSE_S
FULL Join rsu b on x.LSE_S = b.LSE_S
FULL Join rarb d on x.por_s = d.por_s
FULL join vrcx_les y on x.les_s = y.les_s
FULL join req z on x.LSE_S = z.LSE_S
FULL join rlr k on z.dlr_s = k.dlr_s
FULL join ral n on x.les_s = n.les_s
FULL join rug o on x.lse_s = o.lse_s
FULL JOIN RGR m ON c.CLIENT_ID_S = m.GRN_S 
where x.act_s in ('AACQ', 'AASM', 'AEXM', 'ANOR', 'ARBK', 'ARED', 'AREN', 
'AXFR') and x.d_mat_s > to_date ('07/01/2018', 'MM/DD/YYYY')
and a.por_s = x.por_s
AND a.com_s = x.com_s
AND a.reg_s = x.reg_s
AND a.off_s = x.off_s
AND a.lse_s = x.lse_s
AND d.invo_s = a.invo_s
AND d.a_typ_s in ('PROR', 'IDR2', 'SECD')
AND NVL(a.income_accrued_c, ' ') <> 'N'
group by z.por_s,z.lse_s,y.les_s,z.unit_S,x.d_com_s,
x.d_mat_s,x.p_con_d,k.nam_long_s,
z.dlr_s,y.client_s_name_s,y.short_add_s,
y.short_cty_s,y.st_s,z.eq_add_s,x.trm_l,x.PMT_L, 
z.id_S,z.org_d,x.lsd_unit_d,x.acl_depr_d,x.sec_d,
b.bal_d,a.pamt_b_d,a.iamt_b_d,a.pamt_p_d,a.iamt_p_d,
x.op_bill_adj_d,x.unrec_in_d,x.rec_d,x.res_guar_d,
d.aamt_b_d,d.aamt_p_d,a.pamt_b_d,a.iamt_b_d,a.pamt_p_d, 
a.iamt_p_d,x.tot_res_d,x.res_guar_d,x.l_ue_d,x.r_ue_d,
x.i_ue_d,x.d_ue_d,x.l_in_ptd_d,x.r_in_ptd_d,x.i_in_ptd_d,
x.d_in_ptd_d,x.susp_l_acc_d,x.susp_r_acc_d,x.susp_d_acc_d,
x.susp_i_acc_d,z.res_d,n.bus_phn_s,n.hom_phn_s,o.field19_s,
o.field20_s,m.add_s,m.add2_s,m.cty_s,m.st_s,m.zip_s;

Any changes needs to made on this query just help me out on this.

 select distinct z.por_s as Portfolio_Number,
 z.lse_s as Account_Number,
 ...

 from rgc c 
 FULL join rls x on c.APP_LSE_S = x.LSE_S #match nro account (1)
 FULL Join rar a on x.LSE_S = a.LSE_S #match nro account (2)
 FULL Join rsu b on x.LSE_S = b.LSE_S #match nro account (3)
 FULL Join rarb d on x.por_s = d.por_s #match (4)
 FULL join vrcx_les y on x.les_s = y.les_s #match nro less
 FULL join req z on x.LSE_S = z.LSE_S #match nro account

 FULL join rlr k on z.dlr_s = k.dlr_s

 FULL join ral n on x.les_s = n.les_s #match nro less
 FULL join rug o on x.lse_s = o.lse_s #match nro account

 FULL JOIN RGR m ON c.CLIENT_ID_S = m.GRN_S 
 where x.act_s in 
 ('AACQ', 'AASM', 'AEXM', 'ANOR', 'ARBK', 'ARED', 'AREN', 'AXFR') #use this in the full join (2)
 and x.d_mat_s > to_date ('07/01/2018', 'MM/DD/YYYY') #use this in the full join (2)
 and a.por_s = x.por_s #use this in the full join (2)
 AND a.com_s = x.com_s #use this in the full join (2)
 AND a.reg_s = x.reg_s #use this in the full join (2)
 AND a.off_s = x.off_s #use this in the full join (2)
 AND a.lse_s = x.lse_s #use this in the full join (2)
 AND d.invo_s = a.invo_s #use this in the full join (4)
 AND d.a_typ_s in ('PROR', 'IDR2', 'SECD')
 AND NVL(a.income_accrued_c, ' ') <> 'N' #nvl is not necesary
 group by z.por_s,z.lse_s,y.les_s,z.unit_S,x.d_com_s,
 ...

I would put the restrictions in the joins, so you dont combine all the rows (1000 x 1000 for example), just the rows you need (100 x 1000) Like this:

 from rgc c 
 FULL join rls x on c.APP_LSE_S = x.LSE_S #match nro account (1)
 FULL Join rar a on x.LSE_S = a.LSE_S #match nro account (2)
 and x.act_s in 
 ('AACQ', 'AASM', 'AEXM', 'ANOR', 'ARBK', 'ARED', 'AREN', 'AXFR') #use this in the full join (2)
 and x.d_mat_s > to_date ('07/01/2018', 'MM/DD/YYYY') #use this in the full join (2)
 and a.por_s = x.por_s #use this in the full join (2)
 AND a.com_s = x.com_s #use this in the full join (2)
 AND a.reg_s = x.reg_s #use this in the full join (2)
 AND a.off_s = x.off_s #use this in the full join (2)
 AND a.lse_s = x.lse_s #use this in the full join (2)
 AND a.income_accrued_c <> 'N' #nvl is not necesary, if its null its <> from 'N'

 FULL Join rsu b on x.LSE_S = b.LSE_S #match nro account (3)
 FULL Join rarb d on x.por_s = d.por_s #match (4)
 and d.invo_s = a.invo_s #use this in the full join (4)
 AND d.a_typ_s in ('PROR', 'IDR2', 'SECD')
 FULL join vrcx_les y on x.les_s = y.les_s #match nro less
 FULL join req z on x.LSE_S = z.LSE_S #match nro account

 FULL join rlr k on z.dlr_s = k.dlr_s

 FULL join ral n on x.les_s = n.les_s #match nro less
 FULL join rug o on x.lse_s = o.lse_s #match nro account

 FULL JOIN RGR m ON c.CLIENT_ID_S = m.GRN_S 
 where 1=1
 group by ....

And I agree with the comments, if you want a more helpful answer, you have to post a description of every table used in the query as and a brief explanation of the fields in them. Also the functional explanation of the query, what kind of information you want to extract.