Controls the maximum number of tables to be optimized for join order after optimizer simplifications have been applied.
1 – 64
Each FROM clause is limited to having at most 64 tables. In practice, however, the effective limit on the number of tables in a FROM clause is usually much lower, and is based partially on the complexity of the join relationships among those tables. That effective limit is constrained by the setting for MAX_JOIN_ENUMERATION. The optimizer will attempt to simplify the set of join relationships within a FROM clause. If those simplifications fail to reduce the set of the joins that must be simultaneously considered to no more than the current setting for MAX_JOIN_ENUMERATION, then the query will return an error.
Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.
The query optimizer simplifies its optimization of join order by separate handling of both lookup tables (that is, nonselective dimension tables) and tables that are effective Cartesian products. After simplification, it proceeds with optimizing the remaining tables for join order, up to the limit set by MAX_JOIN_ENUMERATION. If this limit is exceeded, the query is rejected with an error. The user can then either simplify the query or try increasing the limit.
Normally, you should not need to change this value. If you do, Sybase recommends setting MAX_JOIN_ENUMERATION as a temporary or user option.