JOIN_OPTIMIZATION option

Enables or disables the optimization of the join order.

ON, OFF

ON

DBA permissions are not required to set this option. Can be set temporary, for an individual connection, or for the PUBLIC group. Takes effect immediately.

When the JOIN_OPTIMIZATION option is ON, Sybase IQ optimizes the join order to reduce the size of intermediate results and sorts, and to balance the system load. When the option is OFF, the join order is determined by the order of the tables in the FROM clause of the SELECT statement.

JOIN_OPTIMIZATION should always be set ON.

The JOIN_OPTIMIZATION option controls the order of the joins, but not the order of the tables. To show the distinction, consider this example FROM clause with four tables:

FROM  A, B, C, D

By default, this FROM clause creates a left deep plan of joins that could also be explicitly represented as:

FROM  (((A, B), C), D)

If JOIN_OPTIMIZATION is turned OFF, then the order of these joins on the sets of tables is kept precisely as specified in the FROM clause. Thus A and B must be joined first, then that result must be joined to table C, and then finally joined to table D. This option does not control the left/right orientation at each join. Even with JOIN_OPTIMIZATION turned OFF, the optimizer, when given the above FROM clause, can produce a join plan that looks like:

FROM  ((C, (A, B)), D)

or

FROM  (((B, A), C), D)

or

FROM  (D, ((A, B), C))

In all of these cases, A and B are joined first, then that result is joined to C, and finally that result is joined to table D. The order of the joins remains the same, but the order of the tables appears different.

In general, if JOIN_OPTIMIZATION is turned OFF, you probably should use parentheses in the FROM clause, as in the above examples, to make sure that you get the join order you want. If you want to join A and B to the join of C and D, you can specify this join by using parentheses:

FROM ((A, B), (C, D))

Note that the above FROM clause is a different join order than the original example FROM clause, even though all the tables appear in the same order.

JOIN_OPTIMIZATION should be set to OFF only to diagnose obscure join performance issues or to manually optimize a small number of predefined queries. With JOIN_OPTIMIZATION turned OFF, queries can join up to 128 tables, but might also suffer serious performance degradation.