AGGREGATION_PREFERENCE option

Function

Controls the choice of algorithms for processing an aggregate.

Allowed values

-3 – 3

Default

0

Scope

DBA permissions are not required to set this option. Can be set temporary, for an individual connection, or for the PUBLIC group. Takes effect immediately.

Description

For aggregation (GROUP BY, DISTINCT, SET functions) within a query, the Sybase IQ optimizer has a choice of several algorithms for processing the aggregate. This AGGREGATION_PREFERENCE option lets you override the optimizer’s costing decision when choosing the algorithm. It does not override internal rules that determine whether an algorithm is legal within the query engine.

This option is normally used for internal testing and for manually tuning queries that the optimizer does not handle well. Only experienced DBAs should use it. Inform Sybase Technical Support if you need to set AGGREGATION_PREFERENCE, as setting this option might mean that a change to the optimizer is appropriate.

Table 2-5 describes the valid values and their actions for the AGGREGATION_PREFERENCE option.

Table 2-5: AGGREGATION_PREFERENCE values

Value

Action

0

Let the optimizer choose

1

Prefer aggregation with a sort

2

Prefer aggregation using IQ indexes

3

Prefer aggregation with a hash

-1

Avoid aggregation with a sort

-2

Avoid aggregation using IQ indexes

-3

Avoid aggregation with a hash