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
--------------------------------------------
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。