这几天在开发一个模块的时候，遇到一个棘手的问题：海量数据的查询效率问题，在下面的sql语句中，sf02表的数据在七千万左右，sf01表的数据在三千万左右，并且两个表有关联：
select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamount
from sf02 a
where a.ksdm = '11019204'
and exists
(select policyno
from sf01 b
where b.businessnature = '531'
union all select policyno from sf01 b where b.businessnature = '532'
and a.policyno = b.policyno)
and (a.kindcode = 'R21' or a.kindcode = 'R29')
and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')
and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')
group by a.tcol2, a.departcode, a.sendtime
由于数据库是分公司的，创建临时表不是太好使，这个问题一直没有太好的解决办法，不知道各位有没有好的思路，希望各位留下宝贵的意见，由于这个帖子放在提问区一直没有人浏览，不得已放在首页，还请blogJava对该帖放行，非常感谢。
select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamount
from sf02 a
where a.ksdm = '11019204'
and exists
(select policyno
from sf01 b
where b.businessnature = '531'
union all select policyno from sf01 b where b.businessnature = '532'
and a.policyno = b.policyno)
and (a.kindcode = 'R21' or a.kindcode = 'R29')
and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')
and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')
group by a.tcol2, a.departcode, a.sendtime
由于数据库是分公司的，创建临时表不是太好使，这个问题一直没有太好的解决办法，不知道各位有没有好的思路，希望各位留下宝贵的意见，由于这个帖子放在提问区一直没有人浏览，不得已放在首页，还请blogJava对该帖放行，非常感谢。
1 楼 冯程程 2011-09-08
难不成是保单、抽档、报盘等等...
2 楼 chansman 2011-09-08
group by 效率差, 能否用游标循环
3 楼 king9 2011-09-08
采用表连接的方式比EXISTS更有效率
根据你查询的条件，建立相关的索引，不过像这样大批量的数据，肯定要建索引的吧。
根据你查询的条件，建立相关的索引，不过像这样大批量的数据，肯定要建索引的吧。
4 楼 zui4yi1 2011-09-08
最好的解决方案是拆开来查询，每个查询都要拆开来，我们公司就是这样规定的。
虽然速度慢了一点，但性能却是最好的。
虽然速度慢了一点，但性能却是最好的。
5 楼 king9 2011-09-08
zui4yi1 写道
最好的解决方案是拆开来查询，每个查询都要拆开来，我们公司就是这样规定的。
虽然速度慢了一点，但性能却是最好的。
虽然速度慢了一点，但性能却是最好的。
有的时候拆不拆开都是一样的，sql的执行效率是一样的。你可以吧sql语句F5执行下看看，对比下消耗，和cpu的使用情况。
拆开来查询，就是拆了几次对数据库表进行了几次操作，而且数据量又这么大，就太慢了，会死人的，，错 会死机的。。。
6 楼 shryan_bin 2011-09-08
看起来像是保险公司的数据。
7 楼 yjc2020 2011-09-08
早该分表了
8 楼 xutao5641745 2011-09-08
and (a.kindcode = 'R21' or a.kindcode = 'R29')可不可以換成
and a.kindcode in ('R21','R29')and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')可不可以換成and (a.insert_time between '2000-08-25' and '2011-09-05')
然后
group by a.tcol2, a.departcode, a.sendtime
后面可不可以加一个
order by null
或者说，将
select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamountfrom sf02 awhere a.ksdm = '11019204'and exists(select policynofrom sf01 bwhere b.businessnature = '531'union all select policyno from sf01 b where b.businessnature = '532'and a.policyno = b.policyno)and (a.kindcode = 'R21' or a.kindcode = 'R29')and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')group by a.tcol2, a.departcode, a.sendtime換成
先执行结果集1select policynofrom sf01 bwhere b.businessnature = '531'union all select policyno from sf01 b where b.businessnature = '532'and a.policyno = b.policyno)and (a.kindcode = 'R21' or a.kindcode = 'R29')and a.insert_time <= to_date('2011-09-05', 'yyyy-MM-dd')and a.insert_time >= to_date('2000-08-25', 'yyyy-MM-dd')接着执行结果集2select a.tcol2, a.departcode, a.sendtime, sum(a.mainamount) mainamountfrom sf02 awhere a.ksdm = '11019204'and 结果集1 group by a.tcol2, a.departcode, a.sendtime 9 楼 xinxian 2011-09-08
建索引应该有点用吧
10 楼 jiewo 2011-09-08
第一次你SQL执行结果的记录是多少？
你的执行计划是否可以贴下？
这样直接看SQL，感觉写的没有啥问题，如果索引都走到。
唯一我想修改的地方就是
select policyno
from sf01 b
where b.businessnature = '531'
union all
select policyno
from sf01 b
where b.businessnature = '532'
and a.policyno = b.policyno
是否可以做成，
select policyno
from sf01 b
where b.businessnature = '531'or( b.businessnature = '532' and a.policyno = b.policyno)
减少一次扫描~
你的执行计划是否可以贴下？
这样直接看SQL，感觉写的没有啥问题，如果索引都走到。
唯一我想修改的地方就是
select policyno
from sf01 b
where b.businessnature = '531'
union all
select policyno
from sf01 b
where b.businessnature = '532'
and a.policyno = b.policyno
是否可以做成，
select policyno
from sf01 b
where b.businessnature = '531'or( b.businessnature = '532' and a.policyno = b.policyno)
减少一次扫描~
11 楼 jiewo 2011-09-08
修改下：
select policyno from sf01 b where b.businessnature = '531' union all select policyno from sf01 b where b.businessnature = '532' and a.policyno = b.policyno==>select distict policyno from sf01 b where b.businessnature = '531'or( b.businessnature = '532' and a.policyno = b.policyno)试下加上或者不加上 distict
12 楼 ma_xuezhi1987 2011-09-09
果断建立索引
13 楼 man_yutao 2011-09-09
jiewo 写道
第一次你SQL执行结果的记录是多少？
你的执行计划是否可以贴下？
这样直接看SQL，感觉写的没有啥问题，如果索引都走到。
唯一我想修改的地方就是
select policyno
from sf01 b
where b.businessnature = '531'
union all
select policyno
from sf01 b
where b.businessnature = '532'
and a.policyno = b.policyno
是否可以做成，
select policyno
from sf01 b
where b.businessnature = '531'or( b.businessnature = '532' and a.policyno = b.policyno)
减少一次扫描~
你的执行计划是否可以贴下？
这样直接看SQL，感觉写的没有啥问题，如果索引都走到。
唯一我想修改的地方就是
select policyno
from sf01 b
where b.businessnature = '531'
union all
select policyno
from sf01 b
where b.businessnature = '532'
and a.policyno = b.policyno
是否可以做成，
select policyno
from sf01 b
where b.businessnature = '531'or( b.businessnature = '532' and a.policyno = b.policyno)
减少一次扫描~
select policyno
from sf01 b
where (b.businessnature = '531'or b.businessnature = '532' ) and a.policyno = b.policyno
14 楼 yoonix 2011-09-09
各位说的都在理，不知楼主怎么做的
15 楼 yunyanmiman 2011-09-09
表分区,,,,
16 楼 isaac.198 2011-09-10
没见过KW级表的飘过，我们一般会做垂直分表的。
顺便来看看大家的讨论
顺便来看看大家的讨论
17 楼 kongzhizhen 2011-09-10
可以考虑对表进行分区.
18 楼 canyang452 2011-09-18
这就是保险行业的sql吧，我也遇到lz类似的问题，执行一个sql花了一个晚上都没有结果。不过我的解决方法是建了临时表 因为直接操作服务器数据库
期待解决方法
期待解决方法