Handling a simple state

A simple state is an aggregate state whose data type is the same as the aggregate argument. At any point in the iteration, a simple state contains only the partial result of the aggregation.

For example, the SUM built-in aggregate uses a simple state because its state contains only the partial result: the running total of the aggregate arguments. When the SUM aggregate operates on INTEGER aggregate arguments, it creates an integer partial sum for these arguments. Therefore, the data type of its aggregate argument and aggregate state is the same.

However, the AVG built-in aggregate does not use a simple state. Because it must divide the total by the number of values processed, its state requires two values: the running total and the number of arguments processed. When the AVG aggregate operates on INTEGER aggregate arguments, it creates an integer partial sum and an integer count for these arguments. Therefore, the data type of its aggregate argument (INTEGER) cannot be not the same as its aggregate state (two INTEGER values).

When a user-defined aggregate has a simple state, the following items apply:
  • The INIT aggregate support function does not need to allocate the aggregate state.

    In this case, the database server automatically performs the state management. If a UDA with a simple state does not include any other tasks that require an INIT support function (see Initialization tasks for the INIT aggregate support function), you can omit the INIT function from the definition of the UDA. The only possible state-management task you might want to perform in the INIT function is state initialization. For more information, see When to allocate and deallocate a state.

  • The COMBINE aggregate support function can just call the ITER support function.

    In this case, you do not have to create special code in the COMBINE function for the handling of parallel execution. Instead, the ITER function can perform the merge of two partial results. For more information, see Execute a user-defined aggregate in parallel queries.

  • The FINAL function is not required if the data type of the simple state is the same as the aggregate result.

    In this case, the aggregate argument, aggregate state, and aggregate result have the same data type. Such a user-defined aggregate is called a simple binary operator. If a UDA is a simple binary operator and does not include any other tasks that require a FINAL support function (see Post-iteration tasks for the final aggregate support function), you can omit the FINAL function from the definition of the UDA. For more information, see Return an aggregate result different from the aggregate state.

When a UDA does not include an INIT function, the database server takes the following state-management steps:
  • Allocates the PER_COMMAND state buffer to hold the aggregate state

    The database server can determine the size of the state buffer from the data type of the aggregate argument, which is passed into the user-defined aggregate.

  • Initializes this aggregate state to an SQL NULL value whose data type is the same as the aggregate argument
  • Passes the NULL-valued aggregate state to the first invocation of the ITER support function

    The ITER support function can just use the system-allocated state buffer to hold the state information. If you have some minor initialization tasks that you need to perform, the ITER function can check for a NULL-valued aggregate state on its first iteration. If the state is NULL, ITER can initialize the state to its appropriate value. In this way, you can perform minor state initialization without the overhead of a separate invocation of the INIT function.

When a UDA does not include a FINAL function, the database server passes the final state as the aggregate result of the user-defined aggregate.

The implementation on the SQSUM1 aggregate includes an INIT support function that initializes the aggregate state (see The INIT aggregate support function for SQSUM1 on INTEGER). However, because SQSUM1 has a simple state, this INIT function is not required. Instead, an ITER function can check for a NULL-valued state and perform the state initialization. The ITER support function of the SQSUM2 aggregate (see Aggregate support functions for SQSUM2 on INTEGER) shows this type of implementation.

The SUMSQ user-defined aggregate (The SUMSQ user-defined aggregate) also has a simple state and therefore does not require an INIT support function for state management.