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