Help

Observation: Many Column Functions

The number of column functions exceeds the threshold limit set for a single statement. DB2 evaluates column functions most efficiently during either data retrieval or sort time. The DB2 optimizer indicates in PLAN_TABLE column COLUMN_FN_EVAL at what time it evaluated column functions with values "R" (data retrieval) and "S" sort time.

The scenarios below describe DB2's most efficient evaluation of column functions. The description of each scenario includes:

  • Column function usage and required database objects, such as indexes

  • Column values in PLAN_TABLE which indicate DB2 has chosen a scenario

  • Criteria required to satisfy the scenario.

The scenarios follow:

Column function without GROUP BY: PLAN_TABLE column COLUMN_FN_EVAL equals "R".

  • Each column function operates on a single column.

  • DB2 evaluates all predicates at stage 1.

  • For joins, column functions operate on the inner table.

Column function without GROUP BY, with index for MIN or MAX: PLAN_TABLE column COLUMN_FN_EVAL equals " " and ACCESSTYPE equals "I1".

  • You coded only one column function and it operates on a single column.

  • DB2 evaluates all predicates at stage 1.

  • You created a composite index with the predicate columns defined as its primary columns, and the MIN/MAX column immediately following them. The index key on the MIN/MAX column should be ascending for MIN, and descending for MAX.

Column function with GROUP BY, with no sort required for the GROUP BY: PLAN_TABLE column COLUMN_FN_EVAL equals "R"

  • Each column function operates on a single column.

  • DB2 evaluates all predicates at stage 1.

  • For joins, column functions operate on the inner table.

Column function with GROUP BY, with sort required for the GROUP BY: PLAN_TABLE column COLUMN_FN_EVAL equals "S"

  • Each column function operates on a single column.

  • You did not specify DISTINCT for any of the column functions.

  • You specified DISTINCT for one of the column functions and an index containing the DISTINCT and GROUP BY columns is ordered in the proper sequence.

  • DB2 evaluates all predicates at either stage 1 or stage 2

  • For joins, column functions operate on any table.

Avoiding using the column functions STDDEV, STDDEV_SAMP, VAR, and VAR_SAMP if possible, because of their extensive unavoidable processing costs.