原文地址:https://oracle-base.com/articles/21c/any_value-21c
原文作者:Tim Hall
ANY_VALUE 函數(shù)允許我們從 GROUP BY 子句中安全地刪除列,以減少任何性能開銷。
目錄
環(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á)式。




