首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > oracle >

一条sql语句,执行太慢了,请问下如何优化

2012-02-23 
一条sql语句,执行太慢了,请教下怎么优化一条sql语句,执行太慢了,请教下怎么优化。主要是里面有31条子查询,

一条sql语句,执行太慢了,请教下怎么优化
一条sql语句,执行太慢了,请教下怎么优化。主要是里面有31条子查询,子查询里面又有联合查询,所以才比较慢。
请教一下改怎么优化啊?
语句如下,由于csdn限制发帖字数,我只写了到as   d7,其实后面还有一直要到as   d31,所以不慢才怪呢,但是不知道该怎么优化一下呢
select   HRM_KQMONTH.*,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '01 '   and   PersonID=HRM_KQMONTH.personid))   as   d1,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '02 '   and   PersonID=HRM_KQMONTH.personid))   as   d2,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '03 '   and   PersonID=HRM_KQMONTH.personid))   as   d3,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '04 '   and   PersonID=HRM_KQMONTH.personid))   as   d4,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '05 '   and   PersonID=HRM_KQMONTH.personid))   as   d5,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '06 '   and   PersonID=HRM_KQMONTH.personid))   as   d6,((select   case   when   ctype= '04 '   then   (select   symbol   from   hrm_setdata   where   TID   =   'QJ '   and   did=hrm_kqDay.qtype)   else   (select   symbol   from   hrm_setdata   where   TID   =   'KQ '   and   did=hrm_kqDay.ctype)   end   from   hrm_kqDay   where   KSTATUS= 'T '   and   to_char(KDate, 'yyyy ')= '2007 '   and   to_char(KDate, 'mm ')= '08 '   and   to_char(kDate, 'dd ')= '07 '   and   PersonID=HRM_KQMONTH.personid))   as   d7,(select   SortID   from   HRM_PERSONNEL   where   UserID   =   HRM_KQMONTH.UserID)   as   SortID   from   HRM_KQMONTH   where   1=1   and   KYEAR=2007   and   KMONTH=8   and   (KSTATUS=   '3 '   or   KSTATUS=   '2 ')



[解决办法]
关注下,==,帮你看看
[解决办法]
看得我都晕了,帮你顶吧。
[解决办法]
把子查询用连接查询代替,

用decode进行判断
[解决办法]
我也看晕了.帮顶.
[解决办法]
把case when都去掉 放到程序去判断 还有那些嵌套的查询 都给拆开 一次出不来 多次查

热点排行