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;
8 个解决方案
#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
#2
支持
#3
1、首先,你这个语句只取第一页,当然可能较快。如果取最后一页呢,性能将大大下降。可能与排序后取优势就不是那么大了。
2、排序是一个很耗性能的一个操作,其性能要跟你的IO,CPU,内存分配,特别是PGA的分配有很大关系,如果PGA不足,将导致磁盘排序的话,将严重降低排序性能。
2、排序是一个很耗性能的一个操作,其性能要跟你的IO,CPU,内存分配,特别是PGA的分配有很大关系,如果PGA不足,将导致磁盘排序的话,将严重降低排序性能。
#4
-- 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;
#5
谢谢luoyoumou的见意,但这样改没什么作用,这个SQL的执行计划中没有全表扫描的现象,tangren说的有些道理,还请具体的描述一下怎么样优化排序。谢谢大家的回复!
#6
支持群猪
#7
加大sort_area_size可能会让你好受点。
排序的order by后的字段最好建上索引。
然后就是尽量避免大数据量结果集的排序。
#8
1.查询结果里不使用全部"*" 自己累点没什么
2.换大写 理由同上
3.rownum 换成 rownumber over () 可减少一层嵌套
4.里层FROM后的临时表“flow”放到检索条件内,使用EXISTS
5.oracle的运行顺序是由下向上的,所以根据条件范围改检索条件顺序
以上
2.换大写 理由同上
3.rownum 换成 rownumber over () 可减少一层嵌套
4.里层FROM后的临时表“flow”放到检索条件内,使用EXISTS
5.oracle的运行顺序是由下向上的,所以根据条件范围改检索条件顺序
以上
#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
#2
支持
#3
1、首先,你这个语句只取第一页,当然可能较快。如果取最后一页呢,性能将大大下降。可能与排序后取优势就不是那么大了。
2、排序是一个很耗性能的一个操作,其性能要跟你的IO,CPU,内存分配,特别是PGA的分配有很大关系,如果PGA不足,将导致磁盘排序的话,将严重降低排序性能。
2、排序是一个很耗性能的一个操作,其性能要跟你的IO,CPU,内存分配,特别是PGA的分配有很大关系,如果PGA不足,将导致磁盘排序的话,将严重降低排序性能。
#4
-- 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;
#5
谢谢luoyoumou的见意,但这样改没什么作用,这个SQL的执行计划中没有全表扫描的现象,tangren说的有些道理,还请具体的描述一下怎么样优化排序。谢谢大家的回复!
#6
支持群猪
#7
加大sort_area_size可能会让你好受点。
排序的order by后的字段最好建上索引。
然后就是尽量避免大数据量结果集的排序。
#8
1.查询结果里不使用全部"*" 自己累点没什么
2.换大写 理由同上
3.rownum 换成 rownumber over () 可减少一层嵌套
4.里层FROM后的临时表“flow”放到检索条件内,使用EXISTS
5.oracle的运行顺序是由下向上的,所以根据条件范围改检索条件顺序
以上
2.换大写 理由同上
3.rownum 换成 rownumber over () 可减少一层嵌套
4.里层FROM后的临时表“flow”放到检索条件内,使用EXISTS
5.oracle的运行顺序是由下向上的,所以根据条件范围改检索条件顺序
以上