SUBQUERY_FLATTENING_PREFERENCE option

Function

Allows a user to override the decisions of the optimizer when transforming (flattening) scalar or EXISTS subqueries into joins.

Allowed values

-3 to 3

Value

Action

-3

Avoid flattening both EXISTS and scalar subqueries to a join operation.

-2

Avoid flattening a scalar subquery to a join operation.

-1

Avoid flattening an EXISTS subquery to a join operation.

 0

Allow the IQ optimizer to decide to flatten subqueries.

 1

Ignore cost flattening EXIST, if possible.

 2

Ignore cost flattening scalar, if possible.

 3

Ignore cost of both EXISTS and scalar subquery.

Default

0

Scope

DBA permissions are not required to set this option. SUBQUERY_FLATTENING_PREFERENCE can be set by any user at any level. This option takes effect immediately. If you set the option to a non-zero value, every subquery predicate in the query is affected; this option can not be used selectively for one subquery predicate in a query.

Description

The Sybase IQ optimizer may convert a correlated scalar subquery or an EXISTS or NOT EXISTS subquery into an equivalent join operation to improve query performance. This optimization is called subquery flattening. The SUBQUERY_FLATTENING_PREFERENCE option allows you to override the costing decision of the optimizer when choosing the algorithm to use.

Setting SUBQUERY_FLATTENING_PREFERENCE to 0 (allow the IQ optimizer to decide to flatten subqueries) is equivalent to setting the now deprecated FLATTEN_SUBQUERIES option to ON in earlier releases of Sybase IQ.

See also

“SUBQUERY_FLATTENING_PERCENT option”