日期:2014-05-17  浏览次数:20938 次

oracle查询效率
表,都创建索引了,但是查询速度依然很慢,高手请指教。
SQL code

select sx201008.stmc stmc,(nvl(sx201008.i_kaizhangsl,0)+nvl(sx201009.i_kaizhangsl,0)+nvl(sx201010.i_kaizhangsl,0)+nvl(sx201011.i_kaizhangsl,0)) i_kaizhangsl,'预警分析' title,'2010-11-29' rq,'徐州首创' sx
                 ,'6798023' jhsl,'80' red,'100' yellow 
                 from  (select '徐州首创' stmc,0+sum(i_zongyongl) i_kaizhangsl
                            from IBCS_XZ.ZW_YINGYEZ201008 a
                            inner join IBCS_XZ.ZW_YINGYEZ_ZB201008 b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201008
                            and b.i_jlzt=0 and i_feiyongdlbh=580 
                            and i_xiaozhang<>20) sx201008 
                            left join 
                            (select '徐州首创' stmc,450776+sum(i_zongyongl) i_kaizhangsl
                            from IBCS_XZ.ZW_YINGYEZ201009 a
                            inner join IBCS_XZ.ZW_YINGYEZ_ZB201009 b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201009 and b.i_jlzt=0 and i_feiyongdlbh=580
                            and i_xiaozhang<>20) sx201009 on sx201009.stmc=sx201008.stmc 
                            left join 
                            (select '徐州首创' stmc,-17120+sum(i_zongyongl) i_kaizhangsl
                            from IBCS_XZ.ZW_YINGYEZ201010 a
                            inner join IBCS_XZ.ZW_YINGYEZ_ZB201010 b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201010 and b.i_jlzt=0 and i_feiyongdlbh=580
                            and i_xiaozhang<>20) sx201010 on sx201010.stmc=sx201008.stmc 
                            left join 
                            (select '徐州首创' stmc,sum(i_zongyongl)-16506 i_kaizhangsl
                            from IBCS_XZ.zw_yingyez a
                            inner join IBCS_XZ.zw_yingyez_zb b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201011 and b.i_jlzt=0 and i_feiyongdlbh=580
                            and i_xiaozhang<>20) sx201011 on sx201011.stmc=sx201008.stmc 
union all 
                            select sx201003.stmc stmc,(nvl(sx201003.i_kaizhangsl,0)+nvl(sx201004.i_kaizhangsl,0)+nvl(sx201005.i_kaizhangsl,0)+nvl(sx201006.i_kaizhangsl,0)+nvl(sx201007.i_kaizhangsl,0)+nvl(sx201008.i_kaizhangsl,0)+nvl(sx201009.i_kaizhangsl,0)+nvl(sx201010.i_kaizhangsl,0)+nvl(sx201011.i_kaizhangsl,0)) i_kaizhangsl,'预警分析' title,'2010-11-29' rq,'铜陵首创' sx
                 ,'43677613' jhsl,'80' red,'100' yellow 
                 from  (select '铜陵首创' stmc,0+sum(i_zongyongl) i_kaizhangsl
                            from IBCS_TL.ZW_YINGYEZ201003 a
                            inner join IBCS_TL.ZW_YINGYEZ_ZB201003 b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201003
                            and b.i_jlzt=0 and i_feiyongdlbh=580 
                            and i_xiaozhang<>20) sx201003 
                            left join 
                            (select '铜陵首创' stmc,75278+sum(i_zongyongl) i_kaizhangsl
                            from IBCS_TL.ZW_YINGYEZ201004 a
                            inner join IBCS_TL.ZW_YINGYEZ_ZB201004 b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201004 and b.i_jlzt=0 and i_feiyongdlbh=580
                            and i_xiaozhang<>20) sx201004 on sx201004.stmc=sx201003.stmc 
                            left join 
                            (select '铜陵首创' stmc,142742+sum(i_zongyongl) i_kaizhangsl
                            from IBCS_TL.ZW_YINGYEZ201005 a
                            inner join IBCS_TL.ZW_YINGYEZ_ZB201005 b on a.i_feeid=b.i_feeid
                            where a.i_jlzt=0 and i_y*100+i_m=201005 and b.i_jlzt=0 and i_feiyongdlbh=58