AGGREGATION_PREFERENCE Option
Controls the choice of algorithms for processing an aggregate.
-6 to 6
0
Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
For aggregation (GROUP BY, DISTINCT, SET functions) within a query, the SAP IQ optimizer has a choice of several algorithms for processing the aggregate. AGGREGATION_PREFERENCE lets you override the costing decision of the optimizer when choosing the algorithm. the option 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 SAP Technical Support, if you need to set AGGREGATION_PREFERENCE, as setting this option might mean that a change to the optimizer may be appropriate.
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 |
|
4 |
Prefer aggregation with a distinct/grouping sort |
|
5 |
Prefer aggregation with a sort if grouping columns include all the partitioning keys of a hash partitioned table. |
|
6 |
Prefer aggregation with a hash if grouping columns include all the partitioning keys of a hash partitioned table. |
-1 |
Avoid aggregation with a sort |
-2 |
Avoid aggregation using IQ indexes |
-3 |
Avoid aggregation with a hash |
|
-4 |
Avoid aggregation with a distinct/grouping sort |
|
-5 |
Avoid aggregation with a sort if grouping columns include all the partitioning keys of a hash partitioned table. |
|
-6 |
Avoid aggregation with a hash if grouping columns include all the partitioning keys of a hash partitioned table. |