Controls the maximum number of tables to be optimized for join order after optimizer simplifications have been applied.
1 – 32
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.
Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set 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.
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, set MAX_JOIN_ENUMERATION as a temporary or user option.