oracle优化ht,order by如何优化

order by 字段ds_datetime已经有索引

SQL:

select *

from (select t.*, rownum ro

from (select (select mc from b_class where id = t.ywlx) ywlxmc,

t.*,

f_get_khkhmc(t.zkfx, t.ct_hyid) khmc,

f_get_hykh(t.zkfx, t.ct_hyid) hykh,

(select mc from ve_dept where bh = t.ds_deptid) ds_deptmc,

(case ywlx

when '100911' then

pkg_ps.f_ifbdh(t.ddbh, '100911')

when '100901' then

pkg_ps.f_ifbdh(t.ddbh, '100901')

when '100902' then

pkg_ps.f_ifbdh(t.ddbh, '100902')

else

'0'

end) ifbdhnull,

f_yy_return_tss_dd(t.ddbh) tss,

(select mc from ve_dept where bh = t.cj_deptid) cj_deptmc,

(case ywlx

when '100901' then

(select prints from kh_khdd where ddbh = t.ddbh)

when '100902' then

(select PRINGCS from ticket_return where id = t.ddbh)

when '100903' then

(select prints

from b_member_djsq

where sqdh = t.ddbh)

when '100904' then

(select prints from spy_cwq where id = t.ddbh)

when '100912' then

(SELECT prints FROM t_gqsqb where gqdh = t.ddbh)

end) prints,

(case ywlx

when '100901' then

(select version from kh_khdd where ddbh = t.ddbh)

when '100918' then

(select version from ht_tkd where tkdh = t.ddbh)

end) version,

(case ywlx

when '100901' then

(select sfgl from kh_khdd_extend where ddbh = t.ddbh)

when '100902' then

(select sfgl from ticket_return where id = t.ddbh)

when '100912' then

(select sfgl from t_gqsqb where gqdh = t.ddbh)

when '100911' then

(select sfgl from t_bxdd where bxddh = t.ddbh)

end) sfgl,

(case ywlx

when '100901' then

(select gldh from kh_khdd_extend where ddbh = t.ddbh)

when '100902' then

(select gldh from ticket_return where id = t.ddbh)

when '100912' then

(select gldh from t_gqsqb where gqdh = t.ddbh)

when '100911' then

(select gldh from t_bxdd where bxddh = t.ddbh)

end) gldh,

(case ywlx

when '100901' then

f_get_gldh(t.ddbh, '1')

when '100902' then

f_get_gldh(t.ddbh, '2')

when '100912' then

f_get_gldh(t.ddbh, '3')

when '100911' then

f_get_gldh(t.ddbh, '4')

end) allglddbh,

(case ywlx

when '100902' then

(select sffh from ticket_return where id = t.ddbh)

end) sffh,

(case ywlx

when '100902' then

(select tp_type from ticket_return where id = t.ddbh)

when '100911' then

(select ddlx from t_bxdd where bxddh = t.ddbh)

end) tp_type,

(case ywlx

when '100919' then

(select decode(zf_fkf,

'1',

0,

NVL(kh_gqfy, 0) + NVL(fwf, 0) -

NVL(zf_je, 0))

from train_gq

where gqdh = t.ddbh)

when '100918' then

(select decode(zfzt, '1', 0, ytje)

from ht_tkd

where tkdh = t.ddbh)

when '100917' then

(select decode(skf, '1', 0, sj_xsj)

from ht_khdd

where id = t.ddbh)

when '100916' then

(select decode(zf_fkf, '1', 0, ysje - zf_je)

from ly_fpgl

where id = t.ddbh)

when '100915' then

(select decode(zf_fkf, '1', 0, ysje)

from bc_khdd

where ddbh = t.ddbh)

when '100912' then

(select decode(zf_fkf, '1', 0, gqfy)

from t_gqsqb

where gqdh = t.ddbh)

when '100901' then

(select fysje from table(khddje(t.ddbh)))

when '100902' then

(SELECT sum(tp_custje)

from ticket_returnrecord

where tfid = t.ddbh

and tp_tpzt <> '7')

when '100905' then

(SELECT sum(DECODE(zf_fkf, '1', 0, ysje))

FROM train_ticket

WHERE ddbh = t.ddbh)

when '100906' then

(SELECT sum(DECODE(zf_fkf, '1', 0, tp_je))

FROM train_return_ddmxb

WHERE tpdh = t.ddbh

and tpzt <> '6')

when '100907' then

(SELECT sum(DECODE(tpzt_cz, '1', 0, tp_je_cz))

FROM train_return_ddmxb

WHERE tpdh = t.ddbh

and tpzt <> '6')

when '100911' then

(SELECT sum(DECODE(zf_fkf, '1', 0, pj_bxxjjsj))

FROM t_bxdd

WHERE bxddh = t.ddbh)

else

0

end) ysje

from ps_task t

where pslx = '1'

and (ps_zt = '6' or ps_zt = '7')

and zt = '1'

and DS_DATETIME_DATE >=

TO_DATE('2014-07-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

and DS_DATETIME_DATE <=

TO_DATE('2014-10-14 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

and ds_compid in (select distinct *

from the (select cast(f_ybf_strtoin(f_ybf_getcompid('GZJXD')) as

t_ybf_emnu)

from dual))

order by ds_datetime

) t

where rownum <= 50)

where ro > 0

有order by 的执行计划

RTX截图未命名1.png

(2.5 MB, 下载次数: 3)

2014-10-14 17:48 上传

RTX截图未命名2.png

(2.41 MB, 下载次数: 4)

2014-10-14 17:48 上传