JOIN_SEARCH_LEVEL Database Parameter 

The special database parameter JOIN_SEARCH_LEVEL is used by the SQL Optimizer for processing joins.

Defines the algorithm for the join sequence search and thus determines the cost and time of the join sequence search. You can either directly specify the algorithm to be used or have it calculated based on the parameters JOIN_MAXTAB_LEVEL4, JOIN_MAXTAB_LEVEL9 and the number of tables in the join.

To specify the algorithm directly, enter one of the following values for JOIN_SEARCH_LEVEL:

·        9: All possible join sequences are calculated (permutation algorithm).

·        4: Various join sequences are calculated, depending on the query structure (transformer algorithm).

·        1: The simplest algorithm is used for the join sequence search (greedy algorithm).

If you enter 0 as the JOIN_SEARCH_LEVEL, then the algorithm for the join sequence search is specified as follows:

Table Number n of the Join	Algorithm for the Join Sequence Search
n ≤ JOIN_MAXTAB_LEVEL9	9
JOIN_MAXTAB_LEVEL9 <n ≤ JOIN_MAXTAB_LEVEL4	4
JOIN_MAXTAB_LEVEL4 < n	1

JOIN_SEARCH_LEVEL is set to 0.

JOIN_MAXTAB_LEVEL4 is set to 16.

JOIN_MAXTAB_LEVEL9 is set to 4.

Five tables are used for a join.

Join sequence search level 4 (transformer algorithm) is used for this join because JOIN_MAXTAB_LEVEL9 < 5 ≤ JOIN_MAXTAB_LEVEL4 applies.