oracle分页sql ，还有优化的余地吗？

Question

刚用p6spy + IronTrack监测了下应用，发现如下的oracle分页语句花费的时间比较长:

select *
  from (select t.*, rownum rn
          from (select t.*, 0 as cnt
                  from t_group t
                 where t_user_id = 6631717
                 order by ctime desc) t
         where rownum < 7)
 where rn >= 1

 

只在t_group的主键t_group_id字段上建索引，其他字段都没有建索引，

执行10，平均花费145毫秒，最多的时候用了687毫秒。

 

请问还有改进的余地吗？ 一般像这种sql调优，该如何进行呢，谢谢 。

 

Answer 1

这个看上去只是个按时间取前7条记录的SQL啊，有分页吗？最外层查询貌似没实际作用。

给t_user_id和ctime上加索引，然后看看执行计划

Answer 2

游标吧，像oracle这种顽固的家伙，是打死不出limit语法的。

Answer 3

t_user_id是number类型，刚在t_user_id上加了索引后，sql的执行时间少了一半。

Answer 4

@zhangyou1010: ctime再加上索引试试。

Answer 5

@艺术家: ctime上有些值是空的，也可以建索引吗？

Answer 6

@zhangyou1010: 空值的记录不会被索引啊

Answer 7

t_user_id 是 什么类型的？

 比较不匹配的数据类型也是难于发现的性能问题之一。
下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。 
select * from dept where dept_id = 900198; 
这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。
把SQL语句改为如下形式就可以使用索引 

select * from dept where dept_id = '900198';

--------------------------------------------------------------------------------------------------------------------------------------------

ctime 要加索引啊