這個陳述句執行了好多個小時都無法執行出資料，有大佬知道怎么優化一下嗎？？？要瘋了
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
uj5u.com熱心網友回復：
好多子查詢啊，資料量大 肯定慢uj5u.com熱心網友回復：
業務性太強，沒有機構，沒有資料量，不好定位1、查看其執行計劃，看看慢在哪個環節
2、一點一點注釋，直到找出慢的環節
uj5u.com熱心網友回復：
上執行計劃。如果要猜，最容易忽視的是：標量子查詢里的每一張表與外層查詢的連接欄位都需要建立索引，并能使用到（有種情況可能是關聯欄位型別不一致，那么就需要先想辦法讓連接欄位型別一致）轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/69664.html
標籤：高級技術