In Adaptive Server version 15.0 and later, join transitive closure is always on and cannot be disabled. The search engine uses the timeout mechanism to avoid excessive optimization time. Although the timeout setting no longer affects the actual use of transitive closure for the query processor, it can still affect the initial join order with which the search engine begins the permutation when the timeout occurs. You may find this discussion useful when you suspect that a suboptimal join order is being chosen at timeout.
By default, join transitive closure is not enabled at the server level, since it can increase optimization time. You can enable join transitive closure at a session level with set jtc on. The session-level command overrides the server-level setting for the enable sort-merge joins and JTC configuration parameter (available for versions of Adaptive Server earlier than 15.0).
For queries that execute quickly, even when several tables are involved, join transitive closure may increase optimization time with little improvement in execution cost. For example, with join transitive closure applied to this query, the number of possible joins is multiplied for each added table:
select * from t1, t2, t3, t4, ... tN where t1.c1 = t2.c1 and t1.c1 = t3.c1 and t1.c1 = t4.c1 ... and t1.c1 = tN.c1
For joins on very large tables, however, the additional optimization time involved in costing the join orders added by join transitive closure may result in a join order that greatly improves the response time.
Use set statistics time to see how long Adaptive Server takes to optimize the query. If running queries with set jtc on greatly increases optimization time, but also improves query execution by choosing a better join order, check the showplan, set option show_search_engine normal, or set option show_search_engine long output. Explicitly add the useful join orders to the query text. Run the query without join transitive closure, and get the improved execution time, without the increased optimization time of examining all possible join orders generated by join transitive closure.
You can also enable join transitive closure and save abstract plans for queries that benefit. If you then execute those queries with loading from the saved plans enabled, the saved execution plan is used to optimize the query, making optimization time extremely short.
See Performance and Tuning: Optimizer and Abstact Plans for more information on using abstract plans and configuring join transitive closure server-wide.
