Allows a user to override the decisions of the optimizer when transforming (flattening) scalar or EXISTS subqueries into joins.
-3 to 3
-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
0
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.
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.
