select nvl(sum(t1.JF_NUM_PUB), 0) as col2,
nvl(sum(t1.JF_NUM_PRI), 0) as col3,
nvl(sum(t2.JF_NUM_PUB), 0) as col4,
nvl(sum(t2.JF_NUM_PUB), 0) as col5,
nvl(sum(t3.JF_NUM_PUB), 0) as col6,
nvl(sum(t3.JF_NUM_PRI), 0) as col7,
nvl(sum(t4.JF_NUM_PUB), 0) as col8,
nvl(sum(t4.JF_NUM_PUB), 0) as col9,
nvl(sum(t5.JF_NUM_PUB), 0) as col10,
nvl(sum(t5.JF_NUM_PRI), 0) as col11
from JF_PENSION t1
inner join JF_UNEMPLOY t2 on t1.jdep = t2.jdep
and t1.jdate = t2.jdate
inner join JF_WORKINJURY t3 on t3.jdep = t1.jdep
and t1.jdate = t3.jdate
inner join JF_MEDICAL t4 on t1.jdep = t4.jdep
and t1.jdate = t4.jdate
inner join JF_HOUSE t5 on t1.jdep = t5.jdep
and t1.jdate = t5.jdate
where t1.JDATE like '%2012-%'
数据量大的话这个查询会死掉 很慢很慢 哪位大神给优化下 谢谢了
1、看看这几表的jdep和jdate加索引了吗？
2、inner join太多，建议拆成5个单独的sql试试
首先，将这些联合查询的表jdep字段设定索引。如果需要进一步优化，就需根据具体的数据分布，选择那些先过滤，那些后过滤了。
你的查询条件其实都一样，何不分开，单独查询
[code="sql"]select nvl(sum(t1.JF_NUM_PUB), 0) as col2,
nvl(sum(t1.JF_NUM_PRI), 0) as col3,
from JF_PENSION t1
where t1.JDATE like '%2012-%' [/code]
[code="java"]select
nvl(sum(t2.JF_NUM_PUB), 0) as col4,
nvl(sum(t2.JF_NUM_PUB), 0) as col5,
from JF_PENSION t2
where t2.JDATE like '%2012-%' [/code]
而且结果都汇总值（sum），就是都只有一行数据，单独查询后，在应用中简单合并就可以了