[译] oracle database 21c 中的 any-m6米乐安卓版下载

原文地址：
原文作者：tim hall

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

目录

• 环境准备
• 问题描述
• m6米乐安卓版下载的解决方案：any_value
• 注意事项

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

-- 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 函数造成了新的开销。

在 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 错误。
• 与大多数函数一样，输入表达式可以是列、常量、绑定变量或由它们组成的表达式。