Global joins preferred over local joins in case of many joined tables
Details
| Detail name | Value |
|---|---|
| Changelog Number | 9425 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.2.0, Exasol 6.1.3 |
| Fix Versions | Exasol 7.0.0, Exasol 6.2.5 |
| Resolution Date | 2020-02-20 |
Background
The join order optimizer determines the types of joins as well as the order of joins in a pipeline. In case of a pipeline with many joins, the join order search space becomes very large so that only a subset of the total space can be considered. The very best join order can be missed in such cases.
There was a problem therein that a more expensive global join was favored over a similar but cheaper local (replicated) join. The reason for it was that the global join was accidentally considered first in search space traversal.
Improvement
The search space traversal has been fixed to consider local joins before global joins.
Workaround
The optimizer behavior can be adjusted to prefer local over global joins using the database parameter "-cboCostSortMode=0".
