oracle rownum分页3中方式的比较

Oracle分页

 

①采用rownum关键字(三层嵌套)

 

SELECT * FROM(

  SELECT A.*,ROWNUM  num FROM

  (SELECT * FROM t_order)A

  WHERE

  ROWNUM<=15)

WHERE num>=5;--返回第5-15行数据

 

②采用row_number解析函数进行分页(效率更高)

 

SELECT xx.* FROM(

SELECT t.*,row_number() over(ORDERBY o_id)AS num

FROM t_order t

)xx

WHERE num BETWEEN 5AND 15;

 

--返回第5-15行数据

 

解析函数能用格式

 

函数() over(pertion by 字段 order by 字段);

 

Pertion 按照某个字段分区

 

Order 按照勒个字段排序

 

select * from (select t.*,row_number() over (order by id) a from ztemp t)s where

s.a between 0 and 10;

 

SELECT * FROM ( 

  SELECT  t.*,row_number() over(ORDER BY n_count DESC) r 

  FROM t_news t 

  ORDER BY t.n_count DESC 

) t 

WHERE r BETWEEN 4 AND 6 

 

--通用语法： 解析函数() over(partition by 字段  order by 字段) 

 

 

 

1.根据ROWID来分<!--more-->

 

select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from 

t_xiaoxi  order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 

 

select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from

t_xiaoxi  order by cid desc) where rownum<10000) where rn>9980) order by cid desc;

 

执行时间0.03秒

2.按分析函数来分

 

select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 

 

select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;

 

执行时间1.01秒

3.按ROWNUM来分

 

select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where 

rn>9980; 

 

select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where

rn>9980;

 

执行时间0.1秒

其中t_xiaoxi为表名称，cid为表的关键字段，取按CID降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录

1的效率最好，3次之，2最差 

转自百度文库