SUBQUERY_PLACEMENT_PREFERENCE option

Function

Controls the placement of correlated subquery predicate operators within a query plan.

Allowed Values

-1 to 1

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.

Default

0

Scope

Can be set for any scope, any user, takes immediate effect.

Description

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, Sybase Technical Support might ask you to change this value.