In my experience, if query hits following error, which involes a multi table joins:
"SQL Anywhere Error -1005025: Query rejected as too complex. After join simplifications there were still 16 tables to be joined, which exceeds the current setting of Max_Join_Enumeration."
It is temprarily resolved by bumping the Max_Join_Enumeration option to higher value and the user tendency is to make this change permanent (PUBLIC) to avoid such problems in future.
Although it seems like an attractive solution to avoid such issues, it should not be changed/set at database (PUBLIC) level because it can lead to performance degradation for other queries.
This option "Max_Join_Enumeration" controls the maximum number of tables to be optimized for join order after optimizer simplifications have been applied.
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, the optimizer will attempt to simplify remaining tables for join order, if those simplifications fail to reduce the set of the joins that must be simultaneously considered to less than the current setting for MAX_JOIN_ENUMERATION, then the query will return an error.
Impact of increasing "Max_Join_Enumeration"
- The main impact is that the optimization will take longer.
- The more tables are involved in joins means additional resources necessary.
- Performance will be impacted because of the usage of more threads & temp cache for queries with bushy join relationships.(NOTE: bushy joins are considered by optimizer internally).
- In queries that use only a linear chain of join relationships, increasing MAX_JOIN_ENUMERATION setting can provide reasonable optimization times.
Therefore, it is not recommended to change the value of Max_Join_Enumeration option, but if ever changed, it should be tested thoroughly.
Changing it's value could avoid failure of certain queries, but optimization time could increase significantly for queries with join tables greater than Max_Join_Enumeration option value.
Regards
Gagan
