Fix Version/s: Exasol 7.1.0
Previous versions of Exasol already converted outer joins to inner joins based on some filters given in the WHERE condition.
Optimizer capabilities to convert outer joins into inner joins for improved query performance have been expanded.
The following additional conditions now allow for the conversion of outer joins to inner joins:
- WHERE conditions that contain expressions with more than one table
- INNER JOINS that eliminate rows generated by the outer join semantic.
Operations that handle NULL values (e.g string concatenation) are still excluded from this improvement.
Notes on Example 4
Without the improvement, Exasol's engine would process all the data of the left join just to filter out most of it in the following inner join (join order FACT >> DIM >> SEL).
With the improvement, the left join would be converted to an inner join,
- optionally allowing a different join order like (SEL>> DIM >> FACT), accessing data in FACT through an Index instead of scanning the whole table.
- likely reducing the amount of data earlier in the pipeline
Please note that actual changes in join ordering depend on data model, query structure and data statistics. Giving our optimizer more freedom does not always guarantee better results. Some data models may contain outer joins for the sole purpose of enforcing a "known good" join order. These may be negatively affected by this improvement.