SUBQUERY_FLATTENING_PREFERENCE Option
Allows a user to override the decisions of the optimizer when transforming (flattening) scalar or EXISTS subqueries into joins.
| 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. |
0
Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. 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. 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 SAP 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 SAP IQ.