Oracle SQL分页语句与性能

Oracle SQL分页语句与性能
例如：
TRIP_BASEINFO 出差申请表共有1665条数据，使用sql developer进行性能分析。
--------------------------------------------------------------------

select * from   
(  
  select rownum rn, t.* from (  
    select trip.* from TRIP_BASEINFO trip  
  ) t where rownum <= 100  
) t1  where t1.rn >= 90;  

共用时间：cost = 11315
走全表扫描
----------------------------------------------------------------------

select trip1.* from   
(  
  select rownum rn, t.to_id from (  
    select trip.to_id from TRIP_BASEINFO trip  
  ) t where rownum <= 100  
) t1 inner join TRIP_BASEINFO trip1 on t1.n_id = trip1.n_id    
where t1.rn >= 90;  

以上to_id为主键
共用时间：cost = 171
走主键索引
[b]此种分页语句最佳。[/b]
-----------------------------------------------------------------------

select * from TRIP_BASEINFO where to_id in (  
    select to_id from (  
        select rownum rownum_, to_id from (  
            select to_ID from TRIP_BASEINFO  
        ) where rownum <= 100  
    ) where rownum_ >= 90  
)  

共用时间：cost = 172
使用了in
-------------------------------------------------------------------------