两种分页SQL效率比较

数据库版本：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

经过对比发现，后者一致性读取次数及物理读取次数明显少于前者。另外，当获取记录逐渐靠后时，二者的查询计划差异逐渐缩小

实验在此省略。

通过以上判断，分页使用第二种方案效率较高，表数据量越大时，效果越明显。