CNDBA社区
先来说说需求：生产环境中有一张大表大概有400W+的数据记录。按照业务的需求，需要先对表中的某个字段进行group by分组后，再对记录集进行分页处理。因为可能涉及到别人公司业务机密，不好拿原案例出来分析，所以这里做了一个模拟案例，并且尽量将模型最简单化。
测试案例如下：
create table node(a number,b varchar2(100),c varchar2(10),d date);
insert into node
select rownum as a,
dbms_random.string('a', 50),
dbms_random.string('u', 3) as c,
to_date('2016-01-01', 'yyyy-mm-dd') + dbms_random.value * 360 as d
from dual
connect by level <= 4000000;
commit;
create index idx_node_c_a on node(c,a);
create index idx_node_c on node(c);
create index uk_node on node(a);
对T表生成400W条记录，字段A可以理解为表的业务主键，建立了唯一索引，字段C建立普通索引。从上面的生成算法来看，按字段C分组后的每个组平均记录数会有 400W/26/26/26 < 1000 条记录。 业务的需求是按照C字段分组后，取分组中a列最大的那一行数据。或者大家可以理解为获取每个分组里最新的记录，记录的新旧通过a列值大小来衡量。
根据上面的业务描述，我们很容易想到一个方案，将表node进行group分组max(a)获取该分组最大a值，通过a关联node表获取到的记录是符合条件的。然后将得到的记录集分页即可。
方案一 通过group by方式实现
SQL> select *
2 from (select t2.*,rownum rn
3 from (select max(a) a, c
4 from node
5 group by c) t1
6 left join node t2
7 on t1.a = t2.a
8 where rownum <= 10)
9 where rn >= 0;
已选择10行。
已用时间: 00: 00: 06.44
执行计划
----------------------------------------------------------
Plan hash value: 3600688307
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 37 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 940 | 37 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS OUTER | | 11 | 891 | 37 (0)| 00:00:01 |
| 4 | VIEW | | 11 | 143 | 4 (0)| 00:00:01 |
| 5 | HASH GROUP BY | | 17548 | 171K| 6003 (6)| 00:01:13 |
| 6 | TABLE ACCESS FULL | NODE | 3001K| 28M| 5737 (2)| 00:01:09 |
| 7 | TABLE ACCESS BY INDEX ROWID| NODE | 1 | 68 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | UK_NODE | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=0)
2 - filter(ROWNUM<=10)
8 - access("T1"."A"="T2"."A"(+))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
41764 consistent gets
24927 physical reads
0 redo size
1562 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
当然，也可以通过exists方式实现
方案二，exists的方式实现
SQL> select *
2 from (select rownum rn, t.*
3 from node t
4 where not exists (select null
5 from node t2
6 where t2.c = t.c
7 and t.a < t2.a)
8 and rownum <= 10)
9 where rn > 0;
已选择10行。
已用时间: 00: 00: 28.57
执行计划
----------------------------------------------------------
Plan hash value: 1216091477
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 940 | 26 (0)| 00:00:01 |
|* 1 | VIEW | | 10 | 940 | 26 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | NESTED LOOPS ANTI | | 11 | 858 | 26 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| NODE | 12 | 816 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_NODE_C_A | 144K| 1414K| 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">0)
2 - filter(ROWNUM<=10)
5 - access("T2"."C"="T"."C" AND "T"."A"<"T2"."A")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6088721 consistent gets
24084 physical reads
0 redo size
1562 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
还有，也可以使用分析函数实现
方案三，分析函数方式
select * from (select rownum rn, t.* from (select distinct first_value(a) over(partition by c order by a) as a, first_value(b) over(partition by c order by a) as b, c, first_value(d) over(partition by c order by a) as d from node) t where rownum <= 10) where rn > 0;
总结一下上面的几种实现方案，除了方案2，其他都用了分组算法，用原生的SQL处理，不可避免都会读取到表的所有数据，或者通过hash group by, 或者使用 sort group by,将数据按分组存放后，再获取分组里面满足条件的记录。 然后再是分页,这两种方案的执行时间都是5s+。唯一一个方案2，没有使用到分组算法，从执行结果来看，却是比其他两种方案更是惨不忍睹，方案2的执行时间用了28s。方案2是从段node的起始块开始，逐条检查每条记录是否满足，每次检查都会产生额外的开销，而满足条件的记录，通常又是在段node的最后，几乎每次都要检查到最后才会得到满足条件的记录，因此实际效果会比方案1,3差很多。
但是，拜托，我只是想要前面满足条件的几十条记录而已，有必火急火燎去检查完所有记录后，然后才给我吐出这区区几十条记录吗。但是看起来又好像没有更好的SQL方案能更加优雅地完成这个工作了，至少我是没有想到更好的处理这个工作的SQL了。好吧，既然原生的SQL方案如此丑陋，那我们完全可以自己编写程序来完成这个工作。那就是下面要说的数据流函数方案与管道函数方案。
我设想PLSQL引擎来处理这个事，应该这样处理才是更优雅的：假如分页条件为10，从段node的起始块开始检索，找到满足分页条件的10个分组记录(运气好的话，可能前面10条记录就可找到10个不同分组)，然后通过字段c(索引)去查找这10个分组中每个分组的最大的a列值，然后通过a列值(唯一索引)去获取node表的那条记录，而这条记录，就是我想要的记录。
好吧，来看看我的方案四是怎样完成的：
方案四，数据流函数/管道函数实现
先定义需要用到的集合数据类型：
SQL> create type ty_node is object(a number,b varchar2(200),c varchar2(200),d date); 2 / Type created SQL> create type ty_node_set is table of ty_node; 2 / Type created
编写数据流函数
/*------------
stopkey_num: 分页记录数
*/
create or replace function get_nodes_by_stream(stopkey_num number) return ty_node_set is cursor c is select * from node; c_entity c%rowtype; v_ty_node ty_node := ty_node(null,null,null,null); v_ty_node_set ty_node_set := ty_node_set(); v_stopkey_num number := stopkey_num; v_len number; --v_tmp_node ty_node(null,null,null,null); b boolean := false; begin if v_stopkey_num is null or v_stopkey_num<= 0 then raise_application_error(-20000,'传入下标参数为空或者下标小于1'); end if; v_ty_node_set.extend(); open c; loop fetch c into c_entity; exit when c%notfound or v_ty_node_set.last > v_stopkey_num ; v_ty_node.a := c_entity.a; v_ty_node.b := c_entity.b; v_ty_node.c := c_entity.c; v_ty_node.d := c_entity.d; b := false; for i in 1 .. v_ty_node_set.count loop if v_ty_node_set(i).c =v_ty_node.c then b := true; exit; end if; end loop; if b = false then select max(a) into v_ty_node.a from node where c = v_ty_node.c; --获取c类型中最大的a值 v_ty_node_set(v_ty_node_set.last) := v_ty_node; --if v_ty_node_set.last < v_stopkey_num then v_ty_node_set.extend(); --end if; end if; end loop; v_ty_node_set.delete(v_stopkey_num +1); return v_ty_node_set; end;
OK，用数据流函数来检索出符合我们要求的数据：
没错，consistent gets只有207， 执行所用的时间小到不在oracle的计量单位以内！
管道函数方式实现
编写代码：
create or replace function get_nodes_by_pipe(stopkey_num number) return ty_node_set pipelined is cursor c is select * from node; c_entity c%rowtype; v_ty_node ty_node := ty_node(null,null,null,null); v_ty_node_set ty_node_set := ty_node_set(); v_stopkey_num number := stopkey_num; v_len number; --v_tmp_node ty_node(null,null,null,null); b boolean := false; begin if v_stopkey_num is null or v_stopkey_num<= 0 then raise_application_error(-20000,'传入下标参数为空或者下标小于1'); end if; v_ty_node_set.extend(); open c; loop fetch c into c_entity; exit when c%notfound or v_ty_node_set.last > v_stopkey_num; v_ty_node.a := c_entity.a; v_ty_node.b := c_entity.b; v_ty_node.c := c_entity.c; v_ty_node.d := c_entity.d; b := false; for i in 1 .. v_ty_node_set.count loop if v_ty_node_set(i).c =v_ty_node.c then b := true; exit; end if; end loop; if b = false then select max(a) into v_ty_node.a from node where c = v_ty_node.c; v_ty_node_set(v_ty_node_set.last) := v_ty_node; v_ty_node_set.extend(); pipe row(v_ty_node); end if; end loop; return; end;
用管道函数来检索出符合我们要求的数据：
在这个案例里面，管道函数与数据流函数的效果差别不大。实际上对于要求返回大数据量的需求来说，一般推荐使用管道函数，因为它的工作方式是边执行边返回。跟SQL引擎的工作方式是类似的。当然，这个案例仅仅是个引子，案例的代码也不完善，例如分页功能的实现上只有stopkey，没有start_key。
看到改进后的威力了吧，大家对比前后几种方案的实际效果就知道了。实际应用上，如果我们发现某些SQL无论怎么改写都没有性能的改善，你甚至都怀疑ORACLE能不能支持你心目中理想的算法的时候，不妨使用管道函数、数据流函数的方式，自己写代码解决吧。
版权声明：本文为博主原创文章，未经博主允许不得转载。
管道函数 数据流函数 SQL优化
