Oracle分页语句与性能
olts_trade.trd_item表共有1665条数据，使用sql developer进行性能分析(解释计划)。
--------------------------------------------

select * from 
(
  select rownum rn, t.* from (
    select itm.* from OLTS_TRADE.trd_item itm
  ) t where rownum <= 100
) t1  where t1.rn >= 90;

cost = 11315
走全表扫描
--------------------------------------------

select itm1.* from 
(
  select rownum rn, t.n_id from (
    select itm.n_id from OLTS_TRADE.trd_item itm
  ) t where rownum <= 100
) t1 inner join OLTS_TRADE.trd_item itm1 on t1.n_id = itm1.n_id  
where t1.rn >= 90;

以上n_id为主键
cost = 171
走主键索引
--------------------------------------------

    select * from olts_trade.trd_item where n_id in (
        select n_id from (
            select rownum rownum_,n_id from (
                select n_ID from olts_trade.trd_item
            ) where rownum <= 100
        ) where rownum_ >= 90
    )

cost = 172
使用了in
--------------------------------------------