ANY_VALUE Function in Oracle 21c

ANY_VALUE is a new function introduced in Oracle 21c, and it is also available in the later Release Updates of Oracle 19c (i.e., from 19.8 onward). In this article, we will get familiar with this function.

The following query is executed in pdb1 and is intended to determine how many datafiles each tablespace has:

select t.ts#, t.name, count(*) "Tedad_DataFile"
  from v$datafile d, v$tablespace t
 where t.ts# = d.ts#
 group by t.ts#, t.name;

As you can see, in the above query, both columns ts# and name are included in the GROUP BY clause. However, omitting the name column from the GROUP BY clause does not change the result, but Oracle does not allow this:

ORA-00979: not a GROUP BY expression

Prior to Oracle 21c, to work around this limitation, we could use aggregate functions like MIN or MAX:

select t.ts#, min(t.name), count(*) "Tedad_DataFile"
  from v$datafile d, v$tablespace t
 where t.ts# = d.ts#
 group by t.ts#

However, in Oracle version 21c, the ANY_VALUE function has been introduced for this very purpose — to improve code readability and potentially even performance. This function returns the first non-null value it encounters:

select t.ts#, any_value(t.name), count(*) "Tedad_DataFile"
  from v$datafile d, v$tablespace t
 where t.ts# = d.ts#
 group by t.ts#