# oracle 高效分页

jopen 8年前

## oracle高效分页

--建测试表

create table t_testrownum

( rid        number,      rvalue     varchar2(30))

--插入测试数据

begin

insert into t_testrownum values(1,  'aaaa');

insert into t_testrownum values(2,  'aaaa');

insert into t_testrownum values(3,  'aaaa1');

insert into t_testrownum values(4,  'aaaa');

insert into t_testrownum values(5,  'aaaa');

insert into t_testrownum values(6,  'aaaa');

insert into t_testrownum values(7,  'aaaa');

insert into t_testrownum values(8,  'aaaa4');

insert into t_testrownum values(9,  'aaaa');

insert into t_testrownum values(10, 'aaaa');

insert into t_testrownum values(11, 'aaaa');

insert into t_testrownum values(12, 'aaaa');

insert into t_testrownum values(13, 'aaaa5');

insert into t_testrownum values(14, 'aaaa');

insert into t_testrownum values(15, 'aaaa');

insert into t_testrownum values(16, 'aaaa');

insert into t_testrownum values(17, 'aaaa');

insert into t_testrownum values(18, 'aaaa8');

insert into t_testrownum values(19, 'aaaa');

insert into t_testrownum values(20, 'aaaa');

end;

select * from t_testrownum where rownum = 1; 返回结果集的第一条记录。那么select * from T where rownum = 2;应该返回结果集的第二条记录。可是实际上第二个查询语句不会返回任何记录，为什么呢？

select * from T where rownum >= 1 and rownum <= 5; 返回前5条记录，而

select * from T where rownum >= 2 and rownum <= 5;  无记录返回。

rownum = 1

for x in ( select * from T )

loop

if ( x satisifies the predicate )

then

OUTPUT the row

rownum = rownum + 1

end if;

end loop;

SELECT STATEMENT Optimizer Mode=CHOOSE          TABLE

ACCESS FULL      USDPD502.T_TESTROWNUM

SELECT STATEMENT Optimizer Mode=CHOOSE

COUNT STOPKEY

TABLE ACCESS FULL USDPD502.T_TESTROWNUM

rownum = 1

for x in ( select * from T )

loop

if ( x satisifies the predicate )

then

OUTPUT the row

rownum = rownum + 1

end if;

if  ( rownum已超出指定范围 )

then

跳出循环

end if;

end loop;

--语句1

select * t_testrownum a where rownum >= 11 and rownum <= 20;

--语句2

select *

from ( select a.*, rownum r from t_testrownum a )

where r>= 11 and r <= 20

--语句3

select *

from ( select a.*, rownum r

from t_testrownum a     where rownum <= 20)

where r>= 11

--语句4

select *

from ( select a.*, rownum r from t_testrownum a    where rownum <= 20   order by rvalue)

where r>= 11

--语句5

select *

from ( select a.*, rownum r from t_testrownum a    order by rvalue ）

where r>= 11 and r <= 20

--语句6

select *

from ( select b.*, rownum r

from ( select a.*

from t_testrownum a    order by rvalue    ) b

where rownum <= 20     )

where r>= 11

SELECT STATEMENT, GOAL = CHOOSE

VIEW  Object owner=USDPD502

COUNT STOPKEY

VIEW    Object owner=USDPD502

SORT ORDER BY STOPKEY

TABLE ACCESS FULL Objectowner=USDPD502 Objectname=T_TESTROWNUM

--语句7

select *

from ( select a.* from t_testrownum a      order by rvalue    )

where rownum <= 20

--语句8

select *

from ( select a.* from t_testrownum a    order by rvalue   )

where rownum <= 10

--语句9

select a.*, rownum r

from t_testrownum a

whererownum <= 10

orderby rvalue

--语句10

select a.*, rownum r

from t_testrownum a

whererownum <= 20

orderby rvalue

SELECT STATEMENT, GOAL = CHOOSE

SORT ORDER BY

COUNT STOPKEY

TABLE ACCESS FULL   Object owner=USDPD502   Object name=T_TESTROWNUM

--语句11

create index idx_testrownum_rvalue on t_testrownum(rvalue);

--语句12

select *

from ( select b.*, rownum r

from ( select a.*

from t_testrownum a

where rvalue > chr(1)     order by rvalue   ) b

where rownum <= 20  )

where r>= 11

SELECT STATEMENT, GOAL = CHOOSE

VIEW   Object owner=USDPD502

COUNT STOPKEY

VIEW    Object owner=USDPD502

TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM

INDEX RANGE SCAN Object owner=USDPD502 Object name=IDX_TESTROWNUM_RVALUE

-语句13

select *

from ( select b.*, rownum r

from ( select/*+index_desc(a idx_testrownum_rvalue)*/a.*

from t_testrownum a

where rvalue > chr(1)    order by rvalue desc    ) b

where rownum <= 20   )

where r>= 11

SELECT STATEMENT, GOAL = CHOOSE

VIEW Object owner=USDPD502

COUNT STOPKEY

VIEW Object owner=USDPD502

TABLE ACCESS BY INDEX ROWID Object owner=USDPD502 Object name=T_TESTROWNUM

INDEXRANGE SCAN DESCENDING Objectowner=USDPD502

1)        排序字段上的索引必须是升序索引，如果使用降序索引将导致升序排序时分页出现问题。（具体是什么原因我现在还没弄明白，如果有知道原因的可以指点一下）

2)        在分页开始记录数大于10000后，利用索引排序进行分页的性能反而不如直接排序分页的方式好。（如果字段是数字性，性能下降不大，如果是字符串则性能下降明显，这可能和字符串和数字的比较方式不同有关）