Allows the user to change the threshold at which the optimizer decides to transform scalar subqueries into joins.
0: Let optimizer cost model decide 1 - (232 -1): Values greater than 0 set the percentage of references at which to flatten
100
This option only applies to correlated scalar subqueries. DBA permissions are not required to set SUBUERY_FLATTENING_PERCENT. This option can be set by any user, at any level and 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 Sybase IQ query optimizer can convert a correlated scalar subquery into an equivalent join operation to improve query performance. The SUBQUERY_FLATTENING_PERCENT option allows the user to adjust the threshold at which this optimization occurs.
SCALAR_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.
