【Oracle】两种Oracle传统分页语句之效率比较
结论：方案二以不大的优势胜出，推荐使用如下SQL进行传统分页：
select * from (select tta.*,rownum as rn from ( your biz sql ) tta where rownum<=【end】 ) ttb where ttb.rn>【start】
至此，伸手党可以退散，较真党请继续往下看。
现有两种传统分页方案：
Oracle传统分页方案一：
select * from ( select ta.*,rownum as rn from ( your biz sql )ta )tb where 【start】<tb.rn and tb.rn<=【end】
介绍页面：
Oracle传统分页方案二：
select * from (select tta.*,rownum as rn from ( your biz sql ) tta where rownum<=【end】 ) ttb where ttb.rn>【start】
介绍页面：
为了验证哪种效率更高些，我们可以建个稍大的表：
create table emp8( id number(12), name nvarchar2(30), primary key(id) ) insert into emp8 select rownum,dbms_random.String('*',dbms_random.value(1,30)) from dual connect by level<10001
然后，设定我们的业务代码是：
biz sql: select * from emp8 order by name
查找范围限定在大于5500和小于等于6500的一千条记录上，两种方案的SQL就变成：
找5500~6500之间的数据 方案一： select * from ( select ta.*,rownum as rn from ( select * from emp8 order by name )ta )tb where 5500<tb.rn and tb.rn<=6500 方案二： select * from (select tta.*,rownum as rn from ( select * from emp8 order by name ) tta where rownum<=6500 ) ttb where ttb.rn>5500
对方案一执行解释计划是：
-------------------------------------------------------------------------------- Plan hash value: 1362420864 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10000 | 566K| 19 (11)| 00:00:01 | |* 1 | VIEW | | 10000 | 566K| 19 (11)| 00:00:01 | | 2 | COUNT | | | | | | | 3 | VIEW | | 10000 | 439K| 19 (11)| 00:00:01 | | 4 | SORT ORDER BY | | 10000 | 439K| 19 (11)| 00:00:01 | | 5 | TABLE ACCESS FULL| EMP8 | 10000 | 439K| 17 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TB"."RN"<=6500 AND "TB"."RN">5500) Note ----- - dynamic statistics used: dynamic sampling (level=2) 已选择 21 行。
方案二的解释计划是：
Plan hash value: 4133749571 -------------------------------------------------------------------------------- - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6500 | 368K| 19 (11)| 00:00:01 | |* 1 | VIEW | | 6500 | 368K| 19 (11)| 00:00:01 | |* 2 | COUNT STOPKEY | | | | | | | 3 | VIEW | | 10000 | 439K| 19 (11)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 4 | SORT ORDER BY STOPKEY| | 10000 | 439K| 19 (11)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP8 | 10000 | 439K| 17 (0)| 00:00:01 | -------------------------------------------------------------------------------- - PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TTB"."RN">5500) 2 - filter(ROWNUM<=6500) 4 - filter(ROWNUM<=6500) Note ----- - dynamic statistics used: dynamic sampling (level=2) 已选择 23 行。
好了，现在可以出比较表格了：
| 方案一 | 方案二 | |
select * from ( select ta.*,rownum as rn from ( select * from emp8 order by name )ta )tb where 5500 |
select * from (select tta.*,rownum as rn from ( |
|
| cost | 19 | 19 |
| rows | 在最后的筛选前，一直是一万行 | 到ttb时，便成了六千五百行 |
| 结论 | 效率稍低 | 效率稍高 |
| 解释 | 运行sql期间形成的两个view：ta和tb都是全结果集带着跑，最后才砍掉所有冗余数据。 | 运行sql期间形成的两个view：tta是全结果集，ttb是6500，砍了接近四成冗余数据后，最后再砍掉前面的5500行。 |
| 但是 | 劣势不明显 | 优势也不突出 |
到此，结论清晰了，一般可以选择方案二，但选方案一也不是不行，在中小应用中用户不细品未必能觉察出来。
当然，Oracle分页不止两种方案，有空我们继续探讨。
END