我有条SQL如下
select b.cons_no,
b.cust_query_no,
b.cons_name,
a.rcvbl_ym,
a.release_date,
a.amt_type,
a.RCVBL_AMT,
a.RCVED_AMT,
a.RCVBL_PENALTY,
a.RCVED_PENALTY,
a.T_PQ,
(select sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY)
from EPM_JX.A_RCVBL_FLOW
where cons_no = a.cons_no) OWE_AMT,
(select nvl(sum(PREPAY_BAL), 0)
from EPM_JX.A_ACCT_BAL
where cons_no = a.cons_no) PREPAY_BAL
from EPM_JX.A_RCVBL_FLOW a, EPM_JX.c_cons b
where a.cons_no = b.cons_no
and a.rcvbl_amt > 0
and a.RELEASE_DATE = '20081001'
and a.org_no like '36408'
and b.org_no like '36408'它的结果集有 40000 条记录,但是很慢,而且执行到1000行左右就执行不下去了,
主要是如下两个费时间
(select sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY)
from EPM_JX.A_RCVBL_FLOW
where cons_no = a.cons_no) OWE_AMT,
(select nvl(sum(PREPAY_BAL), 0)
from EPM_JX.A_ACCT_BAL
where cons_no = a.cons_no) PREPAY_BAL
这我该如何优化SQL啊,高手帮我!!!!
select b.cons_no,
b.cust_query_no,
b.cons_name,
a.rcvbl_ym,
a.release_date,
a.amt_type,
a.RCVBL_AMT,
a.RCVED_AMT,
a.RCVBL_PENALTY,
a.RCVED_PENALTY,
a.T_PQ,
(select sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY)
from EPM_JX.A_RCVBL_FLOW
where cons_no = a.cons_no) OWE_AMT,
(select nvl(sum(PREPAY_BAL), 0)
from EPM_JX.A_ACCT_BAL
where cons_no = a.cons_no) PREPAY_BAL
from EPM_JX.A_RCVBL_FLOW a, EPM_JX.c_cons b
where a.cons_no = b.cons_no
and a.rcvbl_amt > 0
and a.RELEASE_DATE = '20081001'
and a.org_no like '36408'
and b.org_no like '36408'它的结果集有 40000 条记录,但是很慢,而且执行到1000行左右就执行不下去了,
主要是如下两个费时间
(select sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY)
from EPM_JX.A_RCVBL_FLOW
where cons_no = a.cons_no) OWE_AMT,
(select nvl(sum(PREPAY_BAL), 0)
from EPM_JX.A_ACCT_BAL
where cons_no = a.cons_no) PREPAY_BAL
这我该如何优化SQL啊,高手帮我!!!!
select b.cons_no,
b.cust_query_no,
b.cons_name,
a.rcvbl_ym,
a.release_date,
a.amt_type,
a.RCVBL_AMT,
a.RCVED_AMT,
a.RCVBL_PENALTY,
a.RCVED_PENALTY,
a.T_PQ,
c.OWE_AMT,
d.PREPAY_BAL
from EPM_JX.A_RCVBL_FLOW a, EPM_JX.c_cons b ,
(select cons_no,sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY) as OWE_AMT
from EPM_JX.A_RCVBL_FLOW
group by cons) c ,
(select cons_no,nvl(sum(PREPAY_BAL), 0) as PREPAY_BAL
from EPM_JX.A_ACCT_BAL
group by cons) d
where a.cons_no = b.cons_no
and a.cons_no = c.cons_no
and a.cons_no = d.cons_no
and a.rcvbl_amt > 0
and a.RELEASE_DATE = '20081001'
and a.org_no like '36408'
and b.org_no like '36408'
where cons_no = a.cons_no
想办法把它提取出来,应该会有很大改善,试试下面的语句吧,估计会有问题,没有测试环境,不好调试
select b.cons_no,
b.cust_query_no,
b.cons_name,
a.rcvbl_ym,
a.release_date,
a.amt_type,
a.RCVBL_AMT,
a.RCVED_AMT,
a.RCVBL_PENALTY,
a.RCVED_PENALTY,
a.T_PQ,
sum(rcvbl_amt - rcved_amt+ RCVBL_PENALTY- RCVED_PENALTY) OWE_AMT,
nvl(sum(PREPAY_BAL), 0) PREPAY_BAL
from EPM_JX.A_RCVBL_FLOW a, EPM_JX.c_cons b
where a.cons_no = b.cons_no
and a.rcvbl_amt > 0
and a.RELEASE_DATE = '20081001'
and a.org_no like '36408'
and b.org_no like '36408'
结贴了