Sunday, September 12, 2021

Performance Tuning - ANY_VALUE aggregate function (example)






 

                In this post, we have tried to explain the performance impact of the miscellaneous function named ANY_VALUE with simple example.

How many times we have come across below scenario (display department_name) where we are forced to use aggregate function like MIN or MAX to retrieve a static value within the group. We did this to simply avoid adding additional columns in group by clause.

 Oracle came back with a new aggregate function named “ANY_VALUE()” in 21C. See its usage below.

Oracle official documentation:

ANY_VALUE returns a single non-deterministic value of expr. You can use it as an aggregate function.

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.

It ensures that there are no comparisons for any incoming row and also eliminates the necessity to specify every column as part of the GROUP BY clause. Because it does not compare values, ANY_VALUE returns a value more quickly than MIN or MAX in a GROUP BY query.

Source: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ANY_VALUE.html#GUID-A3C47D5E-B145-40B2-93D2-CA3BA65C2D81

Analysis:

The interesting part is performance boost which comes with ANY_VALUE comparing to traditional way of MIN or MAX to display a department name like above. Here is the simple script which helps to test the same.

DECLARE TYPE ltest IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER; ltab_test ltest; lt1 timestamp; lt2 timestamp; BEGIN dbms_output.put_line('Print Department name using MIN'); lt1 := SYSTIMESTAMP; dbms_output.put_line('Start Time:'||lt1); FOR i IN 1 .. 100 LOOP SELECT d.deptno ||'('||MIN(dname)||')' BULK COLLECT INTO ltab_test FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno GROUP BY d.deptno; END LOOP; lt2 := SYSTIMESTAMP; dbms_output.put_line('End Time:'||lt2); dbms_output.put_line('Elapsed Seconds: '||TO_CHAR(lt2-lt1, 'SSSS.FF')); dbms_output.put_line('Print Department name using ANY_VALUE'); lt1 := SYSTIMESTAMP; dbms_output.put_line('Start Time:'||lt1); FOR i IN 1 .. 100 LOOP SELECT d.deptno ||'('||any_value(dname)||')' BULK COLLECT INTO ltab_test FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno GROUP BY d.deptno; END LOOP; lt2 := SYSTIMESTAMP; dbms_output.put_line('End Time:'||lt2); dbms_output.put_line('Elapsed Seconds: '||TO_CHAR(lt2-lt1, 'SSSS.FF')); END;

 











Conclusion:

As I shown in above example script, when I used for lower volume data like SCOTT schema, ANY_VALUE didn’t gave the performance boost. In fact, it’s slower than MIN or MAX approach. However, with volume of data against which we perform GROUP BY increases the time taken for MIN/MAX is increased whereas time taken by ANY_VALUE is moreover same.

Hence, as per my analysis, ANY_VALUE is pretty much useful from readability perspective. However, performance improvement comes only when data volume is a high.

Hope it’s useful!!.

Any difference in thoughts, I welcome you put in comments. Its mutual learning J

1 Responses to “Performance Tuning - ANY_VALUE aggregate function (example)”

wisewitch said...
September 13, 2021 at 8:42 AM

Thank you for tip!!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.