JoinSearchLevel Database Parameter
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.
|
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). |
|
Number of Tables in the Join |
Chosen Algorithm |
|---|---|
|
n ≤ JoinSearchTableTreshold9 |
9 |
|
JoinSearchTableTreshold9 <n ≤ JoinSearchTableTreshold4 |
4 |
|
JoinSearchTableTreshold4 < n |
1 |
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) because JoinSearchTableThreshold9 < 5 ≤ JoinSearchTableThreshold4.
Database Administration, Special Database Parameters