|
Determines whether query processing is optimized towards a workload that is a mix of updates and reads or a workload that is predominantly read-based (OLAP).
Mixed, OLAP
Mixed
| PUBLIC role | For current user | For other users | |
|---|---|---|---|
| Allowed to set permanently? | Yes, with SET ANY SYSTEM OPTION | No | No |
| Allowed to set temporarily? | Yes, with SET ANY SYSTEM OPTION | No | No |
The optimization_workload option controls whether queries are optimized for a workload that is a mix of updates and reads or that is predominantly read-only.
If the option is set to Mixed (the default), the optimizer chooses query optimization algorithms appropriate for a workload that is a mixture of short inserts, updates, and deletes and longer running read-only queries.
If the option is set to OLAP, the optimizer chooses algorithms appropriate for a workload that consists for the most part of long-running queries, combined with batch updates. In particular, the optimizer may choose to use the Clustered Hash Group By query execution algorithm.
When the option is set to OLAP, the Clustered Hash Group By algorithm is enabled. If the option is set to Mixed (the default), it is disabled.
You can override any temporary or PUBLIC settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.