有两种分页查询的实现方式，分别是传统的基于rownum的和基于rowid的方式，写法如下：
-----基于rownum的分页方式
SELECT *
FROM (
select t.*,rownum rn from(
SELECT *
FROM mytable
ORDER BY
id
) t
where rownum<=2800010
)
where rn>=2800001;
----基于rowid的分页方式
select /*+ ordered use_nl(t1,t2) */
t2.id,
t2.paginator,
t2.value
from (select rid
from (select r.rid,rownum rn
from(select rowid rid
from mytable t
order by id ) r
where rownum<=2800010)
where rn >=2800001) t1,
mytable t2
where t1.rid=t2.rowid;
分别执行上述的两种sql，根据10046的跟踪来看二者的执行计划及执行结果：
---基于rownum的方式
********************************************************************************
SELECT *
FROM (
select t.*,rownum rn from(
SELECT *
FROM mytable
ORDER BY
id
) t
where rownum<=2800010
)
where rn>=2800001
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.33 2.03 16864 16866 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.33 2.03 16864 16866 0 10
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
10 VIEW (cr=16866 pr=16864 pw=0 time=2037919 us)
2800010 COUNT STOPKEY (cr=16866 pr=16864 pw=0 time=2801613 us)
2800010 VIEW (cr=16866 pr=16864 pw=0 time=1597 us)
2800010 TABLE ACCESS BY INDEX ROWID MYTABLE (cr=16866 pr=16864 pw=0 time=1592 us) ----此处TABLE ACCESS BY INDEX ROWID执行了2800010次
2800010 INDEX FULL SCAN PK_MYTABLE_ID (cr=6100 pr=6099 pw=0 time=1389 us)(object id 10827)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 16864 0.07 0.43
SQL*Net message from client 2 0.00 0.00
********************************************************************************
---基于rowid的方式
********************************************************************************
select /*+ ordered use_nl(t1,t2) */
t2.id,
t2.paginator,
t2.value
from (select rid
from (select r.rid,rownum rn
from(select rowid rid
from mytable t
order by id ) r
where rownum<=2800010)
where rn >=2800001) t1,
mytable t2
where t1.rid=t2.rowid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.60 0.82 6100 6110 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.61 0.83 6100 6110 0 10
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27
Rows Row Source Operation
------- ---------------------------------------------------
10 NESTED LOOPS (cr=6110 pr=6100 pw=0 time=827099 us)
10 VIEW (cr=6100 pr=6099 pw=0 time=827082 us)
2800010 COUNT STOPKEY (cr=6100 pr=6099 pw=0 time=436 us)
2800010 VIEW (cr=6100 pr=6099 pw=0 time=431 us)
2800010 INDEX FULL SCAN PK_MYTABLE_ID (cr=6100 pr=6099 pw=0 time=427 us)(object id 10827)
10 TABLE ACCESS BY USER ROWID MYTABLE (cr=10 pr=1 pw=0 time=93 us) -------此处TABLE ACCESS BY INDEX ROWID执行了10次
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 6100 0.00 0.05
SQL*Net message from client 2 0.00 0.00
由此可以验证当总数据量较大且需要查询的数据靠后的时候，基于rowid的分页查询方式能大大降低TABLE ACCESS BY INDEX ROWID的次数，
逻辑读也相应的降低，从而提高了性能。
另外，从10046的跟踪文件来看，基于rownum的方式是一边读索引一边读表的方式，而基于rowid的方式是读完索引后，取得了最终的10条记录的rowid
后，再根据这10条记录的rowid去表中读取数据，从而大大减少了TABLE ACCESS BY INDEX ROWID的次数:
SQL> select object_name,object_id,object_type,owner from dba_objects where object_id in(10826,10827);
OBJECT_NAME OBJECT_ID OBJECT_TYPE OWNER
------------------------------ ---------- ------------------- ------------------------------
MYTABLE 10826 TABLE SCOTT
PK_MYTABLE_ID 10827 INDEX SCOTT
10046trace文件中的内容：
--基于rownum
END OF STMT
PARSE #1:c=0,e=301,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1289796965616819
BINDS #1:
EXEC #1:c=1000,e=228,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1289796965617237
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1289796965617312
WAIT #1: nam='db file sequential read' ela= 88 file#=1 block#=29978 blocks=1 obj#=10827 tim=1289796965618674
WAIT #1: nam='db file sequential read' ela= 31 file#=1 block#=30824 blocks=1 obj#=10827 tim=1289796965618915
WAIT #1: nam='db file sequential read' ela= 43 file#=1 block#=29979 blocks=1 obj#=10827 tim=1289796965619072
WAIT #1: nam='db file sequential read' ela= 26 file#=4 block#=71831 blocks=1 obj#=10826 tim=1289796965619263
WAIT #1: nam='db file sequential read' ela= 26 file#=4 block#=71832 blocks=1 obj#=10826 tim=1289796965619951
WAIT #1: nam='db file sequential read' ela= 39 file#=1 block#=29980 blocks=1 obj#=10827 tim=1289796965620340
WAIT #1: nam='db file sequential read' ela= 24 file#=4 block#=71828 blocks=1 obj#=10826 tim=1289796965620765
WAIT #1: nam='db file sequential read' ela= 39 file#=1 block#=29981 blocks=1 obj#=10827 tim=1289796965621375
WAIT #1: nam='db file sequential read' ela= 24 file#=4 block#=71829 blocks=1 obj#=10826 tim=1289796965621564
WAIT #1: nam='db file sequential read' ela= 25 file#=4 block#=71830 blocks=1 obj#=10826 tim=1289796965622178
WAIT #1: nam='db file sequential read' ela= 25 file#=1 block#=29982 blocks=1 obj#=10827 tim=1289796965622549
.........
.........
WAIT #1: nam='db file sequential read' ela= 8 file#=4 block#=47935 blocks=1 obj#=10826 tim=1289796967654942
WAIT #1: nam='db file sequential read' ela= 7 file#=4 block#=47951 blocks=1 obj#=10826 tim=1289796967655066
WAIT #1: nam='db file sequential read' ela= 7 file#=1 block#=36193 blocks=1 obj#=10827 tim=1289796967655108
WAIT #1: nam='db file sequential read' ela= 6 file#=4 block#=47967 blocks=1 obj#=10826 tim=1289796967655206
WAIT #1: nam='db file sequential read' ela= 15 file#=1 block#=36194 blocks=1 obj#=10827 tim=1289796967655368
WAIT #1: nam='db file sequential read' ela= 7 file#=4 block#=47983 blocks=1 obj#=10826 tim=1289796967655392
WAIT #1: nam='db file sequential read' ela= 6 file#=4 block#=47999 blocks=1 obj#=10826 tim=1289796967655510
WAIT #1: nam='db file sequential read' ela= 8 file#=1 block#=36195 blocks=1 obj#=10827 tim=1289796967655607
FETCH #1:c=1337796,e=2037921,p=16864,cr=16864,cu=0,mis=0,r=1,dep=0,og=1,tim=1289796967655641
---基于rowid
END OF STMT
PARSE #1:c=7999,e=10767,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1289797000119781
BINDS #1:
EXEC #1:c=0,e=105,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1289797000120168
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=9723 tim=1289797000120228
WAIT #1: nam='db file sequential read' ela= 40 file#=1 block#=29978 blocks=1 obj#=10827 tim=1289797000120442
WAIT #1: nam='db file sequential read' ela= 33 file#=1 block#=30824 blocks=1 obj#=10827 tim=1289797000120599
WAIT #1: nam='db file sequential read' ela= 18 file#=1 block#=29979 blocks=1 obj#=10827 tim=1289797000120684
WAIT #1: nam='db file sequential read' ela= 20 file#=1 block#=29980 blocks=1 obj#=10827 tim=1289797000121140
WAIT #1: nam='db file sequential read' ela= 28 file#=1 block#=29981 blocks=1 obj#=10827 tim=1289797000121635
WAIT #1: nam='db file sequential read' ela= 34 file#=1 block#=29982 blocks=1 obj#=10827 tim=1289797000122195
WAIT #1: nam='db file sequential read' ela= 24 file#=1 block#=29983 blocks=1 obj#=10827 tim=1289797000122692
WAIT #1: nam='db file sequential read' ela= 20 file#=1 block#=29984 blocks=1 obj#=10827 tim=1289797000123135
WAIT #1: nam='db file sequential read' ela= 21 file#=1 block#=29985 blocks=1 obj#=10827 tim=1289797000123565
WAIT #1: nam='db file sequential read' ela= 31 file#=1 block#=29986 blocks=1 obj#=10827 tim=1289797000124080
.........
.........
WAIT #1: nam='db file sequential read' ela= 5 file#=1 block#=36190 blocks=1 obj#=10827 tim=1289797000946586
WAIT #1: nam='db file sequential read' ela= 7 file#=1 block#=36191 blocks=1 obj#=10827 tim=1289797000946701
WAIT #1: nam='db file sequential read' ela= 13 file#=1 block#=36192 blocks=1 obj#=10827 tim=1289797000946877
WAIT #1: nam='db file sequential read' ela= 8 file#=1 block#=36193 blocks=1 obj#=10827 tim=1289797000947015
WAIT #1: nam='db file sequential read' ela= 7 file#=1 block#=36194 blocks=1 obj#=10827 tim=1289797000947140
WAIT #1: nam='db file sequential read' ela= 6 file#=1 block#=36195 blocks=1 obj#=10827 tim=1289797000947262
WAIT #1: nam='db file sequential read' ela= 10 file#=4 block#=47999 blocks=1 obj#=10826 tim=1289797000947364
FETCH #1:c=604907,e=827095,p=6100,cr=6100,cu=0,mis=0,r=1,dep=0,og=1,tim=1289797000947383
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-710933/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
natural join
请登录后发表评论
登录
全部评论
