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);
還是不好用。。求大神看下給一下優化思路。。謝謝！
uj5u.com熱心網友回復：
索引建得有問題，正確的應該是：
CREATE INDEX EMEB_PS_EP_TREASURYPAY_FAST ON PS_EP_TREASURYPAY(CONTRACT_ID,PAYSTATE);
uj5u.com熱心網友回復：
exists用得太多，不清楚腳本邏輯，無從優化。uj5u.com熱心網友回復：
1、少用子查詢2、根據執行計劃做優化，比如加索引
uj5u.com熱心網友回復：
這種是典型的差SQL吧，嵌套子查詢太多另外相關表都要為合同編號建上索引，是按1樓那種形式。原先的索引建法不起作用。
uj5u.com熱心網友回復：
子查詢太多，而且又是那種條件較少的查詢，基本已沒有優化的空間了，如果確實需要優化的話，看看有沒有重復的SQL，采用with結構，或許可以減少一點兒邏輯讀
uj5u.com熱心網友回復：
oracle11g以后基本上不用exists陳述句，對優化已經沒用了。這么多exists不頭疼啊。uj5u.com熱心網友回復：
你看下執行計劃有沒有某個表多次訪問，搞個with asuj5u.com熱心網友回復：
你的這個代碼看起來邏輯混亂，而且建索引未必有用，建議對于not exists 的表先with as，避免掃描多次轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/78005.html
標籤：開發
下一篇：PB 資料管道資料不能完全傳