在 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