SQL 优化问题
select count(T.CONTRACT_ID) TOTALNUM,
nvl(sum(S.SPLITAMOUNT), 0) TOTALMONEY,
nvl(sum(S.SPLITNUM), 0) TOTALAMOUNT
from PS_CTT_CONTRACT T, PS_CTT_CONTRACTDETAIL S
where T.CONTRACT_ID = S.CONTRACT_ID
and T.CONTRACTENDSTATE = '0'
and ((T.RUNSTATE in ('5', '10') and
(exists (select *
from PS_AA_AVAILBILL A
where A.CONTRACT_ID = T.CONTRACT_ID
and A.BILL_STATE in ('1', '2', '3', '4', '10', '11')) and
(not exists (select B.CONTRACT_ID
from PS_EP_TREASURYPAY B
where B.CONTRACT_ID = T.CONTRACT_ID) and
not exists
(select C.CONTRACT_ID
from PS_EP_PAY C
where C.CONTRACT_ID = T.CONTRACT_ID) or exists
(select B.CONTRACT_ID
from PS_EP_TREASURYPAY B
where B.CONTRACT_ID = T.CONTRACT_ID
and B.PAYSTATE in ('00', '01', '03')
union all
select C.CONTRACT_ID
from PS_EP_PAY C
where C.CONTRACT_ID = T.CONTRACT_ID
and C.PAYSTATE in ('00', '01', '03'))))) or
(T.RUNSTATE in ('6', '7', '9') and not exists
(select B.CONTRACT_ID
from PS_EP_TREASURYPAY B
where B.CONTRACT_ID = T.CONTRACT_ID
and B.PAYSTATE in ('00', '01', '03')) and not exists
(select C.CONTRACT_ID
from PS_EP_PAY C
where C.CONTRACT_ID = T.CONTRACT_ID
and C.PAYSTATE in ('00', '01', '03'))))
and T.DISTRICTCODE = '00';
大量数据,此sql已经是经过优化了,还是效率慢,加过了如下索引
CREATE INDEX emeb_PS_EP_TREASURYPAY_fast on PS_EP_TREASURYPAY(PAYSTATE,CONTRACT_ID);
还是不好用。。求大神看下给一下优化思路。。谢谢!