Controls which algorithm to use for processing correlated subquery predicates.
-3 to 3
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. |
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
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.
See Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Search Conditions > Subqueries in Search Conditions > Disjunction of Subquery Predicates.
SUBQUERY_CACHING_PREFERENCE is normally used for internal testing by experienced DBAs only. It does not apply to IN subqueries.