oracle分页如何提高效率！

Question

公司有一个操作记录表，里面好像有上千万行数据。我的分页语句是这样写的：
Select *
  from (Select rownum As r_n, tbl.*
          from (select tt.TRANSFERID,
                       tt.sendpurseid,
                       tt.receivepurseid,
                       tt.sendamount,
                       tt.receiveamount,
                       tt.transferdate,
                       tt.reason,
                       tpc.pursechangereason,
                       tup.ownerid,
                       tup.pursetype,
                       tup.subid,
                       tup.balance,
                       tup.currencytype
                  from tblc_transfer tt
                 inner join tblc_pursechangereason tpc on tt.REASON =
                                                          tpc.INTVALUE
                 inner join tblc_user_purse tup on tt.receivepurseid =
                                                   tup.purseid
                                               and tt.SENDPURSEID = 2124
                                               where rownum <100
                 order by TRANSFERID desc) tbl
         where rownum <= 10) tbl2
 Where r_n >= 1
其中：TRANSFERID 是表transfer 的主键。这个语句的执行需要80多秒。而如果将order by TRANSFERID desc注释掉后，执行需要30秒左右！但是这样的耗时还是太多了！哪位大哥大姐能否帮帮小弟，有什么办法可以优化一下查询？谢谢！

Answer 1

select tt.TRANSFERID, 
                      tt.sendpurseid, 
                      tt.receivepurseid, 
                      tt.sendamount, 
                      tt.receiveamount, 
                      tt.transferdate, 
                      tt.reason, 
                      tpc.pursechangereason, 
                      tup.ownerid, 
                      tup.pursetype, 
                      tup.subid, 
                      tup.balance, 
                      tup.currencytype 
                  from tblc_transfer tt 
                inner join tblc_pursechangereason tpc on tt.REASON = 
                                                          tpc.INTVALUE 
                inner join tblc_user_purse tup on tt.receivepurseid = 
                                                  tup.purseid 
                                              and tt.SENDPURSEID = 2124 
                                              where rownum <100 
                order by TRANSFERID desc---应该是先在子查询里面排序，再用rownum<100,否则就是先取rownum<100，然后再排序1、楼主你的分页已经写死了，不是最好的分页方法，可以加载到控件中，在控件里面分页2、也可以用通用的分页存储过程来分页，google下应该有很多的

Answer 2

用 row_number() over()分析函数实现分页吧。比rownum效率高。
另外这么大的数据量，where条件上的列建索引吧

Answer 3

修改了一下，发现这样写效率有所提高。希望其他高手有更好的改进方法：
Select *
  from (select *
          from (Select rownum as r_n, tbl.*
                  from (select tt.TRANSFERID,
                               tt.sendpurseid,
                               tt.receivepurseid,
                               tt.sendamount,
                               tt.receiveamount,
                               tt.transferdate,
                               tt.reason,
                               tpc.pursechangereason,
                               tup.ownerid,
                               tup.pursetype,
                               tup.subid,
                               tup.balance,
                               tup.currencytype
                          from tblc_transfer tt
                         inner join tblc_pursechangereason tpc on tt.REASON =
                                                                  tpc.INTVALUE
                         inner join tblc_user_purse tup on tt.receivepurseid =
                                                           tup.purseid
                                                       and tt.SENDPURSEID = 2124
                         order by TRANSFERID desc) tbl) tbl2)
 where r_n <= 10 and r_n >= 1

Answer 4

试试看：
Select *
from (Select row_number() over(order by tbl.TRANSFERID desc) as r_n, tbl.*
                  from (select tt.TRANSFERID,
                              tt.sendpurseid,
                              tt.receivepurseid,
                              tt.sendamount,
                              tt.receiveamount,
                              tt.transferdate,
                              tt.reason,
                              tpc.pursechangereason,
                              tup.ownerid,
                              tup.pursetype,
                              tup.subid,
                              tup.balance,
                              tup.currencytype
                          from tblc_transfer tt
                        inner join tblc_pursechangereason tpc on tt.REASON =
                                                                  tpc.INTVALUE
                        inner join tblc_user_purse tup on tt.receivepurseid =
                                                          tup.purseid
                                                      and tt.SENDPURSEID = 2124
                        )tbl
) tbl1
where tbl1.r_n <= 10 and tbl1.r_n >= 1