3 Replies Latest reply on Sep 4, 2015 10:52 AM by Steven Hawkins

    Pushing left outer joins in a multi-source query

    Mike Higgins Newbie

      Using Teiid 8.8, I have some SQL similar to the following:

       

      select *

      from s1.t1 t1

      left outer join s2.t2 t2 on (t1.id = t2.id)

      left outer join s2.t3 t3 on (t2.id2 = t3.id2)

      where t1.name = 'x'

      option makedep s2.t2

       

      t1 is in one database, t2 and t3 are in a different database.  Both databases are Oracle.

       

      Teiid will not push the left outer join from t2 to t3 into the second database, and instead performs the join itself.

      If the second join is grouped with parentheses, it will push the outer join to the second database.

      This results in much better performance.  In the actual SQL, there are two more outer joins,

      which results in a 10x improvement in speed if they are all pushed.

       

      I believe in this case, the outer joins are associative because the on conditions reference only the immediately joined tables.

      Besides grouping the joins with parentheses, is there some other hint or option that would allow the outer

      joins to be pushed?  Or is the behavior different in a later version of Teiid?

       

      The SQL is generated from a program, so adding parentheses around the second join is possible, but not easy.

       

      I should mention that if all the joins are changed to inner joins, everything works as expected.  But this is not an

      option for the queries I am looking at.

       

       

      Thanks,

      Mike Higgins