Controls the choice of algorithms when processing joins.
-7 to 7
0
DBA permissions are not required to set JOIN_PREFERENCE. Can be set temporary, for an individual connection, or for the PUBLIC group. Takes effect immediately.
For joins within a query, the IQ optimizer has a choice of several algorithms for processing the join. JOIN_PREFERENCE allows you to override the optimizer’s cost-based decision when choosing the algorithm to use. It does not override internal rules that determine whether an algorithm is legal within the query engine. If you set it to any nonzero value, every join in a query is affected; you cannot use it to selectively modify one join out of several in a query.
This option is normally used for internal testing, and only experienced DBAs should use it. Table 2-14 describes the valid values for this option and their action.
Value |
Action |
|---|---|
0 |
Let the optimizer choose |
1 |
Prefer sort-merge |
2 |
Prefer nested-loop |
3 |
Prefer nested-loop push-down |
4 |
Prefer hash |
5 |
Prefer hash push-down |
6 |
Prefer prejoin |
7 |
Prefer sort-merge push-down |
-1 |
Avoid sort-merge |
-2 |
Avoid nested-loop |
-3 |
Avoid nested-loop push-down |
-4 |
Avoid hash |
-5 |
Avoid hash push-down |
-6 |
Avoid prejoin |
-7 |
Avoid sort-merge push-down |
