This statement is executed many hours can perform the data, have bosses know how to optimize it for me??????? Going crazy

Select count (*) from (

The select cons_no,
(select prop_list_name from epm_ln. Sa_prop_list where prop_type_id='plan_status' and prop_list_id=y.p lan_status) state of the current plan,
(select prop_list_name from epm_ln. Sa_prop_list where prop_type_id='mr_sect_attr' and prop_list_id=y.m r_sect_attr) electricity properties,
Y.P LAN_MR_DATE plan date of meter reading,
Y.s end_date issue date,
X.b uild_date document date,
Z.o gr_name KKK,
(select prop_list_name from epm_ln. Sa_prop_list where prop_type_id='mr_mode' and prop_list_id=y.m r_mode_code) meter reading way,

(select - the biggest ons_no user number,
NVL (p. repay_bal, 0) - (NVL (sum (a.r cvbl_amt - a.r cved_amt) + sum (a.r cvbl_penalty - a.r cved_penalty), 0))
The from epm_ln. A_rcvbl_flow a
Right join epm_ln. A_acct_bal b on a.c ons_no=biggest ons_no
Where b.org _no like '21402%'
And biggest ons_no=x.c ons_no group by biggest ons_no, p. repay_bal) user balance,
X.m r_sect_no



The from epm_ln c_cons x, epm_ln. R_plan y, epsa_ln.
sa_org_no where x.m r_sect_no=y.m r_sect_no and x.org like '21402%'
And z.org_no=y.org _no
And y.a mt_ym='201803'
And y.p lan_status='08'
And x.s tatus_code & lt;> '9'
And y.org _no like '21402%'
And y.m r_sect_attr not in (' 00 ')
- and y.m r_sect_no='8007980422'
And to_char (y.s end_date, '- dd yyyy - mm)=' 2018-03-06 '


Union all

The select cons_no,
(select prop_list_name from epm_ln. Sa_prop_list where prop_type_id='plan_status' and prop_list_id=y.p lan_status) state of the current plan,
(select prop_list_name from epm_ln. Sa_prop_list where prop_type_id='mr_sect_attr' and prop_list_id=y.m r_sect_attr) electricity properties,
Y.P LAN_MR_DATE plan date of meter reading,
Y.s end_date issue date,
X.b uild_date document date,
Z.o gr_name KKK,
(select prop_list_name from epm_ln. Sa_prop_list where prop_type_id='mr_mode' and prop_list_id=y.m r_mode_code) meter reading way,

(select - the biggest ons_no user number,
NVL (p. repay_bal, 0) - (NVL (sum (a.r cvbl_amt - a.r cved_amt) + sum (a.r cvbl_penalty - a.r cved_penalty), 0))
The from epm_ln. A_rcvbl_flow a
Right join epm_ln. A_acct_bal b on a.c ons_no=biggest ons_no
Where b.org _no like '21402%'
And biggest ons_no=x.c ons_no group by biggest ons_no, p. repay_bal) user balance,
X.m r_sect_no



The from epm_ln c_cons x, epm_ln. Arc_r_plan y, z epsa_ln. Sa_org
_no where x.m r_sect_no=y.m r_sect_no and x.org like '21402%'
And z.org_no=y.org _no
And y.a mt_ym='201803'
And y.p lan_status='08'
And x.s tatus_code & lt;> '9'
And y.org _no like '21402%'
And y.m r_sect_attr not in (' 00 ')
- and y.m r_sect_no='8007980422'
And to_char (y.s end_date, '- dd yyyy - mm)=' 2018-03-06 '
) where NVL (user balance, 0) & lt; 0
Group by KKK

CodePudding user response:

Good sons, query, data must be slow

CodePudding user response:

Equipment is too strong, no institution, no amount of data, bad positioning
1, check the execution plan, see slow in which link
2, little by little, until find out slow link

CodePudding user response:

On execution plan, if you want to guess, is the most easily neglected: a list of the scalar subquery connected to the outer query fields need to be indexed, and can be used to (the case may be associated with the field type, you will need to think of some way to get first connect the field type)