【Orale】Oracle新分页方案 暨 三种分页方案之比较表格

前篇：https://www.cnblogs.com/heyang78/p/15750685.html  & https://www.cnblogs.com/heyang78/p/15751235.html

从12c起，Oracle也推出了不依赖于rownum的分页语句，其格式如下：

select * from emp7 order by name
offset 【start】 rows fetch next 【pageSize】 rows only

说明：

第一句：您需要根据自己的业务书写SQL语句

第二句绿色部分：固定格式，无需调整

start：起始行

pageSize：叶容量

相信大家也看出来了，这种分页方式比传统两种简洁多了，代价就是对数据库版本有些挑。

 

【数据验证】

为了验证新分页方式的有效性，还是对emp7进行分页操作，emp7表的建表语句是这样的：

create table emp7(
    id number(3),
    name nvarchar2(20),
    primary key(id)
)

数据这样充值：

insert into emp7(id,name) values(1,'Andy');
insert into emp7(id,name) values(2,'Bill');
insert into emp7(id,name) values(3,'Cindy');
insert into emp7(id,name) values(4,'Douglas');
insert into emp7(id,name) values(5,'Eliot');
insert into emp7(id,name) values(6,'Felix');
insert into emp7(id,name) values(7,'Green');
insert into emp7(id,name) values(8,'Hilter');
insert into emp7(id,name) values(9,'Jack');
insert into emp7(id,name) values(10,'Tom');
insert into emp7(id,name) values(11,'Zerg');
insert into emp7(id,name) values(12,'宋江');
insert into emp7(id,name) values(13,'林冲');
insert into emp7(id,name) values(14,'鲁智深');
insert into emp7(id,name) values(15,'李逵');
insert into emp7(id,name) values(16,'武松');
insert into emp7(id,name) values(17,'吴用');

业务代码还是：

select * from emp7 order by name

分页方案还是按五行一页来。

之后我们看

第一页：

select * from emp7 order by name
offset 0 rows fetch next 5 rows only;

        ID NAME
---------- ----------------------------------------
         1 Andy
         2 Bill
         3 Cindy
         4 Douglas
         5 Eliot

第二页：

select * from emp7 order by name
offset 5 rows fetch next 5 rows only;
        ID NAME
---------- ----------------------------------------
         6 Felix
         7 Green
         8 Hilter
         9 Jack
        10 Tom

第三页：

select * from emp7 order by name
offset 10 rows fetch next 5 rows only;
        ID NAME
---------- ----------------------------------------
        11 Zerg
        17 吴用
        12 宋江
        15 李逵
        13 林冲

第四页：

select * from emp7 order by name
offset 15 rows fetch next 5 rows only;
        ID NAME
---------- ----------------------------------------
        16 武松
        14 鲁智深

将每页数据和之前两种方案比较，我们可以发现每页的数据和顺序都是一样的，这说明三种方案在业务上都是可以确信的。

 

【效率比较】

新分页方案的效率如何呢，我们可以用之前验证传统两种方案的emp8表来用。

emp8表建表语句：

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

然后我们还是从5500起取1000行，这个条件是和前两种是一样的，于是SQL便是：

select * from emp8 order by name
offset 5500 rows fetch next 1000 rows only

对其执行解释计划：

explain plan for 
select * from emp8 order by name
offset 5500 rows fetch next 1000 rows only

select * from table(dbms_xplan.display);

结果：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2150022822

--------------------------------------------------------------------------------
-

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |  6500 |   571K|    19  (11)| 00:00:01
|

|*  1 |  VIEW                    |      |  6500 |   571K|    19  (11)| 00:00:01
|

|*  2 |   WINDOW SORT PUSHED RANK|      | 10000 |   439K|    19  (11)| 00:00:01
|

|   3 |    TABLE ACCESS FULL     | EMP8 | 10000 |   439K|    17   (0)| 00:00:01
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=6500 AND
              "from$_subquery$_002"."rowlimit_$$_rownumber">5500)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP8"."NAME")<=6500)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

已选择 21 行。

从上面可以看出，cost三者是一样的19，rows方面新方案则接近传统第二种方案，先甩掉3500行，再砍掉5500行，因此两者效率也相近。至于底层实现，从filter部分能看出，新方案的核心是用了分析函数row_number()对排序的结果设定行号，然后还是走子查询，分两次砍掉冗余数据，基本上是第二种方案的思路。

 

现在可以下结论了，具体的分页方式选择可以参考以下表格：

DB Version >=12c	<12c
新分页方案	传统方案二（效率稍高）	传统方案一
底层细节：分析函数rownumber() +子查询	底层细节：rownum +子查询	底层细节：rownum +子查询
select * from emp7 order by name offset 【start】 rows fetch next 【pageSize】 rows only	select * from (select tta.*,rownum as rn from ( select * from emp7 order by name ) tta where rownum<=【end】 ) ttb where ttb.rn>【start】	select * from ( select ta.*,rownum as rn from ( select * from emp7 order by name )ta )tb where 【start】<tb.rn and tb.rn<=【end】

说明：

红色部分请根据您的业务替换成需要的SQL语句

绿色部分是固定句式，基本不用修改

蓝色部分是控制显示行的，需要指定数字来控制具体页将显示的数据。

END

 PS：在此篇和前两篇：

https://www.cnblogs.com/heyang78/p/15750685.html

https://www.cnblogs.com/heyang78/p/15751235.html

里，我将之前的思路整理好，自己组织数据，验证业务，比较效率，探究底层，早已不是早年间拿来主义者的做派了。