Optimizer: Extended Outer Join Conversion

Details

Detail name Value
Changelog Number 10928
Type Improvement
Status Resolved
Fix Versions Exasol 7.1.0
Resolution Date 2021-08-03

Background

Previous versions of Exasol already converted outer joins to inner joins based on some filters given in the WHERE condition.

Example 1:

SELECT * 
FROM T1
LEFT JOIN T2 ON T1.a = T2.a
WHERE T2.x = 18

Improvements

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:

  • WHERE conditions that contain expressions with more than one table 
    Example 2: 
SELECT * 
FROM T1
LEFT JOIN T2 ON T1.a = T2.a
WHERE T1.x + T2.y = 18

Example 3:

SELECT * 
FROM T1
LEFT JOIN T2 ON T1.a = T2.a
WHERE T1.x = CASE WHEN T2.y>1 THEN T2.x ELSE T1.y END

JOIN conditions:

  • INNER JOINS that eliminate rows generated by the outer join semantic.
    Example 4: 
SELECT *
FROM fact
LEFT JOIN dim on fact.p = dim.p
INNER JOIN sel on sel.x = dim.x

Additional Details

Restrictions

Operations that handle NULL values (e.g string concatenation) are still excluded from this improvement. 
Example 5:

SELECT * 
FROM T1
LEFT JOIN T2 ON T1.a = T2.a
WHERE T1.x||T1.y = T2.x||T2.y

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

Disclaimer

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.

Changed behavior

The extra database parameter *{{-disableOuterJoinRemoval}}* has been removed. Instead, the new parameter *{{-outerJoinRemovalLevel}}* is introduced.