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
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.
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)”
September 13, 2021 at 8:42 AM
Thank you for tip!!
Post a Comment