Oracle分页话语与性能
Oracle分页语句与性能
olts_trade.trd_item表共有1665条数据,使用sql developer进行性能分析(解释计划)。
--------------------------------------------
cost = 11315
走全表扫描
--------------------------------------------
以上n_id为主键
cost = 171
走主键索引
--------------------------------------------
cost = 172
使用了in
--------------------------------------------
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
--------------------------------------------