一条返回大结果集SQL语句的优化

Question

下面一条SQL语句，结果集返回二十多万行的数据，在没有排序、去掉重复的语句下，运行时速度很快，但加上了排序后对其性能的影响很大，半天才出结果集，请大家谈谈应该怎样优化它select *
  from (select rownum as recordNum, complex.*
          from (select distinct 
                 form.APPID,
                 form.APPCODE,
                 form.APPLICANTID,
                 form.APPLICANTNAME,
                 form.fta_applyno,
                 passenger.STAFFID,
                 passenger.STAFFNAME,
                 passenger.surname,
                 passenger.givenname,
                 form.submitdate,
                 trip.departuredate,
                 form.STATUS,
                 owner.owner,
                 form.C_TR_NO,
                 form.FLOW_FLAG,
                 form.return_chinano,
                 d.managerid,
                 form.returndate
                  from air.T_APP_form form,
                       air.T_APP_trip trip,
                       air.T_APP_PASSENGER passenger,
                       air.t_app_dept d,
                       (select b.entry_id
                          from air.os_wfentry a, air.os_historystep b
                         where a.id = b.entry_id
                           and a.name = 'air_application'
                           and b.status = 'Finished'
                           and b.caller = 'JPB manager') flow,
                       air.os_currentstep owner
                 where form.APPID = trip.APPID
                   and form.APPID = passenger.APPID
                   and d.appid = form.appid
                   and trip.lineno = 0
                   and form.APPID = flow.entry_id
                   and d.type = 30
                   AND FORM.APPID = owner.entry_id(+)
                 order by trip.departuredate desc
                 ) complex
         where rownum <= 10)
 where recordNum >= 1;

Answer 1

排序本来就很耗资源...看下执行计划先。 返回20w数据，整个表的数据是多少？ 以前有人做个测试，当返回的结果集超过整个表的20%的时候，走全表扫描比走索引更快。 ------------------------------------------------------------------------------ 
Blog： http://blog.csdn.net/tianlesoftware 
网上资源： http://tianlesoftware.download.csdn.net 
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx 
DBA1 群：62697716(满); DBA2 群：62697977

Answer 2

1、首先，你这个语句只取第一页，当然可能较快。如果取最后一页呢，性能将大大下降。可能与排序后取优势就不是那么大了。
2、排序是一个很耗性能的一个操作，其性能要跟你的IO,CPU,内存分配，特别是PGA的分配有很大关系，如果PGA不足，将导致磁盘排序的话，将严重降低排序性能。

Answer 3

-- 1) 外层循环纯属多余，直接去掉!select rownum as recordNum, complex.*
          from (select distinct 
                 form.APPID,
                 form.APPCODE,
                 form.APPLICANTID,
                 form.APPLICANTNAME,
                 form.fta_applyno,
                 passenger.STAFFID,
                 passenger.STAFFNAME,
                 passenger.surname,
                 passenger.givenname,
                 form.submitdate,
                 trip.departuredate,
                 form.STATUS,
                 owner.owner,
                 form.C_TR_NO,
                 form.FLOW_FLAG,
                 form.return_chinano,
                 d.managerid,
                 form.returndate
                  from air.T_APP_form form,
                       air.T_APP_trip trip,
                       air.T_APP_PASSENGER passenger,
                       air.t_app_dept d,
                       air.os_currentstep owner
                 where exists ( select 1 
from air.os_wfentry a
where a.name = 'air_application'
  and exists (select 1 from air.os_historystep b
      where b.status = 'Finished' 
and b.caller = 'JPB manager' 
and b.entry_id = a.id )
  and a.id = form.APPID )
   and form.APPID = trip.APPID
                   and form.APPID = passenger.APPID
                   and d.appid = form.appid
                   and trip.lineno = 0
                   and d.type = 30
                   AND FORM.APPID = owner.entry_id(+)
                 order by trip.departuredate desc
                 ) complex
         where rownum <= 10;-------------------------------------------------------------------------------------------------------
