Controls the choice of algorithms for processing an IN subquery.
-3 to 3
0
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.
The IQ optimizer has a choice of several algorithms for processing IN subqueries. This option allows you to override the optimizer's costing decision when choosing the algorithm to use. It does not override internal rules that determine whether an algorithm is legal within the query engine.
IN_SUBQUERY_PREFERENCE is normally used for internal testing and for manually tuning queries that the optimizer does not handle well. Only experienced DBAs should use it. The only reason to use this option is if the optimizer seriously underestimates the number of rows produced by a subquery, and the hash object is thrashing. Before setting this option, try to improve the mistaken estimate by looking for missing indexes and dependent predicates.
Inform Sybase Technical Support if you need to set IN_SUBQUERY_PREFERENCE, as setting this option might mean that a change to the optimizer is appropriate.
Table 2-13 describes the valid values for this option and their actions.
Value |
Action |
|---|---|
0 |
Let the optimizer choose |
1 |
Prefer sort-based IN subquery |
2 |
Prefer vertical IN subquery (where a subquery is a child of a leaf node in the query plan) |
3 |
Prefer hash-based IN subquery |
-1 |
Avoid sort-based IN subquery |
-2 |
Avoid vertical IN subquery |
-3 |
Avoid hash-based IN subquery |
