数据库版本:Oracle9i R9.2.0.6
两种方案:
1.
select * from
(select a.*,rownum rn from (select * from table) a)
where rn<=20 and rn>10;
2.
select * from
(select a.*,rownum rn from (select * from table) a where rownum <=20)
where rn>10;
当table表数据量增大时,二者的性能有明显差别。
实验:查询表第11-20共10条记录。
测试1:
表名:td_edu_member
表记录数:152
测试2:
表名:td_question_content
表记录数:366884
分别对两个表做表分析,收集最新统计信息。
测试1的两个执行计划如下:
测试1A
select * from
(select a.*,rownum rn from (select * from td_edu_member) a)
where rn <=20 and rn>10;
已选择10行。
已用时间: 00: 00: 01.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=152 Bytes=586
72)
1 0 VIEW (Cost=2 Card=152 Bytes=58672)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'TD_EDU_MEMBER' (Cost=2 Card=15
2 Bytes=6688)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1309 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
测试1B
已选择10行。
已用时间: 00: 00: 01.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=20 Bytes=7720
)
1 0 VIEW (Cost=2 Card=20 Bytes=7720)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'TD_EDU_MEMBER' (Cost=2 Card=15
2 Bytes=6688)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1309 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
通过对比,可以看到后者的方式一致性读取次数少于前者。
接着看大表的测试执行计划
测试2A:
SQL> select * from
2 (select a.*,rownum rn from (select * from td_question_content) a)
3 where rn<=20 and rn>10;
已选择10行。
已用时间: 00: 00: 14.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6760 Card=366884 Byt
es=1499454908)
1 0 VIEW (Cost=6760 Card=366884 Bytes=1499454908)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'TD_QUESTION_CONTENT' (Cost=676
0 Card=366884 Bytes=508501224)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
70345 consistent gets
69325 physical reads
0 redo size
11961 bytes sent via SQL*Net to client
6245 bytes received via SQL*Net from client
52 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
测试2B:
SQL> select * from
2 (select a.*,rownum rn from (select * from td_question_content) a where rownum <=20)
3 where rn>10;
已选择10行。
已用时间: 00: 00: 12.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6760 Card=20 Bytes=8
1740)
1 0 VIEW (Cost=6760 Card=20 Bytes=81740)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'TD_QUESTION_CONTENT' (Cost=676
0 Card=366884 Bytes=508501224)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
11961 bytes sent via SQL*Net to client
6245 bytes received via SQL*Net from client
52 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
经过对比发现,后者一致性读取次数及物理读取次数明显少于前者。另外,当获取记录逐渐靠后时,二者的查询计划差异逐渐缩小
实验在此省略。
通过以上判断,分页使用第二种方案效率较高,表数据量越大时,效果越明显。