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 search sequence, 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, or have the system calculate the algorithm from the database parameters JoinSearchTableThreshold4, JoinSearchTableThreshold9 and the number of tables in the join.

Specification of the Algorithm Using JoinSearchLevel

Value

Algorithm Name

Description

9

Permutated

The database system calculates all possible join sequences.

4

Transformer

The database system calculates multiple join sequences depending on the query structure

1

Greedy

The database system uses the simplest algorithm for the join sequence search

0

-

The database system determines the algorithm for the join sequence search (see below).

How Does the Database System Determine the Algorithm for the Join Search Sequence (JoinSearchLevel = 0)?

Number of Tables in the Join

Chosen Algorithm

n ≤ JoinSearchTableTreshold9

9

JoinSearchTableTreshold9 <n ≤ JoinSearchTableTreshold4

4

JoinSearchTableTreshold4 < n

1

Example

JoinSearchLevel = 0

JoinSearchTableThreshold4 = 16

JoinSearchTableThreshold9 = 4

5 tables are used in the join.

The database system uses the join sequence search level 4 (Transformer algorithm) becauseJoinSearchTableThreshold9 < 5 ≤ JoinSearchTableThreshold4.

More Information

Search Strategies for Joins

Database Administration, Special Database Parameters