Background documentationJoinSearchLevel Database Parameter Locate this document in the navigation structure

 

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

This database parameter specifies the algorithm for the join sequence search, which itself determines how long the search takes and how many resources it uses. You can either specify which algorithm the database system should use directly or calculate this from the database parameters JoinSearchTableThreshold4, JoinSearchTableThreshold9 and the number of tables in the join.

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

  • 9: The database system calculates all possible join sequences (“permutated algorithm”).

  • 4: The database system calculates multiple join sequences depending on the query structure (“transformer algorithm”).

  • 1: The database system uses the simplest algorithm for the join sequence search (“greedy algorithm”).

If you enter 0 as the JoinSearchLevel, then the database system determines the algorithm for the join sequence search is specified as follows:

How does the database system define the algorithm for the join sequence search ?

Table number n of join

Algorithm for join sequence search

n ≤ JoinSearchTableTreshold9

9

JoinSearchTableTreshold9 <n ≤ JoinSearchTableTreshold4

4

JoinSearchTableTreshold4 < n

1

Example

JoinSearchLevel = 0

JoinSearchTableThreshold4 = 16

JoinSearchTableThreshold9 = 4

Five tables are used in a join.

The database system uses the join sequence search level 4 (“transformer algorithm”) because the following statement applies for the join:

JoinSearchTableThreshold9 < 5 ≤ JoinSearchTableThreshold4

More Information

Joins

Database Administration, Special Database Parameters