Oracle 21c introduced the "ANY_VALUE" function . And it is backported to 19c as well.
Per Oracle's documentation , this function is optimized .
<quote>
Use ANY_VALUE to optimize a query that has a GROUP BY clause.
ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.
<quote>
From what I see , is that it removes the need for a column to be in GROUP BY clause . I am still trying to find the correct use case for this function.
There are subtle differences in the execution plans for SQL without "ANY_VALUE" ( Listing 1) and for a SQL with "ANY_VALUE" function ( Listing 2) . Also , the function is non deterministic ( the return value can't be guranteed to return the same value) .
Here is the example test case . I welcome your comments
Code Examples:
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> Create table student as
2 SELECT
3 object_id AS student_id,
4 object_name AS student_name
5 FROM
6 all_objects;
Table created.
SQL> CREATE TABLE student_fees
2 AS
3 SELECT
4 student_id,
5 TO_NUMBER(to_char((sysdate - dbms_random.value(100, 10000)),
6 'YYYY')) AS enrolled_semester_year,
7 round(dbms_random.value(1000, 6000),
8 2) AS tuition_fees
9 FROM
10 student;
Table created.
SQL> alter table student add constraint student_pk primary key(student_id) ;
Table altered.
SQL> alter table student_fees add constraint student_fees_pk primary key(student_id, enrolled_semester_year) ;
Table altered.
SQL> alter table student_fees
2 add constraint student_fees_fk foreign key(student_id)
3 references student(student_id);
Table altered.
SQL> exec dbms_stats.gather_table_stats(user , 'STUDENT');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user , 'STUDENT_FEES') ;
PL/SQL procedure successfully completed.
Listing 1:
SQL without "ANY_VALUE" function.
SQL> Explain Plan for
2 SElect s.student_id , max(s.student_name) ,
3 sum(tuition_fees) from
4 student s , student_fees sf
5 Where s.student_id = sf.student_id
6 group by
7 s.student_id, s.student_name ;
Explained.
SQL> Select * from table(dbms_xplan.display);
Plan hash value: 2527482877
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68156 | 3327K| | 1019 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 68156 | 3327K| 4024K| 1019 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 68156 | 3327K| | 177 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| STUDENT_FEES | 68156 | 665K| | 53 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| STUDENT | 68156 | 2662K| | 123 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."STUDENT_ID"="SF"."STUDENT_ID")
16 rows selected.
Listing 2:
SQL with "ANY_VALUE" function.
SQL> Explain Plan for
2 SElect s.student_id , any_value(s.student_name) , sum(tuition_fees) from
3 student s , student_fees sf
4 Where s.student_id = sf.student_id
5 group by
6 s.student_id;
Explained.
SQL> Select * from table(dbms_xplan.display);
Plan hash value: 164295499
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68156 | 3327K| | 989 (1)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 68156 | 3327K| | 989 (1)| 00:00:01 |
| 2 | MERGE JOIN | | 68156 | 3327K| | 989 (1)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| STUDENT | 68156 | 2662K| | 661 (1)| 00:00:01 |
| 4 | INDEX FULL SCAN | STUDENT_PK | 68156 | | | 143 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 68156 | 665K| 2696K| 328 (1)| 00:00:01 |
| 6 | TABLE ACCESS FULL | STUDENT_FEES | 68156 | 665K| | 53 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S"."STUDENT_ID"="SF"."STUDENT_ID")
filter("S"."STUDENT_ID"="SF"."STUDENT_ID")
19 rows selected.
