SUBQUERY_FLATTENING_PERCENT Option
Allows the user to change the threshold at which the optimizer decides to transform scalar subqueries into joins.
| Value | Action |
|---|---|
| 0 | The optimizer cost model decides |
| 1 to (232 -1) | The percentage of references at which to flatten |
100
This option only applies to correlated scalar subqueries. 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 SUBUERY_FLATTENING_PERCENT to a non-default value, every scalar subquery predicate in the query is affected; this option cannot be used selectively for one scalar subquery predicate in a query.
The SAP IQ query optimizer can convert a correlated scalar subquery into an equivalent join operation to improve query performance. The SUBUERY_FLATTENING_PERCENT option allows the user to adjust the threshold at which this optimization occurs.
SUBUERY_FLATTENING_PERCENT represents a percent of estimated inner distinct values to estimated outer distinct values in a scalar subquery. As the estimated percent approaches 100%, the cost of evaluating the subquery as a join is likely to be smaller than using individual index probes. The value may be set larger than 100%, since the estimated inners are not guaranteed to be less than estimated outers.