JOIN_OPTIMIZATION Option

ON, OFF

ON

Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. 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.

When JOIN_OPTIMIZATION is ON, SAP 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.

JOIN_OPTIMIZATION 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.