MAX_JOIN_ENUMERATION Option

Controls the maximum number of tables to be optimized for join order after optimizer simplifications have been applied.

Allowed Values

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.

Warning! Setting MAX_JOIN_ENUMERATION over the default value of 16 should only be done with caution, especially in the case of queries with bushy join relationships that can cause the amount of time required by the optimizer increase dramatically. In queries that use only a linear chain of join relationships, a MAX_JOIN_ENUMERATION setting of 64 can still provide reasonable optimization times.

Default

Scope

Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.

Description

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.