求助数据查询及导出问题

[ylq1994]

这个语句执行了好多个小时都无法执行出数据，有大佬知道怎么优化一下吗？？？要疯了

select count( *) from (

select cons_no,
(select prop_list_name from epm_ln.sa_prop_list where prop_type_id='plan_status' and prop_list_id=y.plan_status) 当前计划状态,
(select prop_list_name from epm_ln.sa_prop_list where prop_type_id='mr_sect_attr' and prop_list_id=y.mr_sect_attr) 用电性质,
y.PLAN_MR_DATE 计划抄表日期,
y.send_date 发行日期,
x.build_date 建档日期,
z.ogr_name kkk,
(select prop_list_name from epm_ln.sa_prop_list where prop_type_id='mr_mode' and prop_list_id=y.mr_mode_code) 抄表方式,

(select -- b.cons_no 用户编号,
nvl(b.prepay_bal,0)-(nvl(sum(a.rcvbl_amt-a.rcved_amt)+sum(a.rcvbl_penalty-a.rcved_penalty),0))
from epm_ln.a_rcvbl_flow a
right join epm_ln.a_acct_bal b on a.cons_no=b.cons_no
where b.org_no like '21402%'
and b.cons_no=x.cons_no group by b.cons_no,b.prepay_bal) 用户余额,
x.mr_sect_no



from epm_ln.c_cons x, epm_ln.r_plan y ,epsa_ln.sa_org
where x.mr_sect_no=y.mr_sect_no and x.org_no like '21402%'
and z.org_no=y.org_no
and y.amt_ym='201803'
and y.plan_status='08'
and x.status_code<>'9'
and y.org_no like '21402%'
and y.mr_sect_attr not in ('00')
--- and y.mr_sect_no='8007980422'
and to_char(y.send_date,'yyyy-mm-dd')='2018-03-06'


union all

select cons_no,
(select prop_list_name from epm_ln.sa_prop_list where prop_type_id='plan_status' and prop_list_id=y.plan_status) 当前计划状态,
(select prop_list_name from epm_ln.sa_prop_list where prop_type_id='mr_sect_attr' and prop_list_id=y.mr_sect_attr) 用电性质,
y.PLAN_MR_DATE 计划抄表日期,
y.send_date 发行日期,
x.build_date 建档日期,
z.ogr_name kkk ,
(select prop_list_name from epm_ln.sa_prop_list where prop_type_id='mr_mode' and prop_list_id=y.mr_mode_code) 抄表方式,

(select -- b.cons_no 用户编号,
nvl(b.prepay_bal,0)-(nvl(sum(a.rcvbl_amt-a.rcved_amt)+sum(a.rcvbl_penalty-a.rcved_penalty),0))
from epm_ln.a_rcvbl_flow a
right join epm_ln.a_acct_bal b on a.cons_no=b.cons_no
where b.org_no like '21402%'
and b.cons_no=x.cons_no group by b.cons_no,b.prepay_bal) 用户余额,
x.mr_sect_no



from epm_ln.c_cons x, epm_ln.arc_r_plan y ,epsa_ln.sa_org z
where x.mr_sect_no=y.mr_sect_no and x.org_no like '21402%'
and z.org_no=y.org_no
and y.amt_ym='201803'
and y.plan_status='08'
and x.status_code<>'9'
and y.org_no like '21402%'
and y.mr_sect_attr not in ('00')
--- and y.mr_sect_no='8007980422'
and to_char(y.send_date,'yyyy-mm-dd')='2018-03-06'
) where nvl(用户余额,0)< 0
group by kkk

[学海无涯-回头是岸]

好多子查询啊，数据量大 肯定慢

[jdsnhan]

业务性太强，没有机构，没有数据量，不好定位 1、查看其执行计划，看看慢在哪个环节 2、一点一点注释，直到找出慢的环节

[minsic78]

上执行计划。如果要猜，最容易忽视的是：标量子查询里的每一张表与外层查询的连接字段都需要建立索引，并能使用到（有种情况可能是关联字段类型不一致，那么就需要先想办法让连接字段类型一致）