前言
本篇文章讲解的主要内容是：怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询，所以你会不会认为分析函数一定比传统查询效率高？一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现？
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、怎样对SQL查询结果集分页比较好
工作中我们为了便于查询网页中的数据,经常会对从数据库查询到的结果集进行分页显示。
比如我现在有一个需求：
要求员工表(EMP的数据）按工资排序后一次只显示5行数据,下次再显示接下来的5行。
下面以第二页数据(6到10行）为例进行分页。
前面已讲过,要先排序,然后在外层才能生成正确的序号：
SQL> with t as/*先排序*/
2 (select sal, ename from emp where sal is not null order by sal),
3 t1 as/*取得排序后的序号,并过滤掉10行以后的数据*/
4 (select rownum as rn, sal, ename from t where rownum <= 10)
/*根据前面生成的序号过滤掉6行以前的数据*/
5 select * from t1 where rn >= 6
6 ;
RN SAL ENAME
---------- --------- ----------
6 1300.00 MILLER
7 1500.00 TURNER
8 1600.00 ALLEN
9 2450.00 CLARK
10 2850.00 BLAKE
SQL> 可能有朋友会感觉我这么嵌套的有点麻烦，我列举一下你们想的：
- 为什么不直接在内层应用条件
WHERE rownum<=10呢？
下面对比一下rownum的结果。
SQL> select rownum rnn,aa.*
2 from (
3 select rownum as rn ,sal, ename from emp where deptno=20 and sal is not null
4 order by sal
5 ) aa;
RNN RN SAL ENAME
---------- ---------- --------- ----------
1 1 800.00 SMITH
2 4 1100.00 ADAMS
3 2 2975.00 JONES
4 5 3000.00 FORD
5 3 3000.00 SCOTT
SQL> 可以看到,内层直接生成的rownum(rn)与sal的顺序不一样,要想得到正确的顺序就要先排序后取序号。
- 为什么不直接用
rownum<=10 and rownum>=6,而要分开写呢？
下面来看一下。
SQL> select * from emp where rownum>=6 and rownum <=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL> 如前面博客所述,因为rownum是一个伪列,需要取出数据后,rownum才会有值,在执行where rownum>=6时,因为始终没取前10条数据出来,所以这个条件就查询不到数据,需要先在子查询中取出数据,然后外层用WHERE rn >=6来过滤。
你也可以先用row_number()生成序号,再过滤,这样就只需要嵌套一次。
SQL>
SQL> select aa.*
2 from (
3 select row_number()over(order by sal) as rn ,sal, ename from emp where sal is not null
4 order by sal
5 ) aa
6 where rn >=6 and rn<=10;
RN SAL ENAME
---------- --------- ----------
6 1300.00 MILLER
7 1500.00 TURNER
8 1600.00 ALLEN
9 2450.00 CLARK
10 2850.00 BLAKE
SQL> 这个语句比较简单,但因为分页语句的特殊性,在调用PLAN时可能会受到分析函数的影响,有些索引或PLAN(如：first_rows)不能用。所以,在此建议大家使用第一种分页方式,把第一种分页方式当作模板,然后套用。
二、分析函数的使用限制及Merge用对场景对性能提升非常大
现有数据如下：
CREATE TABLE hotel(floor_nbr,room_nbr)AS
SELECT 1,100 FROM dual UNION ALL
SELECT 1,100 FROM dual UNION ALL
SELECT 2,100 FROM dual UNION ALL
SELECT 2,100 FROM dual UNION ALL
SELECT 3,100 FROM dual;现在有个需求：将floor_nbr编号更新为类似：101、102、201、202的数据。
我们可以用row_number重新生成编号,或许马上会有读者想到UPDATE语句。让我们来执行一下。
SQL> update hotel set room_nbr =(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr));
update hotel set room_nbr =(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr))
ORA-30483: window 函数在此禁用
SQL> 有人想用“可更新VIEW",语句如下：
SQL> update (
2 select rowid as rid,room_nbr,(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr)) as new_room_nbr
3 from hotel
4 )
5 set room_nbr=new_room_nbr;
update (
select rowid as rid,room_nbr,(floor_nbr*100+row_number()over(partition by floor_nbr order by floor_nbr)) as new_room_nbr
from hotel
)
set room_nbr=new_room_nbr
ORA-01732: 此视图的数据操纵操作非法这些方法都不可靠,当然,把UPDATE再嵌套一层也可以：
SQL> update hotel a
2 set room_nbr =
3 (select new_room_nbr
4 from (select rowid as rid,
5 room_nbr,
6 (floor_nbr * 100 + row_number()
7 over(partition by floor_nbr order by floor_nbr)) as new_room_nbr
8 from hotel) bb
9 where a.rowid = bb.rid);
5 rows updated
SQL> select * from hotel;
FLOOR_NBR ROOM_NBR
---------- ----------
1 101
1 102
2 201
2 202
3 301
SQL> rollback;
Rollback complete
SQL> 执行计划如下：
Plan Hash Value : 4147990906
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 30 | 28 | 00:00:01 |
| 1 | UPDATE | HOTEL | | | | |
| 2 | TABLE ACCESS FULL | HOTEL | 5 | 30 | 3 | 00:00:01 |
| * 3 | VIEW | | 5 | 125 | 4 | 00:00:01 |
| 4 | WINDOW SORT | | 5 | 30 | 4 | 00:00:01 |
| 5 | TABLE ACCESS FULL | HOTEL | 5 | 30 | 3 | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("BB"."RID"=:B1)这种方法虽然能成功,但由执行计划可以看到,子查询执行了5次,对HOTEL有5次全表扫描。那么如果表更大,是不是更慢？博主之前就经常遇到有同事写出类似update SQL，上线的时候可能跑一晚上也跑不出来。
另一个方法是用MERGE语句：
SQL> merge into hotel a
2 using (select rowid as rid,
3 room_nbr,
4 (floor_nbr * 100 + row_number()
5 over(partition by floor_nbr order by floor_nbr)) as new_room_nbr
6 from hotel) bb
7 on (a.rowid = bb.rid)
8 when matched then update set a.room_nbr = bb.new_room_nbr;
5 rows merged
SQL> select * from hotel;
FLOOR_NBR ROOM_NBR
---------- ----------
1 101
1 102
2 201
2 202
3 301
SQL> 执行计划如下：
Plan Hash Value : 1282884214
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 5 | 80 | 7 | 00:00:01 |
| 1 | MERGE | HOTEL | | | | |
| 2 | VIEW | | | | | |
| * 3 | HASH JOIN | | 5 | 220 | 7 | 00:00:01 |
| 4 | VIEW | | 5 | 190 | 4 | 00:00:01 |
| 5 | WINDOW SORT | | 5 | 30 | 4 | 00:00:01 |
| 6 | TABLE ACCESS FULL | HOTEL | 5 | 30 | 3 | 00:00:01 |
| 7 | TABLE ACCESS FULL | HOTEL | 5 | 30 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("A".ROWID="BB"."RID")通过PLAN可以看到,使用MERGE子查询只对hotel访问了一次,效率提高了很多。
三、隔行抽样
有时为了取样而不是查看所有的数据,要对数据进行抽样,前面介绍过选取随机行,这里将介绍隔行返回。
现在有下面需求：对员工表中的数据每隔一行返回一个员工。
为了实现这个目标,用求余函数mod即可,我们看一下mod的结果。
SQL> select *
2 from (select mod(rn, 2) as md, aa.*
3 from (select rownum as rn, a.*
4 from (select ename, empno, sal from emp order by ename) a) aa)
5 where md = 0;
MD RN ENAME EMPNO SAL
---------- ---------- ---------- ----- ---------
0 2 ALLEN 7499 1600.00
0 4 CLARK 7782 2450.00
0 6 JAMES 7900 950.00
0 8 KING 7839 5000.00
0 10 MILLER 7934 1300.00
0 12 SMITH 7369 800.00
0 14 WARD 7521 1250.00
7 rows selected通过这个函数,想间隔几行返回都可以实现。
总结
本篇博客主要给大家介绍了：
- 分析查询的一个小建议，可能大家平时为了方便，用row_number做分页的比较多，但是在有些场景，这个效率真的挺低。
- 我身边很多人都会认为分析函数比传统写法简单并且效率高，无论什么场景都一股脑用分析函数，但是很多时候都会出现效率低下的问题，究其原因类似第二种案例，也建议大家在实际开发过程中，注意下。
- 最后介绍的这个隔行抽样是某网友问我的一个某国企的面试题，也放上来啦。
