[译] Oracle Database 21c 中的 ANY_VALUE 聚合函数

原创 Lucifer三思而后行 2022-01-16

1590

原文地址：https://oracle-base.com/articles/21c/any_value-21c
原文作者：Tim Hall

ANY_VALUE 函数允许我们从 GROUP BY 子句中安全地删除列，以减少任何性能开销。

环境准备

本文中的示例需要提前创建以下表和数据：

-- drop table emp purge;
-- drop table dept purge;

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;

问题描述

我们需要返回一个部门列表，其中包含部门中的员工数量，因此我们可以使用 COUNT 聚合函数和 GROUP BY 子句来实现：

select d.deptno,
       d.dname,
       count(e.empno) as employee_count
from   dept d
       left outer join  emp e on d.deptno = e.deptno
group by d.deptno, d.dname
order by 1;

    DEPTNO DNAME          EMPLOYEE_COUNT
---------- -------------- --------------
        10 ACCOUNTING                  3
        20 RESEARCH                    5
        30 SALES                       6
        40 OPERATIONS                  0

SQL>

在 21C 以前的版本中，我们必须将所有非聚合列包含在 GROUP BY 条件中，否则将会报错且无法执行。由于我们并不关心 GROUP BY 中是否包含 DNAME 列，但是受制于语法必须得这样做，同时在 GROUP BY 中添加额外的列也会造成不必要的开销，为了避免这个问题，大家可能会使用 MIN 或者 MAX 函数。

select d.deptno,
       min(d.dname) as dname,
       count(e.empno) as employee_count
from   dept d
       left outer join  emp e on d.deptno = e.deptno
group by d.deptno
order by 1;

    DEPTNO DNAME          EMPLOYEE_COUNT
---------- -------------- --------------
        10 ACCOUNTING                  3
        20 RESEARCH                    5
        30 SALES                       6
        40 OPERATIONS                  0

SQL>


select d.deptno,
       max(d.dname) as dname,
       count(e.empno) as employee_count
from   dept d
       left outer join  emp e on d.deptno = e.deptno
group by d.deptno
order by 1;

    DEPTNO DNAME          EMPLOYEE_COUNT
---------- -------------- --------------
        10 ACCOUNTING                  3
        20 RESEARCH                    5
        30 SALES                       6
        40 OPERATIONS                  0

SQL>

这种写法使得我们可以从 GROUP BY 中拿掉 DNAME 列，但是新增了 MIN 或者 MAX 函数造成了新的开销。

解决方案：ANY_VALUE

在 Oracle 21c 中引入了 ANY_VALUE 聚合函数来解决这个问题。原理同样是使用 MIN 或者 MAX 函数的方式，只是以 ANY_VALUE 进行替代，它不进行任何类型的比较，而是显示它找到的第一个非 NULL 值，但是经过内部优化可以做到最大幅度减少聚合函数的开销。

select d.deptno,
       any_value(d.dname) as dname,
       count(e.empno) as employee_count
from   dept d
       left outer join  emp e on d.deptno = e.deptno
group by d.deptno
order by 1;

    DEPTNO DNAME          EMPLOYEE_COUNT
---------- -------------- --------------
        10 ACCOUNTING                  3
        20 RESEARCH                    5
        30 SALES                       6
        40 OPERATIONS                  0

SQL>

所以现在我们可以减少 GROUP BY 中附加列的开销，而不必添加 MIN 或 MAX 函数的开销。

注意事项

它具有不确定性，所以不要在预设场景外使用此函数。
数据量小的情况下，我们可能无法观测到性能的改进，但随着数据量的增加，GROUP BY 或者使用 MIN 和 MAX 函数的开销必然超过 ANY_VALUE。
由于 MIN 和 MAX 函数是有确定意义的，如果代码编写者当时仅出于从 GROUP BY 中排除非必要列，非代码编写者读代码时可能对于该写法无法理解其用意，但是 ANY_VALUE 函数是非确定性的，因此使用它对任何其他开发人员来说都是一个明确的信息，即您正在使用它将列从 GROUP BY 中删除。从支持的角度来看，这种额外的清晰度是一件好事。
ANY_VALUE 函数支持 ALL 和 DISTINCT 关键字，但它们没有任何功能。
表达式中的 NULL 值被忽略，因此 ANY_VALUE 将返回它找到的第一个非 NULL 值。如果表达式中的所有值都是 NULL，那么将返回 NULL 值。
它支持除 XMLTYPE、ANYDATA、LOB、文件或集合数据类型之外的任何数据类型，这会导致 ORA-00932 错误。
与大多数函数一样，输入表达式可以是列、常量、绑定变量或由它们组成的表达式。