JOIN_PREFERENCE
Option
Controls the choice of algorithms when processing joins.
| 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 asymmetric sort-merge join |
| 7 | Prefer sort-merge push-down |
| 8 | Prefer asymmetric sort-merge push-down join |
| 9 | Prefer partitioned hash join if the join keys include all the partition keys of a hash partitioned table |
| 10 | Prefer partitioned hash-push down join if the join keys include all the partition keys of a hash partitioned table |
| 11 | Prefer partitioned sort-merge join if the join keys include all the partition keys of a hash partitioned table |
| 12 | Prefer partitioned sort-merge push-down join if the join keys include all the partition keys of a hash partitioned table |
| -1 | Avoid sort-merge |
| -2 | Avoid nested-loop |
| -3 | Avoid nested-loop push-down |
| -4 | Avoid hash |
| -5 | Avoid hash push-down |
| -6 | Avoid asymmetric sort-merge join |
| -7 | Avoid sort-merge push-down |
| -8 | Avoid asymmetric sort-merge push-down join |
| -9 | Avoid partitioned hash join if the join keys include all the partition keys of a hash partitioned table |
| -10 | Avoid partitioned hash-push down join if the join keys include all the partition keys of a hash partitioned table |
| -11 | Avoid partitioned sort-merge join if the join keys include all the partition keys of a hash partitioned table |
| -12 | Avoid partitioned sort-merge push-down join if the join keys include all the partition keys of a hash partitioned table |
0
Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.
Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.
For joins within a query, the SAP 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, but join condition hint strings can do so.
This option is normally used for internal testing or tuning of report queries, and only experienced DBAs should use it.
Simple equality join predicates can be tagged with a predicate hint that allows a join preference to be specified for just that one join. If the same join has more than one join condition with a local join preference, and if those hints are not the same value, then all local preferences are ignored for that join. Local join preferences do not affect the join order chosen by the optimizer.
This example requests a hash join:
AND (T.X = 10 * R.x, 'J:4')