【oracle】探讨Oracle新分页方案的实现方案

在 https://www.cnblogs.com/heyang78/p/15754872.html 里我们见到了12c开始的offset新分页方案。

在执行解释计划是看到了row_number()的身影，当时我猜测此分析函数即offset语法的根本。

在下文里也试着实现一次，具体实现的语句就是:

select * from
(select a.*,row_number() over(order by name) as rn from emp7 a) tta
where 【start】<tta.rn and tta.rn<=【end】

在验证其正确性之前，首先创建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
(select a.*,row_number() over(order by name) as rn from emp7 a) tta
where 0<tta.rn and tta.rn<=5
        ID NAME                                             RN
---------- ---------------------------------------- ----------
         1 Andy                                              1
         2 Bill                                              2
         3 Cindy                                             3
         4 Douglas                                           4
         5 Eliot                                             5

第二页数据：

select * from
(select a.*,row_number() over(order by name) as rn from emp7 a) tta
where 5<tta.rn and tta.rn<=10
        ID NAME                                             RN
---------- ---------------------------------------- ----------
         6 Felix                                             6
         7 Green                                             7
         8 Hilter                                            8
         9 Jack                                              9
        10 Tom                                              10

第三页数据：

select * from
(select a.*,row_number() over(order by name) as rn from emp7 a) tta
where 10<tta.rn and tta.rn<=15
        ID NAME                                             RN
---------- ---------------------------------------- ----------
        11 Zerg                                             11
        17 吴用                                             12
        12 宋江                                             13
        15 李逵                                             14
        13 林冲                                             15

第四页数据：

select * from
(select a.*,row_number() over(order by name) as rn from emp7 a) tta
where 15<tta.rn and tta.rn<=20
        ID NAME                                             RN
---------- ---------------------------------------- ----------
        16 武松                                             16
        14 鲁智深                                           17

这四爷数据和其它三种分页方案的结果一致，足以证明其正确性。

在看看这种做法的效率。

先创建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

要验证的SQL：

select * from
(select a.*,row_number() over(order by name) as rn from emp8 a) tta
where 5500<tta.rn and tta.rn<=6500

这个和其它三种方案都是一致的。

跑解释计划：

explain plan for
select * from
(select a.*,row_number() over(order by name) as rn from emp8 a) tta
where 5500<tta.rn and tta.rn<=6500

select * from table(dbms_xplan.display);

结果：

SQL> 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         |      | 10000 |   566K|    19  (11)| 00:00:01
|

|*  1 |  VIEW                    |      | 10000 |   566K|    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("TTA"."RN">5500 AND "TTA"."RN"<=6500)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "NAME")<=6500)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

已选择20行。

SQL>

cost也是19，但冗余数据行没有及时甩脱，这个就留待日后提高吧。

END