SUBQUERY_FLATTENING_PERCENT option

Function

Allows the user to change the threshold at which the optimizer decides to transform scalar subqueries into joins.

Allowed values

0: Let optimizer cost model decide 1 - (232 -1): Values greater than 0 set the percentage of references at which to flatten

Default

100

Scope

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.

Description

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.

See also

“SUBQUERY_FLATTENING_PREFERENCE option”