SUBQUERY_CACHING_PREFERENCE Option
Controls which algorithm to use for processing correlated subquery predicates.
| Value | Action |
|---|---|
| 1 | Use sort-based processing for the first subquery predicate. Other subquery predicates that do not have the same ordering key are processed using a hash table to cache subquery results. |
| 2 | Use the hash table to cache results for all subquery predicates when it is legal. If available temp cache cannot accommodate all of the subquery results, performance may be poor. |
| 3 | Cache one previous subquery result. Does not use SORT and HASH. |
| 0 | Let the optimizer choose. |
| -1 | Avoid using SORT. The IQ optimizer chooses HASH if it is legal. |
| -2 | Avoid using HASH. The IQ optimizer chooses SORT or cache-one value if it is legal. |
| -3 | Avoid using cache-one value. The IQ optimizer chooses either HASH or SORT if it is legal. |
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 correlated subquery predicates, the IQ optimizer offers a choice of caching outer references and subquery results that reduces subquery execution costs. SUBQUERY_CACHING_PREFERENCE lets you override the optimizer’s costing decision when choosing which algorithm to use. It does not override internal rules that determine whether an algorithm is legal within the query engine.
A setting of a non-zero value affects every subquery predicate in the query. A non-zero value cannot be used selectively for one subquery predicate in a query.
SUBQUERY_CACHING_PREFERENCE is normally used for internal testing by experienced DBAs only. It does not apply to IN subqueries.