系列文章目录

文章目录

系列文章目录
前言
新增分页查询
- 重点关键词
- 案例
总结

前言

在12c之前的查询，如果要返回查询的前N条记录，或则对查询进行分页显示，需要使用rownum伪例，对查询结果进行排序。在oracle12C中引入了row_limiting_clause短语，可以方便的返回结果集中需要的记录。相关关键词为：offset、 row / rows、 fetch 、frist / next 、 rowcount、 precent 、only 。

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

新增分页查询

在12c之前的查询，如果要返回查询的前N条记录，或则对查询进行分页显示，需要使用rownum伪例，对查询结果进行排序。
在oracle12C中引入了row_limiting_clause短语，可以方便的返回结果集中需要的记录。

重点关键词

下面是row_limiting_clause短语的相关关键词及解释

案例

下面让我们通过一些案例来体验一下此新特性以及通过它们的执行计划深入理解下。
初始化数据：

create table EMP
(
  id   NUMBER,
  time DATE
);
begin 
  for i in 1..10000000
    loop
      insert into emp values(i,systimestamp-i/24/60/60);
      end loop;
commit;
      end;
insert into emp select * from emp where id between 7 and 10;
insert into emp select id,systimestamp from emp where id =8;
insert into emp select id,systimestamp from emp where id =9;

做下面查询：根据时间排序，取最近的数据中，排名在5-15的数据信息：

analyze table emp compute statistics;
alter system flush buffer_cache;
alter system flush shared_pool;
select *
  from (select rownum rn, id, time
          from (select id, time from emp order by time desc) order1) order2
 where rn between 5 and 15;
RN	ID	TIME
5	1	2018/7/12 14:36:17
6	2	2018/7/12 14:36:16
7	3	2018/7/12 14:36:15
8	4	2018/7/12 14:36:14
9	5	2018/7/12 14:36:13
10	6	2018/7/12 14:36:12
11	7	2018/7/12 14:36:11
12	7	2018/7/12 14:36:11
13	8	2018/7/12 14:36:10
14	8	2018/7/12 14:36:10
15	9	2018/7/12 14:36:09
Elapsed: 00:00:05.38

sql连续执行三次以后，消除硬解析等影响后，执行时间大概为5s多。执行计划为：
【SQL开发实战技巧】系列（三十九）：Oracle12C常用新特性☞新增分页查询-LMLPHP

如果在12C中实现上面案例的功能：

alter system flush buffer_cache;
alter system flush shared_pool;
select id, time
  from emp
 order by time desc offset 4 rows fetch first 11 rows with ties;
ID	TIME
1	2018/7/12 14:36:17
2	2018/7/12 14:36:16
3	2018/7/12 14:36:15
4	2018/7/12 14:36:14
5	2018/7/12 14:36:13
6	2018/7/12 14:36:12
7	2018/7/12 14:36:11
7	2018/7/12 14:36:11
8	2018/7/12 14:36:10
8	2018/7/12 14:36:10
9	2018/7/12 14:36:09
9	2018/7/12 14:36:09
Elapsed: 00:00:01.23

Sql执行多次后，平均耗时1.2s，执行计划如下：
【SQL开发实战技巧】系列（三十九）：Oracle12C常用新特性☞新增分页查询-LMLPHP

与12c之前的相比，12c的分页查询（耗时1.2s）比使用rownum伪例（耗时5.4s）效率高出4s左右。12C的分页查询，执行计划更简单，从11g和12c的Column Projection Information来看，12c的分页查询改写使用了分析函数。
看前面的查询结果，11g的rownum伪例取出来的数是11条，12c取出来的数是12条，这是为什么？再来分析下初始化时候的操作，在初始化表的时候，根据时间降序排序后距离目前日期最近的前15条数据中，id=7-10的数据我们重复插入了一遍，然后又插入了两条id相同，但是time不同的（id=8，9）数据.于是我们想要从结果集中查询的第5-15共11条数结果应该是下面标记的范围，为什么12c把id=9的重复的哪一条数据也加到5-15条的范围。这就是with ties的作用，会把与分页查询取到的最后一条记录重复的记录值也取出放在当前页面中，使用时应该注意。如果想返回同11g的哪个sql查询的结果集只需把with ties改成only。
现在知道，之所以12C的这类语法效率高是由于用分析函数改写了，如果此时手动用分析函数改写此sql再来看下效率：
先再来看下12C语法的效率和执行计划：

select id, time
  from emp
order by time desc offset 4 rows fetch first 11 rows with ties;

	ID TIME
---------- ------------
	 5 22-OCT-18
	 6 22-OCT-18
	 7 22-OCT-18
	 8 22-OCT-18
	 9 22-OCT-18
	10 22-OCT-18
	11 22-OCT-18
	12 22-OCT-18
	13 22-OCT-18
	14 22-OCT-18
	15 22-OCT-18

11 rows selected.

Elapsed: 00:00:02.04

然后改写：

select *
  from (select id, time, rank() over(order by time desc) as rn
          from emp)
 where  rn between 5 and 15;	
ID TIME 		RN
---------- ------------ ----------
	 5 22-OCT-18		 5
	 6 22-OCT-18		 6
	 7 22-OCT-18		 7
	 8 22-OCT-18		 8
	 9 22-OCT-18		 9
	10 22-OCT-18		10
	11 22-OCT-18		11
	12 22-OCT-18		12
	13 22-OCT-18		13
	14 22-OCT-18		14
	15 22-OCT-18		15

11 rows selected.
Elapsed: 00:00:02.02

发现其实效率差距不大，12C的top-N语句只是对使用rownum伪例类型的分页查询用分析函数进行了优化。

总结

提示：这里对文章进行总结：

例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

赵延东的一亩三分地

【SQL开发实战技巧】系列（三十九）：Oracle12C常用新特性☞新增分页查询

系列文章目录

文章目录

前言

新增分页查询

重点关键词

案例

总结