Aggregate function access (COLUMN_FN_EVAL)
The access path that is chosen for the SQL statement determines when Db2 evaluates aggregate functions.
- If the ACCESSTYPE column value is I1, then a MAX or MIN function can be evaluated by one access of the index that is named in ACCESSNAME.
- For other values of ACCESSTYPE, the COLUMN_FN_EVAL column tells
when Db2 is
evaluating the aggregate functions.
- Value
- Functions are evaluated ...
- S
- During a sort to satisfy a GROUP BY clause
- R
- When data is being read from the table or index
- Blank
- After data retrieval and after any sorts
Generally, values of R and S are considered better for performance than a blank.
Care is required with use of the VARIANCE and STDDEV functions
because they are always evaluated late (that is, COLUMN_FN_EVAL is
blank). As a result, other functions in the same query block must
be evaluated late too. For example, in the following query, the SUM
function is evaluated later than it would be if the variance function
was not present:
SELECT SUM(C1), VARIANCE(C1) FROM T1;