大数据量表查询语句order by 效率问题

Question

系统中需要对一张大数据量表做分页查询，表数据量在几千万行的级别，而且仍然会不断增加。系统为J2EE应用，TOMCAT5.0作为应用服务器，框架采用spring 2.0,使用springJdbcTemplate做数据库持久。数据库采用oracle10g。分页查询只操作单张表A。A表已经建成按过车时间gcsj的分区表。以下是SQL语句：
SELECT * FROM (SELECT ROWNUM no, a.* FROM (select a.* from a where hphm like ? and hpzl = ? and gcsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss')and gcsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') order by gcsj desc) a WHERE ROWNUM <=101) WHERE no >= 1
目前的问题是：当gcsj（date型）范围很大时，比如一个月，SQL语句（ select a.* from a where hphm like ? and hpzl = ? and gcsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss')and gcsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') order by gcsj desc  ）执行非常慢。如果去除order by 语句，在PL/SQL里执行速度还可以。但是由于必须做排序，所以当结果集非常大的时候速度很慢，几乎是没法查询得到。
请问对于大数据量表查询返回结果集非常大的应用场景下，对于order by 的应用是否有好的解决方案？小弟在此多谢了！！！

Answer 1

嵌套这么多select，还是考虑先建个试图吧。
增大sort_area_size 会提高排序速度

Answer 2

gcsj加索引了吗，或者是组合索引？

Answer 3

恩，业务上规避也是一条途径，我可以去限制大范围时间跨度查询，让结果集尽量少些。不知道从其他方面还有没有可以改进的地方了？ springJdbcTemplate的持久方式 是否合适？或者其他的数据库设计当中。。

Answer 4

如果gcsj有个单独索引，下面这句应该比较快才对，不管有没有order by
select a.* from a where gcsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss')
  and gcsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') order by gcsj desc

Answer 5

能否把gcsj改成VARCHAR2型呢？
比较大小的话，VARCHAR2型的时间也同样可以做比较。
少了几个to_date()转换，速度肯定会快很多。

Answer 6

gcsj 并没有单独索引。明天去建下，试试看~~ 目前的框架还不能去改GCSJ的类型。

Answer 7

增大sort_area_size排序区的大小SQL> show parameter sort_area_sizeNAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536(bytes, default value)
SQL>避免磁盘排序，其他就剩下业务上规避这个问题，加强查询条件，或者在页面提示用户查询时间过长

Answer 8

建议还是再gcsj字段上单独建个索引，组合索引需要合理使用才会起作用。
比如你在A表的a、b、c字段上建组合索引的话，它是讲究顺序的，假设：create index idx_A_abc on A(a,b,c);
那么你的语句中要包含第一个字段（a字段）才会起作用，如果只是用到了b、c字段，那么这个组合索引不会提高你sql语句执行的效率的。

Answer 9

建立gcsj的索引，在where里首先设置gcsj的条件，让SQL选中gcsj索引。不使用order by子句。

Answer 10

alter session set sort_area_Size=SomeBigNumber(say 104857600=100MB) on SQL*PLUS before running the query.

Answer 11

要看order by gcsj的时候有没有用那联合索引.
如果没有用到的话,还是新建一个单独的比较好.

Answer 12

把执行计划贴出来看看，另外你那个用rank好些吧，不用嵌套那么多的子查询。

Answer 13

当时间跨度比较大的时候，很可能会走全表扫描，此时若按时间对表进行分区，能提高查询速度
结果集很大的时候，排序也必然会造成性能的占用
用row_number()over()试试，可能会好一点