Controls the placement of correlated subquery predicate operators within a query plan.
| Value | Action |
|---|---|
| -1 | Prefer the lowest possible location in the query plan, thereby placing the execution of the subquery as early as possible within the query. |
| 0 | Let the optimizer choose. |
| 1 | Prefer the highest possible location in the query plan, thereby delaying the execution of the subquery to as late as possible within the query. |
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.
For correlated subquery operators within a query, the IQ optimizer may have a choice of several different valid locations within that query’s plan. SUBQUERY_PLACEMENT_PREFERENCE allows you to override the optimizer’s cost-based decision when choosing the placement location. It does not override internal rules that determine whether a location is valid, and in some queries, there might be only one valid choice. If you set this option to a nonzero value, it affects every correlated subquery predicate in a query; it cannot be used to selectively modify the placement of one subquery out of several in a query.
This option is normally used for internal testing, and only experienced DBAs should use it.
The default setting of this option is almost always appropriate. Occasionally, Technical Support might ask you to change this value.