Skip to Content
avatar image
Former Member

Table Elimination with Full Outer Joins vs Left Outer Joins

We see a discrepency in how HANA optimizes its queryplan with the use of "Full Outer Joins" versus "Left Outer joins".

See the attached file for our scenario:

hana-full-outer-joins.jpg

Our question: Is it intended that in Full Outer Join scenarios all tables are queried even if the query doesn't include columns from tables? This isn't the case with Left Outer Joins

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Oct 30, 2017 at 11:28 AM

    What are the join cardinalities you setup in the model? These determine possible join elimination.

    Add comment
    10|10000 characters needed characters exceeded

    • The full outer join is basically

      Table_A left outer join Table_B
      UNION ALL
      Table_A right outer join Table_B

      Even if join elimination is possible for the first outer join, the second join works inverse to that.

      In order to find all non-matches of Table_B in Table_A (where Table_A would produce NULLs) Table_B has to be probed against Table_A.

      So, there's no option to skip that and leave out that join. Also remember, that the join cardinality provides information on the upper boundary of joined records (how many matching records can be expected on the "other" end of the join), but not on how many non-matches to expect.

      When you add aggregation it may be possible (depending on the aggregation) to push down the calculation and avoid the join altogether. But that has nothing to do with join elimination. It's rather a more general query rewriting step.