千万级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 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)
减少一次扫描~
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花了一个晚上都没有结果。不过我的解决方法是建了临时表 因为直接操作服务器数据库
期待解决方法