oracle order by 优化

[小咪儿六六]

不加红色字体语句，查询很快，但是开发中有不能不加，有没方法优化
SELECT pid,pdepartment,ptype,pstation,pstarttime,pstoptime,pline,pposition,pequipment,pscope,preason
FROM ( SELECT pid,pdepartment,ptype,pstation,pstarttime,pstoptime,pline,pposition,pequipment,pscope,preason,rownum rn
FROM ( SELECT pid,pdepartment,ptype,pstation,pstarttime,pstoptime,pline,pposition,pequipment,pscope,preason
FROM (SELECT pid,pdepartment,ptype,pstation,pstarttime,pstoptime,pline,pposition,pequipment,pscope,preason
FROM Powinfcol
where 1=1 and pDepartment = '1' and pType = '1' and pStarttime >= to_date('2017-12-12','yyyy-mm-dd') and pStarttime <= to_date('2017-12-14','yyyy-mm-dd'))
ORDER BY pStarttime desc)
WHERE rownum <= 30)
WHERE rn>0

[碧水幽幽泉]

代码优化如下：

SELECT pid,
	   pdepartment,
	   ptype,
	   pstation,
	   pstarttime,
	   pstoptime,
	   pline,
	   pposition,
	   pequipment,
	   pscope,
	   preason
  FROM (select pid,
			   pdepartment,
			   ptype,
			   pstation,
			   pstarttime,
			   pstoptime,
			   pline,
			   pposition,
			   pequipment,
			   pscope,
			   preason,
			   row_number()over(order by pstarttime desc) rn
		  from powinfcol
		 where pdepartment = '1'
		   and ptype = '1'
		   and pstarttime >= to_date('2017-12-12', 'yyyy-mm-dd')
		   and pstarttime <= to_date('2017-12-14', 'yyyy-mm-dd'))
	  )
 where rn <= 30

[minsic78]

pStarttime两天的数据量很多？这个字段上没有索引？

[「已注销」]

不加红色部分是随机30条，加了红色部分是最近30条，所以要返回快的pStarttime 需要有索引，1楼的不适合做分页查询

[小咪儿六六]

引用 3 楼 游灬魂的回复:

不加红色部分是随机30条，加了红色部分是最近30条，所以要返回快的pStarttime 需要有索引，1楼的不适合做分页查询

谢谢你的建议，感谢你的回答

[小咪儿六六]

引用 1 楼 碧水幽幽泉的回复:

代码优化如下：

SELECT pid,
	   pdepartment,
	   ptype,
	   pstation,
	   pstarttime,
	   pstoptime,
	   pline,
	   pposition,
	   pequipment,
	   pscope,
	   preason
  FROM (select pid,
			   pdepartment,
			   ptype,
			   pstation,
			   pstarttime,
			   pstoptime,
			   pline,
			   pposition,
			   pequipment,
			   pscope,
			   preason,
			   row_number()over(order by pstarttime desc) rn
		  from powinfcol
		 where pdepartment = '1'
		   and ptype = '1'
		   and pstarttime >= to_date('2017-12-12', 'yyyy-mm-dd')
		   and pstarttime <= to_date('2017-12-14', 'yyyy-mm-dd'))
	  )
 where rn <= 30

感谢你的帮助，问题已经解决了

[小咪儿六六]

引用 2 楼 minsic78的回复:

pStarttime两天的数据量很多？这个字段上没有索引？

不是很多，就三千万条