-- 2) 表 air.os_wfentry 与 表air.os_historystep 相比较，哪个表中的记录行少？-- *(1) 如果  air.os_wfentry 表中的记录行比 air.os_historystep 表中的记录行要少，可以试试如下语句：
select rownum as recordNum, complex.*
          from (select distinct 
                 form.APPID,
                 form.APPCODE,
                 form.APPLICANTID,
                 form.APPLICANTNAME,
                 form.fta_applyno,
                 passenger.STAFFID,
                 passenger.STAFFNAME,
                 passenger.surname,
                 passenger.givenname,
                 form.submitdate,
                 trip.departuredate,
                 form.STATUS,
                 owner.owner,
                 form.C_TR_NO,
                 form.FLOW_FLAG,
                 form.return_chinano,
                 d.managerid,
                 form.returndate
                  from air.T_APP_form form,
                       air.T_APP_trip trip,
                       air.T_APP_PASSENGER passenger,
                       air.t_app_dept d,
                       air.os_currentstep owner
                 where exists ( select 1 
from air.os_wfentry a
where a.name = 'air_application'
  and exists (select 1 from air.os_historystep b
      where b.status = 'Finished' 
and b.caller = 'JPB manager' 
and b.entry_id = a.id )
  and a.id = form.APPID )
   and form.APPID = trip.APPID
                   and form.APPID = passenger.APPID
                   and d.appid = form.appid
                   and trip.lineno = 0
                   and d.type = 30
                   AND FORM.APPID = owner.entry_id(+)
                 order by trip.departuredate desc
                 ) complex
         where rownum <= 10;-- *(2) 如果  air.os_historystep  表中的记录行比 air.os_wfentry 表中的记录行要少，可以试试如下语句：select rownum as recordNum, complex.*
          from (select distinct 
                 form.APPID,
                 form.APPCODE,
                 form.APPLICANTID,
                 form.APPLICANTNAME,
                 form.fta_applyno,
                 passenger.STAFFID,
                 passenger.STAFFNAME,
                 passenger.surname,
                 passenger.givenname,
                 form.submitdate,
                 trip.departuredate,
                 form.STATUS,
                 owner.owner,
                 form.C_TR_NO,
                 form.FLOW_FLAG,
                 form.return_chinano,
                 d.managerid,
                 form.returndate
                  from air.T_APP_form form,
                       air.T_APP_trip trip,
                       air.T_APP_PASSENGER passenger,
                       air.t_app_dept d,
                       air.os_currentstep owner
                 where exists ( select 1 
  from air.os_historystep b 
         where b.status = 'Finished'
   and b.caller = 'JPB manager'
   and exists ( select 1 
  from air.os_wfentry a
 where a.name = 'air_application'
   and a.id = b.entry_id )
   and b.entry_id = form.APPID )
   and form.APPID = trip.APPID
                   and form.APPID = passenger.APPID
                   and d.appid = form.appid
                   and trip.lineno = 0
                   and d.type = 30
                   AND FORM.APPID = owner.entry_id(+)
                 order by trip.departuredate desc
                 ) complex
         where rownum <= 10;

Answer 4

谢谢luoyoumou的见意，但这样改没什么作用，这个SQL的执行计划中没有全表扫描的现象，tangren说的有些道理，还请具体的描述一下怎么样优化排序。谢谢大家的回复!

Answer 5

加大sort_area_size可能会让你好受点。
排序的order by后的字段最好建上索引。
然后就是尽量避免大数据量结果集的排序。

Answer 6

1.查询结果里不使用全部"*" 自己累点没什么
2.换大写 理由同上
3.rownum 换成 rownumber over () 可减少一层嵌套
4.里层FROM后的临时表“flow”放到检索条件内，使用EXISTS
5.oracle的运行顺序是由下向上的，所以根据条件范围改检索条件顺序
以上