Behavioral and Performance Analysis of Common SQL Aggregate Functions in Oracle

Aggregation functions such as COUNT, SUM, AVG, MAX, and MIN are basic tools for summarizing data in SQL. However, their behavior can be affected by several factors such as the occurrence of NULL values, the use of function expressions, constants, or user-defined functions (UDFs). This paper focuses on analyzing the behavior and performance of these functions in Oracle database system. The goal is to identify which query variants lead to performance losses, and which are efficient from the optimizer’s point of view. For this purpose, a test table has been constructed which contains different distributions of NULL and NOT NULL values. Instead of the internal Oracle metric COST, we propose our own Normalized Performance Index (NPI), which allows a fairer comparison of queries.