ANY_VALUE in Oracle 21c

Die Situation

Sie wollen die Daten einer Mastertabelle vollständig anzeigen, plus aggregiert die Daten einer zugehörigen Child-Tabelle? Dafür gab es mehrere Wege:

• Man listet sämtliche Spalten der Mastertabelle  in der group by-Klausel auf
• Man arbeitet mit einer Unterabfrage
• Man listet nur eine Spalte der Mastertabelle  in der group by-Klausel auf und legt über alle anderen Spalten ein MIN oder MAX
   

Hier dargestellt für EMP und DEPT:

SELECT d.deptno,
       d.dname,
       d.loc,
       COUNT(e.empno)     AS angestellte,
       NVL(sum(e.sal), 0) AS gehaltssumme
  FROM dept d
       LEFT OUTER JOIN emp e ON d.deptno = e.deptno
 GROUP BY d.deptno, d.dname, d.loc
 ORDER BY 1;

SELECT d.deptno,
       d.dname,
       d.loc,
       NVL(e.angestellte, 0)   AS angestellte,
       NVL (e.gehaltssumme, 0) AS gehaltssumme
  FROM dept d
       LEFT OUTER JOIN
         (SELECT deptno,
                 COUNT (empno) AS angestellte,
                 SUM (sal)     AS gehaltssumme
            FROM emp
           GROUP BY deptno) e
       ON (d.deptno = e.deptno)
 ORDER BY 1;

SELECT d.deptno,
       MIN(d.dname)       AS dname,
       MAX(d.loc)         AS loc,
       COUNT(e.empno)     AS angestellte,
       NVL(sum(e.sal), 0) AS gehaltssumme
  FROM dept d
       LEFT OUTER JOIN emp e ON d.deptno = e.deptno
 GROUP BY d.deptno 
 ORDER BY 1;

Das Ergebnis ist immer das gleiche:

    DEPTNO DNAME          LOC           ANGESTELLTE GEHALTSSUMME
---------- -------------- ------------- ----------- ------------
        10 ACCOUNTING     NEW YORK                3         8750
        20 RESEARCH       DALLAS                  5        10875
        30 SALES          CHICAGO                 6         9400
        40 OPERATIONS     BOSTON                  0            0

ANY_VALUE

Analog zur Variante mit MIN und MAX gibt es mit Oracle 21c einen weiteren Weg: ANY_VALUE.  
Dabei handelt es sich ebenfalls um eine Gruppenfunktion, nur ist ihr Verhalten anders als bei allen altbekannten Gruppenfunktionen. 
 

SELECT d.deptno,
       ANY_VALUE (d.dname) AS dname,
       ANY_VALUE (d.loc)   AS loc,
       COUNT(e.empno)      AS angestellte,
       NVL(sum(e.sal), 0)  AS gehaltssumme
  FROM dept d
       LEFT OUTER JOIN emp e ON d.deptno = e.deptno
 GROUP BY d.deptno 
 ORDER BY 1;

Wofür ist ANY_VALUE gut und wie verhält es sich? Und warum sollte man eine neue Funktion verwenden, wenn der identische Weg auch schon mit altbekannten, bestens vertrauten Funktionen besteht?

Der Vorteil liegt - entsprechend großen Datenmengen vorausgesetzt - in einem Performance-Gewinn. Sowohl in der Variante mit MIN / MAX als auch bei Auflistung aller Spalten in der GROUP BY-Klausel muss Oracle Arbeit hineinstecken und wirklich gruppieren (GROUP BY) bzw. Werte vergleichen (MIN / MAX).

Bei ANY_VALUE dagegen nimmt Oracle einfach den ersten Wert innerhalb der Gruppe, den es findet („ANY“), und kontrolliert gar nicht weiter, ob es da noch andere, möglicherweise abweichende Werte gibt. Das spart Ressourcen.

Mit anderen Worten:  ANY_VALUE ist bei Lichte betrachtet keine „echte“ Gruppenfunktion, da ja gar nicht die ganze Gruppe betrachtet wird. Die Funktion ist nicht-deterministisch und daher nur geeignet, wenn eine 1:1-Beziehung besteht, wie hier zwischen deptno und dname.
 
Hinsichtlich NULL-Werten verhält sich ANY_VALUE wiederum so wie bei Gruppenfunktionen üblich: Sie werden generell nicht berücksichtigt; nur wenn gar kein Wert gefunden wird, der NOT NULL ist, wird NULL zurückgegeben. 
 

Fazit

Einziger Zweck von ANY_VALUE liegt in der Performance-Optimierung. Der Einsatz bleibt auf klar definierte Fragestellungen begrenzt.