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

ANY_VALUE 函數(shù)允許我們從 GROUP BY 子句中安全地刪除列，以減少任何性能開銷。

目錄

• 環(huán)境準(zhǔn)備
• 問題描述
• 解決方案：ANY_VALUE
• 注意事項(xiàng)

環(huán)境準(zhǔn)備

本文中的示例需要提前創(chuàng)建以下表和數(shù)據(jù)：

-- 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;

問題描述

我們需要返回一個(gè)部門列表，其中包含部門中的員工數(shù)量，因此我們可以使用 COUNT 聚合函數(shù)和 GROUP BY 子句來實(shí)現(xiàn)：

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 條件中，否則將會(huì)報(bào)錯(cuò)且無法執(zhí)行。由于我們并不關(guān)心 GROUP BY 中是否包含 DNAME 列，但是受制于語(yǔ)法必須得這樣做，同時(shí)在 GROUP BY 中添加額外的列也會(huì)造成不必要的開銷，為了避免這個(gè)問題，大家可能會(huì)使用 MIN 或者 MAX 函數(shù)。

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 函數(shù)造成了新的開銷。

解決方案：ANY_VALUE

在 Oracle 21c 中引入了 ANY_VALUE 聚合函數(shù)來解決這個(gè)問題。原理同樣是使用 MIN 或者 MAX 函數(shù)的方式，只是以 ANY_VALUE 進(jìn)行替代，它不進(jìn)行任何類型的比較，而是顯示它找到的第一個(gè)非 NULL 值，但是經(jīng)過內(nèi)部?jī)?yōu)化可以做到最大幅度減少聚合函數(shù)的開銷。

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>

所以現(xiàn)在我們可以減少 GROUP BY 中附加列的開銷，而不必添加 MIN 或 MAX 函數(shù)的開銷。

注意事項(xiàng)

• 它具有不確定性，所以不要在預(yù)設(shè)場(chǎng)景外使用此函數(shù)。
• 數(shù)據(jù)量小的情況下，我們可能無法觀測(cè)到性能的改進(jìn)，但隨著數(shù)據(jù)量的增加，GROUP BY 或者使用 MIN 和 MAX 函數(shù)的開銷必然超過 ANY_VALUE。
• 由于 MIN 和 MAX 函數(shù)是有確定意義的，如果代碼編寫者當(dāng)時(shí)僅出于從 GROUP BY 中排除非必要列，非代碼編寫者讀代碼時(shí)可能對(duì)于該寫法無法理解其用意，但是 ANY_VALUE 函數(shù)是非確定性的，因此使用它對(duì)任何其他開發(fā)人員來說都是一個(gè)明確的信息，即您正在使用它將列從 GROUP BY 中刪除。從支持的角度來看，這種額外的清晰度是一件好事。
• ANY_VALUE 函數(shù)支持 ALL 和 DISTINCT 關(guān)鍵字，但它們沒有任何功能。
• 表達(dá)式中的 NULL 值被忽略，因此 ANY_VALUE 將返回它找到的第一個(gè)非 NULL 值。如果表達(dá)式中的所有值都是 NULL，那么將返回 NULL 值。
• 它支持除 XMLTYPE、ANYDATA、LOB、文件或集合數(shù)據(jù)類型之外的任何數(shù)據(jù)類型，這會(huì)導(dǎo)致 ORA-00932 錯(cuò)誤。
• 與大多數(shù)函數(shù)一樣，輸入表達(dá)式可以是列、常量、綁定變量或由它們組成的表達(dá)式。