Uploaded image for project: 'EXASOL Roadmap'
  1. EXASOL Roadmap
  2. EXASOL-2811

Optimizer: Extended Outer Join Conversion


    • Type: Improvement
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Fix Version/s: Exasol 7.1.0
    • Component/s: None
    • Labels:



      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


      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


      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


      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.




            • Assignee:
              CaptainEXA Captain EXASOL
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              1 Start watching this issue


              • Created: