Oracle分页语句与性能

Oracle分页语句与性能
 
olts_trade.trd_item表共有1665条数据，使用sql developer进行性能分析(解释计划)。 
-------------------------------------------- 
Sql代码  
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 
走全表扫描 
-------------------------------------------- 
Sql代码  
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 
走主键索引 
-------------------------------------------- 
Sql代码  
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 
-------------------------------------------- 
 

上一个：Oracle中start with...connect by递归
下一个：Oracle恢复目录