17,086
社区成员
发帖
与我相关
我的任务
分享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);
还是不好用。。求大神看下给一下优化思路。。谢谢！
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);
还是不好用。。求大神看下给一下优化思路。。谢谢！
...全文
请发表友善的回复…
发表回复
「已注销」 2017-12-15
- 打赏
- 举报
你的这个代码看起来逻辑混乱，而且建索引未必有用，建议对于not exists 的表先with as，避免扫描多次
dzbkq_110 2017-12-14
- 打赏
- 举报
你看下执行计划有没有某个表多次访问，搞个with as
dzbkq_110 2017-12-14
- 打赏
- 举报
oracle11g以后基本上不用exists语句，对优化已经没用了。这么多exists不头疼啊。
zmcc135 2017-12-13
- 打赏
- 举报
这种是典型的差SQL吧，嵌套子查询太多
另外相关表都要为合同编号建上索引，是按1楼那种形式。原先的索引建法不起作用。
风中的千纸鹤 2017-12-13
- 打赏
- 举报
子查询太多，而且又是那种条件较少的查询，基本已没有优化的空间了，如果确实需要优化的话，看看有没有重复的SQL，采用with结构，或许可以减少一点儿逻辑读
江南小鱼 2017-12-11
- 打赏
- 举报
1、少用子查询
2、根据执行计划做优化，比如加索引
碧水幽幽泉 2017-12-11
- 打赏
- 举报
exists用得太多，不清楚脚本逻辑，无从优化。
碧水幽幽泉 2017-12-11
- 打赏
- 举报
索引建得有问题，正确的应该是：
CREATE INDEX EMEB_PS_EP_TREASURYPAY_FAST ON PS_EP_TREASURYPAY(CONTRACT_ID,PAYSTATE);
