sql优化，数据只有上万条

select * from (select a.seq_no,
       a.plat_date,
       a.host_dt,
       c.proj_name proj_code,
       d.proj_by_name acc_diff,
       b.brch_name brch_no,
       b.oper_name oper_no,
       a.payment_no,
       a.user_name,
       a.pay_money,
       decode(a.flag1, '1', '正常', '2', '当日返销', '3', '隔日返销') flag1,
       decode(a.flag2, '1', '未返销', '2', '已当日返销', '3', '已隔日返销') flag2,
       a.org_seq_no,
       a.cancel_date,
       a.ext_info,
       a.account,
       a.pound_type,
       a.pound_unit,
       a.pound_money
  from t_trans_detail_extend a,
       (select aa.brch_no, aa.brch_name, bb.oper_no, bb.oper_name
          from t_brch_mng aa, t_oper_mng bb
         where aa.brch_no = bb.brch_no) b,
       t_proj_code c,
       (select x.acc_diff, x.proj_by_name
          from t_acc_code x,
               (select t.acc_diff, min(t.proj_by_code) proj_by_code
                  from t_acc_code t
                 group by t.acc_diff) y
         where x.acc_diff = y.acc_diff
           and x.proj_by_code = y.proj_by_code) d
where a.brch_no = b.brch_no
   and a.oper_no = b.oper_no
   and c.proj_code(+) = a.proj_code
   and d.acc_diff(+) = a.acc_diff
   and a.brch_no in (select m.brch_no
                       from t_brch_mng m
                      where m.brch_pro = '00'
                     connect by prior m.brch_no = m.up_brch
                      start with m.brch_no = '40902200')
   and a.host_dt >= to_date('2012-04-08', 'yyyy-MM-dd')
   and a.host_dt <= to_date('2012-05-08', 'yyyy-MM-dd')) b  order by b.host_dt;

怎么提高性能呢？   主要是有个order by 把速度拖慢了很多，怎么优化这条sql

4 个解决方案

#1

用a表做主表现在主表中排序好

#2

order by拖慢的？何以见得
好像涉及好几张表，都是上万条的数据量？
贴出执行计划吧

#3

嵌套查询太多建议做个临时中间表
先把数据生成在做联合查询

#4

只有t_trans_detail_extend这一张主表里面有上万条数据，其它的表数据量没有多大才几十条，一楼的做法不错，采用了一楼的，谢谢咯

#1