【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】

介绍页面：https://www.cnblogs.com/heyang78/p/15750685.html

Oracle传统分页方案二：

select * from (select tta.*,rownum as rn from (
your biz sql
) tta where rownum<=【end】 ) ttb where ttb.rn>【start】

介绍页面：https://www.cnblogs.com/heyang78/p/15751235.html

 

为了验证哪种效率更高些，我们可以建个稍大的表：

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<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
cost	19	19
rows	在最后的筛选前，一直是一万行	到ttb时，便成了六千五百行
结论	效率稍低	效率稍高
解释	运行sql期间形成的两个view：ta和tb都是全结果集带着跑，最后才砍掉所有冗余数据。	运行sql期间形成的两个view：tta是全结果集，ttb是6500，砍了接近四成冗余数据后，最后再砍掉前面的5500行。
但是	劣势不明显	优势也不突出

到此，结论清晰了，一般可以选择方案二，但选方案一也不是不行，在中小应用中用户不细品未必能觉察出来。

当然，Oracle分页不止两种方案，有空我们继续探讨。

END