一条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都去掉 放到程序去判断 还有那些嵌套的查询 都给拆开 一次出不来 多次查