3 Replies Latest reply on Mar 31, 2015 2:05 PM by Ramesh Reddy

    Question on VDB Query Plans

    Scott Wallace Newbie

      I have two tables, call them a and b. Both reside in the same database, but imported as separate source xmi files. I've built Vds separately on each.

       

      I want to create another Vdb, which is a full outer join of the two Vdbs. I create a view containing the two Vdbs as follows:

       

      SELECT coalesce(vdb_a.key, vdb_b.key) as key, vdb_a.column_1, vdb_b.column_1

      FROM vdb_a FULL OUTER JOIN /*+ optional */ vbd_b ON vdb_a.key = vdb_b.key

       

      As a test, I query the combined Vdb as follows:

       

      SELECT key, sum(column_1), sum(column_2)

      FROM vdb

      WHERE key = 123

      GROUP BY key

       

      My desired effect is for this to either:

      a) push down the entire Sql to the source as a full outer join statement, including the filter predicate key = 123

      b) push down two queries, each containing the filter predicate key = 123

       

      Instead, I'm seeing two queries without the filter predicate in the source and Teiid ends pulling all the data from those tables and then applying the filter locally. This is not optimal for very large data sets. I'd like to be able to tell Teiid to push the Sql and predicate back to the source. Is this possible?