Controls the choice of algorithms for processing an IN subquery.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set 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.
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 Technical Support if you need to set IN_SUBQUERY_PREFERENCE, as setting this option might mean that a change to the optimizer is appropriate.
| 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 |